SQL - Doctrine / DQL how do I build this query

Question

I have and entity called Event and one called Employee

Event has a ManyToOne with Employee called scheduledEmployee

It also has a ManyToMany with Employee called employeeRequests

There is a column in the Event table called employee_id

There is also a Join Table called Event_Employee_Requests

How would I go about querying for all Events that an Employee has been scheduled OR requested?

$qb = $this->createQueryBuilder('e')
        ->where('e.scheduledEmployee = :user')
        ->setParameter('user', $user)
        ->getQuery();

I am able to get All Events that are scheduled but am unsure how to get the requested events as well.

Update

I have tried the following:

        $qb = $this->createQueryBuilder('e')
        ->join('e.employeeRequests', 'r')
        ->where('e.scheduledEmployee = :user')
        ->orWhere('(r.id = :user AND e.scheduledEmployee is NULL)')
        ->setParameter('user', $user)
        ->getQuery();

This give the following query:

SELECT * FROM event e0_ JOIN event_user_request e2_ ON e0_.id = e2_.event_id INNER JOIN fos_user f1_ ON f1_.id = e2_.user_id WHERE e0_.scheduled_employee_id = 5 OR ((f1_.id = 5 AND e0_.scheduled_employee_id IS NULL))

This query works if I remove the ON clause for the JOIN event_user_request, though I'm not sure how to do that with DQL.

Currently the above SQL returns only requested events and not the ones that are scheduled

score 0 · Accepted Answer · edited Jun 21 '18 at 17:24

0

To get events which are scheduled/ requested for an employee you can go with left join approach which is similar to your query builder code

$qb = $this->createQueryBuilder('e')
->leftJoin('e.employeeRequests', 'r')
->where('e.scheduledEmployee = :user')
->orWhere('(r.id = :user AND e.scheduledEmployee IS NULL)')
->setParameter('user', $user)
->getQuery();

In sql it would look like

SELECT * 
FROM event e0_ 
LEFT JOIN event_user_request e2_ ON e0_.id = e2_.event_id 
LEFT JOIN fos_user f1_ ON f1_.id = e2_.user_id 
WHERE e0_.scheduled_employee_id = 5 
OR (f1_.id = 5 AND e0_.scheduled_employee_id IS NULL)

edited Jun 21 '18 at 17:24

Shawn Northrop

5,826
6
42
80

answered Jun 21 '18 at 06:17

M Khalid Junaid

63,861
10
90
118

That seems to work! What is the difference between `Join` and `leftJoin`? – Shawn Northrop Jun 21 '18 at 17:25
This is best explanation i guess [What is the difference between “INNER JOIN” and “OUTER JOIN”?](https://stackoverflow.com/questions/5706437/whats-the-difference-between-inner-join-left-join-right-join-and-full-join) – M Khalid Junaid Jun 21 '18 at 18:18

SQL - Doctrine / DQL how do I build this query

Update

1 Answers1