DQL Access Property of Property in Query Builder (Doctrine)

Question

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.

Answer 1

I found the answer to my question

$lineItems      = $em->createQueryBuilder()->select('invitems.unitprice,invitems.quantity,invitems.tax,invitems.scrapetax')
                                      ->from('AppBundle:InvoiceLineItems', 'invitems')
                                      ->leftJoin('invitems.invoiceId','inv') //@JA - StoreID is present in the invoice, only need to leftjoin in this case
                                      ->where('inv.companyId = :companyId')
                                      ->andWhere('inv.storeId = :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();

It turns out the code inv.storeId DOES work. This is better than trying to leftJoin in my case. While my question is not technically answered this was the solution to my problem. I am still curious if its possible to leftJoin on a property that you had already leftJoined on?