Ask your Symfony questions! Pay money and get answers fast! (more info)

Left join to a group of tables (nested joins) in Doctrine Symfony

  • SOLVED

How can perform nested joins in Doctrine? my specific need can be better explained with an example:

Tables:
(left side)
-Events(id, name)

a search space made out of:
-EventLog(event_id, user_id, date)
-Users(id, name, dep_id)
-Departments(id, dep_name)

The foreign keys are straightforward and the relations are declared in schema.yml.

When searching for the number of events in a specific department the result should be like:

event_name number_of_events
event1 10
event2 0 -->the zero valued rows are the hard part
event3 0
event4 5

In plain SQL, the following does the job for deparment 10:

SELECT e.name, count(if(l.event_id IS NOT NULL,1,NULL)) as number_of_events
FROM Event e
LEFT JOIN ( EventLog l, Users u, Departments d) ON e.id = l.event_id
AND l.user_id = u.id
AND u.dep_id = d.id
AND d.id = 10


Any ideas on how to implement this on Symfony?? If I only want the values with number_of_events > 0 it is very easy to achieve, but I also need the rows with 'zero' from the DQL statement (i.e. Outer join to a group of tables)

Answers (4)

2010-11-24

Gert Findel answers:

I had this problem today, solved it with:


$object = Doctrine_Query:: create ( )
-> from ( 'Event e' )
->select('e.name, count(if(l.event_id IS NOT NULL,1,NULL)) as number_of_events')
->leftJoin('e.EventLog l ON e.id = l.event_id')
->leftJoin('l.Users u ON l.user_id = u.id' )
->leftJoin('u.Departments d ON u.dep_id = d.id AND d.id = ?',array(10))
->execute();


This DOES give you the rows with 0 Logs


fxsymfony comments:

Thanks a lot Gert, it does work indeed!!

There is one part that's wrong in my sql code, which is that the count if should be instead:

count(if(<strong>d.id</strong> IS NOT NULL,1,NULL))

2010-11-24

Gordon Franke answers:

iam not really shoure but i think this sql does the same


SELECT e.name, count(if(l.event_id IS NOT NULL,1,NULL)) as number_of_events
FROM Event e
LEFT JOIN EventLog l ON e.id = l.event_id
LEFT JOIN User u ON l.user_id = u.id
LEFT JOIN Departments d ON u.dep_id = d.id
WHERE d.id = 10


So in doctrine you can write:

->select('e.name, count(if(l.event_id IS NOT NULL,1,NULL)) as number_of_events')
->leftJoin('e.EventLog l')
->leftJoin('l.Users u')
->leftJoin('u.Departments d')
->where('d.id = ?')
->execute(array(10));


fxsymfony comments:

This doesn't give u the rows with zero as the total.

2010-11-24

Jimish Gamit answers:

little bit change in gordon code

$object = Doctrine_Query:: create ( )
-> from ( 'Event e' )
->select('e.name, count(if(l.event_id IS NOT NULL,1,NULL)) as number_of_events')
->leftJoin('e.EventLog l ON e.id = l.event_id')
->leftJoin('l.Users u ON l.user_id = u.id' )
->leftJoin('u.Departments d ON u.dep_id = d.id')
->where('d.id = ?',array(10))
->execute();


fxsymfony comments:

same error as before, it won't give u the rows with zero.

2010-11-24

Andrei Dziahel answers:

Another improvement suggestion:
$objects = Doctrine_Query:: create ( )
-> from ( 'Event e, e.EventLog l, l.Users u, u.Departments d' ) //shortcut for chain of joins
->select('e.*, count(if(l.event_id IS NOT NULL,1,NULL)) as number_of_events')
->where('d.id = ?', 10)->execute();


fxsymfony comments:

This wont give u the rows with total = 0 because u are only joining for the events that have a log.