Joining only one row in doctrine

Question

I've got a event table with title and description of an event, and i've got a eventInstance table where the dates and venues of an event are stored. So it's a one to n relation: one event can have many instances.

What i'm trying now is to select only the most current event instance for an event. In SQL the query is:

select e.id, e.title, ei.start_date 
from event e 
LEFT join event_instance ei on ei.id = 
(SELECT id FROM event_instance ei where ei.event_id = e.id ORDER BY start_date asc, start_time asc LIMIT 1) 
ORDER BY start_date asc, start_time asc LIMIT 20;

I'm trying to rewrite the sql command in dql. So far i've got this:

SELECT e, ei 
FROM AppBundle:Event e LEFT JOIN e.eventInstances ei WITH ei = 
(SELECT a FROM AppBundle:EventInstance a WHERE a.event = e ORDER BY a.startDate asc, a.startTime asc)

My problem is, there's no LIMIT command in dql, so i cannot limit the subquery to give me one result. So the error i'm getting when executing this query is:

SQLSTATE[21000]: Cardinality violation: 7 ERROR: more than one row returned by a subquery used as an expression

Is there any way i can make this work?

Possible duplicate of [Doctrine Query Language get Max/Latest Row Per Group](https://stackoverflow.com/questions/46106582/doctrine-query-language-get-max-latest-row-per-group) — M Khalid Junaid, Dec 05 '17 at 05:06

Ollie in PGH · Answer 1 · 2017-08-22T15:59:24.653

1

Create a subquery then set max results on that.

$subDql = 'SELECT a
           FROM AppBundle:EventInstance a 
           WHERE a.event = e
           ORDER BY a.startDate asc, a.startTime asc';

$subQuery = $this->getEntityManager()
                 ->createQuery($subDql)
                 ->setMaxResults(1)
;

$dql = 'SELECT e, ei FROM AppBundle:Event e
        LEFT JOIN e.eventInstances ei 
        WITH ei = (' .$subQuery . ')'
;

$query = $this->getEntityManager()
              ->createQuery($dql)
              ->setMaxResults($yourOtherLimit)
;

$resultCollection = $query->getResult();

edited Aug 22 '17 at 15:59

answered Aug 22 '17 at 09:53

Ollie in PGH

2,559
2
16
19

this works fine if i want to set max results for the whole query. But i only want to set a limit for the subquery in the join statement (the part in the brackets) – casper Aug 22 '17 at 10:08
Okay. I edited my answer. My code isn't tested so you may have to fiddle with it. Or comment back here with the exception to see if we can narrow down what went wrong. – Ollie in PGH Aug 22 '17 at 16:00
Unfortunately this isn't working either. If i execute your code there's an error: Object of class Doctrine\ORM\Query could not be converted to string. If i use $subQuery->getDql() i get the same error message as before. I guess that's because the setMaxResults is not part of the dql. I think i have to use native sql if i want this to get working. But thank you for your help. – casper Aug 23 '17 at 07:05
Yeah, at this point there's nothing to lose by just using a native sql query. Sorry. Good luck. – Ollie in PGH Aug 23 '17 at 09:21

M Khalid Junaid · Answer 2 · 2017-09-16T16:11:07.697

An equivalent DQL for your solution (to get latest row per group) will be something like

SELECT e,a 
FROM AppBundle:Event e
    JOIN e.eventInstances a
    LEFT JOIN AppBundle\Entity\Score b 
    WITH a.event = b.event 
    AND a.startDate < b.startDate
    AND a.startTime < b.startTime
WHERE b.event IS NULL
ORDER BY a.startTime DESC

OR

SELECT e,a 
FROM AppBundle:Event e
    JOIN e.eventInstances a
    LEFT JOIN AppBundle\Entity\Score b 
    WITH a.event = b.event 
    AND a.startDate = b.startDate
    AND a.startTime < b.startTime
WHERE b.event IS NULL
ORDER BY a.startTime DESC

Joining only one row in doctrine

2 Answers2