sridhar pandurangiah
sridhar pandurangiah

Reputation: 309

Query doesn't return records but SQL does

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?

Upvotes: 0

Views: 33

Answers (0)

Related Questions