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,
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.