I have two tables:
- A Packages table that stores a list of packages against an order.
- A Package Status table that lists the lifecycle of a package from inception to delivery to the consignee
The idea is to pick up all new packages and make an API call to the Carriers for a Quote. The way to determine this is that the package has no entry in the Package Status table.
On the database my SQL query is:
SELECT p.id ,p.order_number ,p.merchant_code ,p.package_number FROM packages p LEFT JOIN package_status s ON p.order_number = s.order_number AND p.merchant_code = s.merchant_code AND p.package_number = s.package_number WHERE s.order_number IS NULL AND s.merchant_code IS NULL AND s.package_number IS NULL; This query gives me the correct results. I have verified it manually and found them to be correct.
I then created a Query in the repository for Packages in my Symfony application as:
return $this->createQueryBuilder('pa') ->select( 'pa.OrderNumber', 'pa.MerchantCode', 'pa.StoreCode', 'pa.PackType', 'pa.PackageNumber', 'pa.PackWeight', 'pa.PackLength', 'pa.PackWidth', 'pa.PackHeight', 'pa.PackVolume' ) ->leftjoin('App\Entity\PackageStatus', 'psmc', 'WITH', 'pa.MerchantCode = psmc.merchantCode') ->leftjoin('App\Entity\PackageStatus', 'pson', 'WITH', 'pa.OrderNumber = pson.orderNumber') ->leftjoin('App\Entity\PackageStatus', 'pspn', 'WITH', 'pa.PackageNumber = pspn.packageNumber') ->andWhere('psmc.merchantCode is NULL') ->andWhere('pson.orderNumber is NULL') ->andWhere('pspn.packageNumber is NULL') ->addorderBy('pa.StoreCode', 'ASC') ->addorderBy('pa.PackType', 'ASC') ->addorderBy('pa.PackageNumber', 'ASC') ->getQuery() ->getResult() ; The query returns no records. What is wrong in my Doctrine Query?