Using Doctrine Subqueries / Errors with Aliases

Question

I am trying to make a simple request with a subquery in an orWhere clause (with Doctrine).

As always, Doctrine tries to rename all aliases and completely destroys requests ...

Here is an example:

$q = Doctrine_Query::create()
    ->from('Actualite a')
    ->where('a.categorie_id =?', $id)
    ->orWhere('a.categorie_id IN (select id from cat.categorie as cat where cat.categorie_id =?)', $id)
    ->execute();

What in MySQL will do something like:

SELECT * 
FROM actualite a 
WHERE a.categorie_id = 1 OR a.categorie_id IN (SELECT cat.id FROM categorie cat WHERE cat.categorie_id = 1);

Everything is fine, but again Doctrine destroys it: Could not find class cat

Every time I try to do something a little complicated with the Doctrine, I have errors with aliases. Any tips or ideas on how to fix this?

Thank!

Answer 1

The SQL example you provided is good, but the corresponding Doctrine syntax has a couple of errors. Here is the clean version:

$q = Doctrine_Query::create()
    ->select('a.*')
    ->from('Actualite a')
    ->where('a.categorie_id = ?', $id)
    ->orWhere('a.categorie_id IN (SELECT cat.id FROM Categorie cat WHERE cat.categorie_id = ?)', $id)
    ->execute();

Answer 2

createSubquery(), . :

$q = Doctrine_Query::create()
    ->select('a.*')
    ->from('Actualite a')
    ->where('a.categorie_id = ?', $id)
;

$subquery = $q->createSubquery()
    ->select("cat.id")
    ->from("Categorie cat")
    ->where("cat.categorie_id = ?", $id)
;

$q->orWhere('a.categorie_id IN ('.$subquery->getDql().')')->execute();

:

http://www.philipphoffmann.de/2012/08/taming-doctrine-subqueries/

Answer 3

I think you should look like this: add select and remove as

$q = Doctrine_Query::create()
    ->select('a.id')
    ->from('Actualite a')
    ->where('a.categorie_id =?', $id)
    ->orWhere('a.categorie_id IN (select id from cat.categorie cat where cat.categorie_id =?)', $id)
    ->execute();

Try it, it can help you.

thank