2

I want to do a somewhat complex query in doctrine, namely an inner join with a subquery with a group_concat.

See the query in plain SQL:

SELECT *
FROM kinderen k
INNER JOIN
(
  SELECT i.kindid, GROUP_CONCAT(DISTINCT a.periode) as periodes
  FROM inschrijvingen i
  INNER JOIN activiteiten a ON i.activiteitid=a.id
  GROUP BY i.kindid
) p
ON k.kindid=p.kindid;

1) How can I do this in doctrine? In other words how can I translate this regular sql into dql?

2) I would like the extra property (periodes) to be accessible in the resulting Kinderen object. I suspect that this is default behavior for doctrine?

I can't find the solution when I read through the docs and google.

Thanx!

chrizzler
  • 82
  • 2
  • 9

1 Answers1

0

Seems this is available in Doctrine 2 as a "fetch join" but not sure if it exists for 1.2.

http://www.doctrine-project.org/projects/orm/2.0/docs/reference/dql-doctrine-query-language/en

Tom
  • 30,090
  • 27
  • 90
  • 124
  • http://stackoverflow.com/questions/3324101/doctrine-regular-vs-fetch-join Regular vs fetch seems only to be a matter of performance. My main question is how to translate the regular sql into dql, I'll update my question. Thanx. – chrizzler Nov 12 '10 at 09:55
  • I see... I was thrown off by the hydration bit. Does it have to be DQL? See my answer here: http://stackoverflow.com/questions/2774947/using-raw-sql-with-doctrine/2777020 ... not sure what the execution methods offer for hydrating everything. When I need to bypass DQL occassionally, I always for go for array data. – Tom Nov 12 '10 at 15:54
  • Thanks for the link. But... I have several functions processing the result and those expect an object. Of course I can rewrite them, but it would be nice to keep it object-orientated. I use Zend + Doctrine. – chrizzler Nov 15 '10 at 09:23
  • Did you take a look at the "Doctrine_Connection" options here: http://www.doctrine-project.org/projects/orm/1.2/api ... ? You might find something there. – Tom Nov 15 '10 at 20:00
  • The doctrine links are broken. – Jason Liu Oct 30 '18 at 00:03