Condition only inside join in query

Viewed 82

I'm trying to do a query in Symfony where I want find lunches for a day just for a specific employee. I really can't image it will be that hard, so I hope there is a simple answer.

The different tables in the DB are single_day, lunches and employees. A single day can have many lunches from different employees connected to it. In the rest API this will be the url that accomplish this: https://example.org/api/day?employee=728

In SQL this simple query solves it:

    SELECT * FROM hs_morning.single_day as single_day
    LEFT JOIN (SELECT id as lunch_id, vegetarian, employee_id, single_day_id FROM hs_morning.lunch WHERE lunch.employee_id = 727) as lunch
    ON single_day.id = lunch.single_day_id

I have this code in my repo and it returns all days as I want it to, but it does not filter out the lunches by other employees:

    public function findDayAndLunchesByEmployee($employee, $limit = 10)
        {
            return $this->createQueryBuilder('d')
                ->leftJoin('d.lunches', 'l', Expr\Join::WITH, 'l.employee = :val') // conditions here have no effect
                // ->andWhere('l.employee = :val') //this will prevent all days from being included (which I need) - so it does not work
                ->setParameter('val', $employee)
                ->orderBy('d.id', 'ASC')
                ->setMaxResults($limit)
                ->getQuery()
                ->getResult()
            ;
        }

So this is for example the result right now. My request on https://example.org/api/day?employee=728 is almost perfect, but I do not want the lunch for employee nr. 727 appear, only lunches for employee nr. 728:

[
    {
        "id": 118,
        "date": "2021-10-05T09:00:00+00:00",
        "lunches": [
            {
                "id": 2202,
                "vegetarian": false,
                "employee": {
                    "id": 728
                }
            }
        ]
    },
    {
        "id": 119,
        "date": "2021-10-06T09:00:00+00:00",
        "lunches": [
            {
                "id": 2199,
                "vegetarian": false,
                "employee": {
                    "id": 727
                }
            }
        ]
    },
    {
        "id": 120,
        "date": "2021-10-07T09:00:00+00:00",
        "lunches": [
            {
                "id": 2200,
                "vegetarian": false,
                "employee": {
                    "id": 727
                }
            },
            {
                "id": 2201,
                "vegetarian": false,
                "employee": {
                    "id": 728
                }
            }
        ]
    },
    {
        "id": 121,
        "date": "2021-10-08T09:00:00+00:00",
        "lunches": []
    },
    {
        "id": 122,
        "date": "2021-10-09T09:00:00+00:00",
        "lunches": []
    }
]

In other words this should not be in the response:

           {
                "id": 2201,
                "vegetarian": false,
                "employee": {
                    "id": 728
                }
            }
                    

EDIT: Thank you so much to @V-Light for a working solution. FYI: I also managed to achieve the same result with this piece of code (using ResultSetMappingBuilder and createNativeQuery):

public function findRecentDaysAndLunchesByEmployee($employee_id, $limit = 10)
{
    $sql = "SELECT * FROM single_day as single_day
    LEFT JOIN (
        SELECT single_day_id, employee_id, vegetarian, id as lunch_id
        FROM lunch WHERE employee_id = $employee_id
    ) as lunch
    ON single_day.id = lunch.single_day_id
    WHERE
    date BETWEEN '"
        . date("Y-m-d h:i:s", strtotime('monday this week 1am'))
        . "' AND '"
        . date("Y-m-d h:i:s", strtotime('friday this week'))
    . "'
    LIMIT $limit";

    $entityManager = $this->getEntityManager();

    $rsm = new ResultSetMappingBuilder($entityManager);

    //map query to SingleDay entity
    $rsm->addRootEntityFromClassMetadata('App\Entity\SingleDay', 'single_day');
    
    /**
     * map subquery to Lunch entity
     * 2. param: lunch alias
     * 3. param: parent query
     * 4. param: name of ORM relation
     * 5. param: new name for id of lunch in order to prevent conflict with id from single_day
     */
    $rsm->addJoinedEntityFromClassMetadata('App\Entity\Lunch', 'lunch', 'single_day', 'lunches', array('id' => 'lunch_id'));

    $query = $entityManager->createNativeQuery($sql, $rsm);
    return $query->getResult();
}
1 Answers

Without ORM-Relations it will be harder, but I'll try anyway.

First, as I already said in the comments, there is no easy way to transfer your SQL to DQL. Doctrine doesn't know how to work with sub-queries or temp-tables.

But, I see two possible solutions:

First - try to split your query. Move your "pre-selection" out

SELECT id FROM lunch WHERE employee_id = 727

and then use the results in LEFT JOIN as a condition

SELECT * 
FROM single_day d
LEFT JOIN lunch l ON (d.id = l.single_day_id AND l.id IN (:lunch_ids_from_prev_selection) )

with QueryBuilder it would look like this.

public function findDayAndLunchesByEmployee($employee, $limit = 10) {
    // first, get all distinct lunch_ids by given employee
    $distinctLunchesQuery = $this->_em->getRepository(Lunch::class)->createQueryBuilder('l')
                    ->select('l.id AS lunch_id')->distinct()
                    ->where('l.employee = :given_employee')->setParameter('given_employee', $employee)
                    ->getQuery();
    
    $preSelected = $distinctLunchesQuery->getScalarResult();
    // distinct ID as flat-array
    $distinctIds = array_column($preSelected, 'lunch_id'); // see ALIAS in $distinctLunchesQuery
    
    // check if atleast one was found
    if(empty($distinctIds[0]))
    {
        // IN() condition doesn't work with empty lists.
        // since your second query depends on this list, you just provide some dummy (or not existing) data which will 100% NOT MATCH!
        $distinctIds = [-1];
    }
    
    // your "regular" query with LEFT JOIN
    $allSingleDayQuery = $this->createQueryBuilder('d')
        ->select('d')
        ->leftJoin('d.lunches', 'l', 'WITH', 'l.id IN (:given_lunched_ids_list)')
        ->addSelect('l') // important!
        ->setParameter('given_lunched_ids_list', $distinctIds)
        ->orderBy('d.id', 'ASC')
        ->setMaxResults($limit)
        ->getQuery()
        ->getResult()
    ;
    
    return $allSingleDayQuery;
}

But depending on your orm-relations, especially if one of them configured as fetch="EAGER", the result may not match your expectations.


Second - compose desired results by yourself

When I see final output, I see JSON. I also see incrementing days. So you could "shorten" your selection by selecting all single_day for only one month (or some other period)

So you could, again, select desired single_day entries, then loop through all of them and check if there are any lunches for that date by a given employee which you also preselected by period.

Related