4

Using Zend Lucene Search, I am returning a list of relevance-ordered IDs that map to blog records that I will fetch from the database.

Is this the proper way of handling an array with Doctrine2's WHERE IN expression:

$dql = "SELECT b FROM BlogPost WHERE b.id IN (" . implode(', ', $ids) . ")";
$query = $em->createQuery($dql);
...

Or is there a better way of maybe passing in the actual $ids array as a parameter to the query?

Also, the Zend Search returns the array of IDs based on relevance. Will using the above technique preserve the order of relevance in retrieving the blog posts?

Stephen Watkins
  • 25,047
  • 15
  • 66
  • 100

2 Answers2

4

If it makes you feel better, you can use the ExpressionBuilder.

$ex = $em->getExpressionBuilder();
$dql = 'SELECT b FROM BlogPost b WHERE ' . $ex->in('b.id', $ids));
$query = $em->createQuery($dql);

function cmp($a, $b) {
    global $ids;
    return (array_search($a->getId(), $ids) < array_search($b->getId(), $ids)) ? -1 : 1;
}
usort($res, 'cmp');

It's a bit cleaner, but does the same as you behind the screens.

tvlooy
  • 1,036
  • 10
  • 16
  • Thanks, I do like that better. Any idea for preserving ID order with DQL? – Stephen Watkins Apr 16 '11 at 16:23
  • no, I don't think that is possible. You either have to read them one by one, in the sequence you want to have them, or sort them afterwards. (I edited my original answer with an example) – tvlooy Apr 18 '11 at 13:51
  • I'm pretty sure now that it's not possible. You can do the following in SQL: "select * from table order by (case id when 3 then 0 when 1 then 1 when 4 then 2 end)" But CaseExpression not yet supported in Doctrine2, it doesn't work. – tvlooy Apr 18 '11 at 14:15
  • Instead of declaring a global variable wich is not a good practice, you can rely on `use` keyword like this: `function cmp($a, $b) use($ids) {}`. – julesbou May 13 '12 at 21:30
1

You should pas the $ids array through setParameter() function as this is a best practice in doctrine:

$query = $this->_em->createQuery('SELECT b FROM BlogPost WHERE b.id IN (?1)');
$query->setParameter(1, implode(',', $ids));

I think the IN statement will not preserve the order of the passed ID's as IN will match the first found $id not depending on the order.

tom
  • 8,189
  • 12
  • 51
  • 70