Symfony Doctrine query builder find entity with many to one relation

Question

I'm building a website with Symfony for a project which will act like "booking.com" websites but much simplier and with some variations.

Some errors on the database fields but not really important for my issue.

As you can see, these tables concern two entities : apartment and visit. A customer can ask for a visit of an apartment. It is a many to one relationship.

I have a search form to search for apartments with criterias. I want to only show apartments that don't have any visits between the arrival and departure dates that the user provided. So I ended up making a function in apartmentRepository to manage that and other cases.

Problem is: how can I get these apartments ?

Here is a draft of this function which is of course not finished neither perfect (if you have some comments to improve it, it would be great !).

public function findByCustom($parameters): ?array
{
   $query =  $this->createQueryBuilder('a');

   foreach ($parameters as $key=> $parameter){
       if($key != 'keywords' and $key!= 'priceMin' & $key!='priceMax' and $key!="typeAp" and $key!="searchVisitDate") $query->orWhere('a.'.$key." = '".$parameter."'");

       if($key == "typeAp")
       {
           $typeApQuery = "";
           foreach ($parameters[$key] as $index => $type)
           {
               if($index !== count($parameters[$key])-1)
               {
                   $typeApQuery.=" a.typeAp = '".$type."' or";
               }
               else
               {
                   $typeApQuery.= " a.typeAp = '".$type."'";
               }
           }
           $query->andWhere($typeApQuery);
       }
   }

   $query->andWhere('a.rentPrice >='.$parameters['priceMin']." and a.rentPrice <= ".$parameters['priceMax']);
   $withoutInner = $query;
   $query
       ->join('App\Entity\Visit', 'v', Join::ON, 'a = v.apartment')
       ->where("v.date between '2020-03-15' and '2020-03-19'");
    $query->getQuery()->getResult();
    $sorted = $withoutInner->andWhere($this->createQueryBuilder('a')->expr()->notIn('a.id', $query));

   return array($sorted);

Of course apartment has a collection of visits and visit as a field named "apartment" which is related to the apartment object.

I really didn't find a proprer way to do it and I want to avoid doing SQL, to improve my understanding of Doctrine.

Thank you for your help because I'm stuck right now :/

EDIT 1: forgot to mention that I want to get apartments that don't have visits between required dates or that don't have any visits at all

EDIT 2 :

public function findByCustom($parameters): ?array
{
   $query =  $this->createQueryBuilder('a');
    $withoutInner = $this->createQueryBuilder("a");

   foreach ($parameters as $key=> $parameter){
       if($key != 'keywords' and $key!= 'priceMin' & $key!='priceMax' and $key!="typeAp" and $key!="searchVisitDate")
       {
           $withoutInner->orWhere('a.'.$key." = '".$parameter."'");
           $query->orWhere('a.'.$key." = '".$parameter."'");
       }


       if($key == "typeAp")
       {
           $typeApQuery = "";
           foreach ($parameters[$key] as $index => $type)
           {
               if($index !== count($parameters[$key])-1)
               {
                   $typeApQuery.=" a.typeAp = '".$type."' or";
               }
               else
               {
                   $typeApQuery.= " a.typeAp = '".$type."'";
               }
           }
           $withoutInner->andWhere($typeApQuery);
           $query->andWhere($typeApQuery);
       }
   }

   $query->andWhere('a.rentPrice >='.$parameters['priceMin']." and a.rentPrice <= ".$parameters['priceMax']);
   $withoutInner->andWhere('a.rentPrice >='.$parameters['priceMin']." and a.rentPrice <= ".$parameters['priceMax']);
   $query
       ->join('App\Entity\Visit', 'v', Join::WITH, 'a.id = v.apartment')
       ->where("v.date between '2020-03-15' and '2020-03-19'");

    $query->getQuery()->getResult();
    $sorted = $withoutInner->andWhere($this->createQueryBuilder('a')->expr()->notIn('a.id', $query));

With this function I get doctrine error :

Error: Method Doctrine\Common\Collections\ArrayCollection::__toString() must not throw an exception, caught ErrorException: Catchable Fatal Error: Object of class Doctrine\ORM\EntityManager could not be converted to string

you join in the visits and choose those apartments that have no visit at all in that time interval. there might be problems when working with datetime instead of date. Also `$query` is an object, so `$withoutInner` will get modified whenever `$query` is being modified. Improvement suggestion: shorter lines. — Jakumi, Mar 19 '20 at 09:30
Oh yes I didn't think about that.. My point here was not having to retype the line but I will do that. I'm going to try this out and will reach out to you for any problems :) If you could give the code that translates to what you said that woud be great even if I'm going to search by myself :) — julien Dupont, Mar 19 '20 at 11:16

Christoph · Answer 1 · 2020-03-20T08:32:10.330

1

I haven't tested this myself, but something like this should work:

$query
    ->leftJoin('App\Entity\Visit', 'v', Join::WITH, 'a = v.apartment AND v.date between "2020-03-15" and "2020-03-19"')
    ->where('a.visits IS EMPTY');

The idea here is to use leftJoin and select only those result that do not have a corresponding entry in the visit table.

edited Mar 20 '20 at 08:32

answered Mar 19 '20 at 13:39

Christoph

524
10
19

Thank you for you answer :) The query I get (with things added to it before doing the join) : SELECT a FROM App\Entity\Apartment a LEFT JOIN App\Entity\Visit v ON a = v.apartment AND v.date not between "2020-03-15" and "2020-03-19" WHERE a.typeAp = 'T1' AND (a.rentPrice >=1 and a.rentPrice <= 98). That gives me an error : **[Syntax Error] line 0, col 66: Error: Expected end of string, got 'ON'** – julien Dupont Mar 19 '20 at 16:34
1

