为什么教条使用的是而不是限制?

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

为什么教条(1.2)用在哪里而不是限制?

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??)

附加问题:教条怎么知道，用什么id ?(通过向DB发送另一个查询?)

2 个解决方案

#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.

这是因为LIMIT操作数据库行，而不是“对象”。当您输入$q->限制(10)时，您希望从数据库获得10个对象，而不是10行。

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.

要获取10个产品(对象)，您的查询必须至少获取20行。你不能使用极限10因为(仅举例子)只有3个产品会被返回。这就是为什么您需要找出应该获取哪些产品(对产品进行限制)，然后获取实际数据。

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().

第二个查询可能返回20,423或31行。可以看到，这不是limit()的值。

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

在这种情况下，Doctrine2要清楚得多，因为它使用的是setMaxResults()方法，而不是limit()方法，这样比较容易混淆。

#2

Using Doctrine 1.2.3:

使用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

Is there maybe something else going on in your code?

你的代码中可能还有其他的东西吗?

#1