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.