Why does the doctrine use WHERE IN instead of LIMIT?

Question

Why is doctrine (1.2) using 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 behavior is the same for any LIMIT value. This generates very long queries for high LIMIT values.

Bonus question: how does the Doctrine know which identifiers to use? (By sending another query to the DB ??)

Answer 1

This is because LIMIT works with database rows, not with "objects". When you enter $q->limit(10) , you want to get ten objects, not ten rows from the database.

Consider the following query (products and categories are many-to-many):

 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 receive 10 products (objects), your request will have to receive at least 20 lines. You cannot use the reason LIMIT 10 (for example, only), only 3 products will be returned. That's why you need to find out which products should be obtained (the restriction applies to products), and then extract the actual data.

This will result in the following queries:

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

The second request can return 20, 423 or 31 lines. As you can see, this is not the value from limit() .

PS. Doctrine2 is much more clear in this case, as it uses the setMaxResults() method instead of limit() , which is less confusing.

Answer 2

Using Doctrine 1.2.3:

 <?php include(dirname(__FILE__).'/Doctrine.php'); spl_autoload_register(array('Doctrine', 'autoload')); $dbh = new PDO('mysql:dbname=testdb;host=127.0.0.1', 'testdb', 'testdb'); $conn = Doctrine_Manager::connection($dbh); class Table extends Doctrine_Record { public function setTableDefinition() { $this->hasColumn('id_table', integer, 10, array('primary' => true)); } } $q = Doctrine_Query::create() ->from('Table t') ->limit(10) ->getSqlQuery(); echo $q; 

I get the result:

 SELECT t.id_table AS t__id_table FROM table t LIMIT 10 

Perhaps something else is happening in your code?