Doctrine skips datasets when using joins and offset

I'm working with Doctrine 1.2 in Symfony 1.4 on PHP5.3.

I have a database, in which events have multiple start datetimes (1:n) and types (n:m).

For a program, I want a list of start times and event title, which belong to a special type and are not outdated:

$dql = Doctrine_Query::create()
  ->select('ed.start_datetime,e.title')/*edd.*,*/
  ->from('EventDate ed')
  ->leftJoin('ed.Event e')
  ->leftJoin('e.EventTypes et')
  ->leftJoin('e.Doma开发者_开发知识库in d')
  ->orderBy('ed.start_datetime, ed.event_id');
  ->where('e.start_datetime >= ? AND e3.id = ?',array('2011-05-27 17:19:41',2))
  ->fetchArray();

The schema can be reviewed at pastebin.com/SdjvsaxW.

Now, doctrine makes two statements

SELECT DISTINCT e5.event_id FROM event_date e5 LEFT JOIN event e6 ON e5.event_id = e6.id LEFT JOIN event_event_type e8 ON (e6.id = e8.event_id) LEFT JOIN event_type e7 ON e7.id = e8.event_type_id WHERE (e5.start_datetime >= '2011-05-27 17:19:41' AND e7.id = '2') ORDER BY e5.start_datetime, e5.event_id

SELECT e.event_id AS e__event_id, e.start_datetime AS e__start_datetime, e2.id AS e2__id, e2.title AS e2__title FROM event_date e LEFT JOIN event e2 ON e.event_id = e2.id LEFT JOIN event_event_type e4 ON (e2.id = e4.event_id) LEFT JOIN event_type e3 ON e3.id = e4.event_type_id WHERE e.event_id IN (*all the IDs from the first query*) AND (e.start_datetime >= '2011-05-27 17:19:41' AND e3.id = '2') ORDER BY e.start_datetime, e.event_id

The problem starts when I introduce pagination: When I add an offset, it will be added in the first query. Now, as there might be more than one start time for an event, the number of IDs from the first SELECT DISTINCT query may be smaller than the number of dates. Consequently, dates may be skipped. In my case, there are even less than 50 different events, so the second pages displaying event date 51-100 is indeed empty, despite the fact that there are several hundred event dates (due to recurring events).

I think the problem might be solved leaving away the distinct, but I cannot control that.

Anyone any idea?

Doctrine runs two queries, the first a distinct, in a mode called "limit subquery", when you have a base table and join across a one-to-many relationship. This is because you are going to select the product of the join, and will end up with more rows than there are records in the base table. For example, the database will return Event #5 in two rows if there are two EventTypes for Event #5. This two query behavior is in MySQL only, other databases can run a limit in a subquery, and Doctrine generates a single query.

Since your Events have multiple dates, and EventDate has a compound primary key, Doctrine SHOULD select a distinct set of (event_id, start_datetime) in the first query, but it seems to be ignoring the second part of your compound primary key, probably because it can't stuff them into the ON clause in the second query.

You can probably work around this in one of two, or maybe three, ways: 1) add a unique primary key to EventDate (autoincrement column). you likely don't have to reference it anywhere else, but doctrine will start using that 2) do the 2 queries manually. Select all of the distinct event_id, start_datetime from EventDate, add your limit and offset, and then programmatically create an where clause:

 ... WHERE ((event_id = ? AND start_datetime = ?) OR (event_id = ? AND start_datetime = ?))... 

3) Reorder your query to start from a different table or add a DISTINCT in the select. Try selecting distinct(ed.event_id, ed.start_datetime). Sometimes these things can avoid the limit-subquery algorithm from firing. 4) Turn this system off, see link below, never tried this and I'm not sure what side effects there are in hydration and the doctrine object cache.

Personally, I would go with #1. I've found that it always helps to have that extra primary key column with Doctrine, even when you can form a primary key across multiple columns.

If you want to see the code doctrine uses for this, see lib/Doctrine/Record.php, function getLimitSubquery(). You'll note the comment "// what about composite keys?" in the code twice. You can also read http://www.doctrine-project.org/documentation/manual/1_2/en/dql-doctrine-query-language:limit-and-offset-clauses:the-limit-subquery-algorithm