CODEWITHSUNDEEP
doctrine-orm
Gayout Quentin
Gayout Quentin

Reputation: 1

Doctrine2 leftJoin where all joins respect condition

I am facing an issue reguarding a doctrine request. I am using the queryBuilder but cannot find out how to do my request properly.

My database schema is like this : I have a ManyToMany relationship between two entities : "Lot" and "Bail".

What I want to do is get data related to one "Lot" if ALL its "Bail" follow one of these two rules :

  1. The bail is deleted (its field deleteDate is not null)
  2. The date I set as a parameter isn't between the Bail's startDate and endDate (the Bail is too old or to recent)

I want to get the Lot only if ALL its Bail follow one of the two rules, meaning if one Bail whose not deleted or too old/recent, I don't get the Lot.

I have looked for several topics on leftJoin with condition but cannot seem to make it work.

Here is what I have done so far :

$queryBuilder = $this->createQueryBuilder('lot');
    $queryBuilder
        ->select(
            'typo.id as typologyId',
            'typo.label as typology',
            'lot.floor',
            'sum(lot.sdp) as surface',
            'sum(1) as total'
        )
        ->leftJoin('lot.typology', 'typo')
        ->leftJoin('lot.batiment', 'batiment')
        ->leftJoin('lot.baux', 'bail', 'WITH', ':date not between bail.datePriseEffet and bail.dateFin or bail.deleteDate is not null')
        ->leftJoin('batiment.building', 'building')
        ->andWhere('building.id = :buildingId')
        ->addOrderBy('typo.label')
        ->addOrderBy('lot.floor')
        ->groupBy('typologyId, typology, lot.floor')
        ->setParameters(array(":buildingId" =>$buildingId, ":date" =>$date));

This is not working, I get the "Lot" if at least one of its Bail is deleted or too old/recent, not ALL of them.

How can I fix it ?

Maybe I can use the $queryBuilder->expr()->exists() function, but I don't really understand how it works.

Upvotes: 0

Views: 28

Answers (1)

Roubi
Roubi

Reputation: 2106

A (very) late answer!

Let say you have a Library entity related to Book entities: Book <=ManyToOne=>Library. Let say now that a book have a property $available. If you want to fetch all the libraries which ALL books are availables, this query will NOT work:

LibraryRepository
//....
$qb = $this->createQueryBuilder('l')
    -> leftJoin('l.books', 'b')
    -> andWhere("b.available = 1");

This will return libraries containing at least an available book.

To get the expected result, you have to build two queries: The first query ($qb) will fetch the entities we do not want (libraries with at least one book not available.

            $qb = $this->createQueryBuilder('lib') // We're selecting libraries but using a different alias because 'l' is already used in the other query
                ->join('lib.books', 'bo')
                ->andWhere("bo.available = 0");

The second query $qb2 will select all libraries that do not belong to the first query results (hence the libraries wich all books are availables.

$qb2 = $this->createQueryBuilder('l');
$qb2 ->andWhere($qb->expr()->notIn('l.id', $qb->getDQL()));

Note that there's a trick with parameters: if you have to use a parameter in your fisrt query, you have to define it (setParameter() method) inside the second query.

Upvotes: 0

Related Questions