I have the following query I am putting together.
$lineItems = $em->createQueryBuilder()->select('invitems.unitprice,invitems.quantity,invitems.tax,invitems.scrapetax')
->from('AppBundle:InvoiceLineItems', 'invitems')
->leftJoin('invitems.invoiceId','inv') //StoreID is present in the invoice so only need to leftjoin here
->leftJoin('inv.storeId','si')
->where('inv.companyId = :companyId')
->andWhere('si.id = :storeId')
->andWhere('inv.created BETWEEN :startdate AND :enddate')
->andWhere("inv.status = 'sent' or inv.status = 'paid' or inv.status = 'partial' ")
->setParameter('startdate', $startDate)
->setParameter('enddate', $endDate)
->setParameter('companyId',$companyid)
->setParameter('storeId',$store['id'])
->getQuery()
->getResult();
The query runs without crashing but does not work since I know I have database entries that belong to a store for the invoice. It currently returns 0 entries. I verified $store['id'] is giving correct ID so its not a silly error. Removing that line and its parameter reference returns rows just fine otherwise.
The problem is this line, ->leftJoin('inv.storeId','si')
Am I allowed to left join on a property that came from another left join? The reason I need to do this is the lineitems do not have reference to the storeID and only invoice does.
I can't find in the documentation anything about this subject in particular.
I tried this also without any success.
->andWhere('inv.storeId = :storeId')
Essentially what I'm trying to do is this:
InvoiceLineItems has reference to an Invoice. Invoice has reference to a Store
I'm trying to get access to the invoice object (which works currently) but then access to the store object that the invoice object holds.