Getting Doctrine to use MySQL "FORCE INDEX"

Question

I have a query in Doctrine's DQL that needs to be able to use MySQL's "FORCE INDEX" functionality in order to massively reduce the query time. Below is what the query basically looks like in plain SQL:

SELECT id FROM items FORCE INDEX (best_selling_idx)
WHERE price = ... (etc)
LIMIT 200;

I assume I have to extend some Doctrine component to be able to do this with DQL (or is there a way to inject arbitrary SQL into one of Doctrin's queries?). Anyone have any ideas?

Thanks!

Answer 1

See the Native SQL part of the Doctrine documentation. You should be able to use RawSql to accomplish this.

Answer 2

I've found very few helpful Doctrine_RawSql examples online, so here's what I ended up doing to create my query.

$q = new Doctrine_RawSql();
$q->select('{b.id}, {b.description}, {c.description}')
  ->from('table1 b FORCE INDEX(best_selling_idx) INNER JOIN table2 c ON b.c_id = c.id')
  ->addComponent('b', 'Table1 b')
  ->addComponent('c', 'b.Table2 c');