Why does doctrine use WHERE IN instead of LIMIT?

Question

Why does doctrine (1.2) use WHERE IN instead of LIMIT?

This code:

Doctrine_Query::create()
   ->from('Table t')
   ->limit(10)
   ->getSqlQuery();

Returns something like this:

SELECT t.id_table AS t__id_table FROM table AS t WHERE t__id_table IN (1,2,3,4,10,12,18,20,21,25);

Instead of this:

SELECT t.id_table AS t__id_table FROM table AS t LIMIT 10;

This behaivor is same for any LIMIT value. This generates a very long queries for high LIMIT values.

Bonus question: How does Doctrine know, what ids to use? (By sending another query to DB??)

Answer 1

That's because LIMIT operates on database rows not "objects". When you type $q->limit(10) you want to get ten objects, not ten rows from database.

Consider following query (products and categories have many-to-many relationship):

SELECT p.*, c.* FROM product p 
INNER JOIN product_category_ref pcr ON p.id = pcr.prodcut_id
INNER JOIN category c ON c.id = pcr.category_id
WHERE p.price < 123;

To fetch 10 products (objects) your query will have to fetch at least 20 rows. You cannot use LIMIT 10 cause (just for example) only 3 products would be returned. That's why you need to find out which products should be fetched (limit applies to products), and later fetch the actual data.

That will result in following queries:

SELECT p.id FROM product p WHERE p.price < 123;
SELECT ..... WHERE p.id IN (...);

Second query might return 20, 423 or 31 rows. As you can see that's not a value from limit().

PS. Doctrine2 is much more clearer in that case as it's using setMaxResults() method instead of limit() which is less confusing.