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

Warning: Please do not give out any FTP or ssh credentials to anyone, unless you trust them completely. Giving out login details is dangerous.

If the asker does not get an answer then they have 10 days to request a refund.

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

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)

This question has been answered.

fxsymfony | 11/24/10 at 3:56am Edit


The experts have suggested, on average, a prize of $10 for this question.

(8) Responses

See a threaded view of answers?

Warning: Please do not give out any FTP or ssh credentials to anyone, unless you trust them completely. Giving out login details is dangerous.

  • avatar
    Last edited:
    11/24/10
    4:06am
    Gordon Franke says:

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

    Previous versions of this answer: 11/24/10 at 4:06am

  • avatar
    Last edited:
    11/24/10
    5:14am
    Jimish Gamit says:

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

    Previous versions of this answer: 11/24/10 at 5:12am | 11/24/10 at 5:14am | 11/24/10 at 5:14am

  • avatar
    Last edited:
    11/24/10
    9:18am
    Andrei Dziahel says:

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

  • avatar
    Last edited:
    11/24/10
    11:31pm
    Gert Findel says:

    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

  • avatar
    Last edited:
    11/24/10
    9:22pm
    fxsymfony says:

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

  • avatar
    Last edited:
    11/24/10
    9:22pm
    fxsymfony says:

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

  • avatar
    Last edited:
    11/24/10
    9:23pm
    fxsymfony says:

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

  • avatar
    Last edited:
    11/24/10
    11:30pm
    fxsymfony says:

    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(d.id IS NOT NULL,1,NULL))

This question has expired.





Current status of this question: Completed



Warning: Please do not give out any FTP or ssh credentials to anyone, unless you trust them completely. Giving out login details is dangerous.

If the asker does not get an answer then they have 10 days to request a refund.