Doctrine issue - Different queries, same results but not with Doctrine

Question

Hi, i'm having a little issue with doctrine using symfony 1.4 (I think it's using doctrine 1.2). I have 2 queries, using raw sql in the mysql console, they produce the same resultset. The queries can be generated using this code :

    $dates = Doctrine::getTable('Picture')
              ->createQuery('a')
              ->select('substr(a.created_at,1,10) as date')
              ->leftjoin('a.PictureTag pt ON a.id = pt.picture_id')
              ->leftjoin('pt.Tag t ON t.id = pt.tag_id')
              ->where('a.created_at <= ?', date('Y-m-d 23:59:59'))
              ->orderBy('date DESC')
              ->groupby('date')
              ->limit(ITEMS_PER_PAGE)
              ->offset(ITEMS_PER_PAGE * $this->page)
              ->execute();

If I remove the two joins, it changes the query, but the resultset it's the same. But using doctrine execute(), one produces only one row.

Somebody have an idea on what's going on here?

PS : Picture table has id, title, file, created_at (format 'Y-m-d h:i:s'), the Tag table is id, name and PictureTag is an relationship table with id and the two foreign keys.

PS 2 : Here are the two sql queries produced (the first without joins)

SELECT substr(l.created_at, 1, 10) AS l__0 FROM lupa_picture l WHERE (l.created_at <= '2010-03-19 23:59:59') GROUP BY l__0 ORDER BY l__0 DESC LIMIT 4

 SELECT substr(l.created_at, 1, 10) AS l__0 FROM lupa_picture l LEFT JOIN lupa_picture_tag l2 ON (l.id = l2.picture_id) LEFT JOIN lupa_tag l3 ON (l3.id = l2.tag_id) WHERE (l.created_at <= '2010-03-19 23:59:59') GROUP BY l__0 ORDER BY l__0 DESC LIMIT 4

Answer 1

I had something similar this week. Doctrine's generated SQL (from the Symfony debug toolbar) worked fine in phpMyAdmin, but failed when running the query as in your question. Try adding in the following into your query:

->setHydrationMode(Doctrine::HYDRATE_SCALAR)

and see if it gives you the expected result. If so, it's down to the Doctrine_Collection using the Picture primary key as the index in the collection. If you have more than 1 result with the same index, Doctrine will refuse to add it into the collection, so you only end up with 1 result. I ended up running the query using a different table rather than the one I wanted, which resulted in a unique primary key and then the results I wanted appeared.

Answer 2

Well, the solution was that...besides substr(), it needs another column of the table. Using select(substr(), a.created_at) made it work