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();
}