0

I have two tables:

  1. A Packages table that stores a list of packages against an order.
  2. 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?

3
  • 1
    What have you tried to debug the issue? Please see How to get the sql from a createQueryBuilder? to start debugging your issue. Commented Feb 6 at 7:29
  • 2
    Is the Doctrine code meant to replicate the SQL query? Because they don't look anything similar. SQL involves a simple join between 2 tables and your query builder joins the same table three times. Commented Feb 6 at 8:21
  • Let me modify the query and check the result. Commented Feb 11 at 4:22

0

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.