maybe `WITH` instead of `ON` (although it's slightly anti-intuitive) – Jakumi Mar 19 '20 at 17:31
As per @Jakumi's comment, and reading [this](https://stackoverflow.com/a/19479666/4503776) answer, `WITH` might be the solution here. – Christoph Mar 20 '20 at 08:32

julien Dupont · Accepted Answer · 2020-03-30T15:58:00.133

I found a solution to get all apartments that have no rents (where the join is null). Yes I changed visits for rents because that was a mistake in my main question.

 $query->leftJoin('a.rents', 'r',Join::WITH,'a = r.apartment')
        ->where(
            $query->expr()->andX($query->expr()->isNull('r')))
        ->orWhere($query->expr()-> **);

I have an attribute in my apartment entity which has all rents of the current apartment. So I use it to make the join. With the expression isNull I get it to work for the apartments with no visits.

** : I want to be able to get the apartments that have no locations where user entered required arrival date is not between r.arrival and r.departure. This would give me all apartments without any rents and those which are free to book.

I thought about doing another query and do a notIn but I don't know how to do it neither.

Thank you.

EDIT and solution :

I found out by myself how to do it. This is absolutly not the best approach neither the best solution but I'm out of time for my projet so I needed to get this done.

$rents = $this->getEntityManager()->createQueryBuilder()
        ->select("a.id")->from('App:Rent', 'r')
        ->andWhere('r.arrival between :arrival and :departure')
        ->leftJoin('r.apartment', 'a')
        ->setParameters(array("arrival"=>\DateTime::createFromFormat('Y-m-d', "2020-04-20")->setTime(00,00,00), "departure"=>\DateTime::createFromFormat('Y-m-d',"2020-04-31")->setTime(00,00,00)))
        ->getQuery()->getArrayResult();


    $rentsSorted = array();
    foreach ($rents as $rent)
    {
        if(!in_array( $rent['id'],$rentsSorted))
        {
            $rentsSorted[] = $rent['id'];
        }
    }
    if(count($rentsSorted)>0)
    {
        $withRentsNotBetweenDates->andWhere('a1.rentPrice >=' . $parameters['priceMin'] . " and a1.rentPrice <= " . $parameters['priceMax'])
            ->andWhere($withRentsNotBetweenDates->expr()->notIn('a1.id', $rentsSorted));
    }
    else
    {
        $withRentsNotBetweenDates->andWhere('a1.rentPrice >=' . $parameters['priceMin'] . " and a1.rentPrice <= " . $parameters['priceMax']);
    }

Of course I'm going to change the parameters to the required dates given by my form.

This code allows me to get all rents that exists within these required dates. I then store each apartment id in an array that I will pass in the not in to exclude these apartments because they have a rent at this period.

If you have any improvents or a better way to do this don't be shy xD.

Have a good day.

Symfony Doctrine query builder find entity with many to one relation

2 Answers2