Doctrine 1.2, running query over a Many to Many table

Question

I want to make a query on an automatic generated table under Doctrine 1.2. In this particular case I have the following query:

SELECT F.id FROM ficha as F JOIN ficha_has_tema FT ON FT.ficha_id = F.id WHERE FT.tema_id = ? GROUP BY F.id HAVING COUNT(F.id) > 1

But I get the error:

Uncaught exception 'Doctrine_Exception' with message 'Couldn't find class ficha_has_tema' in...

So, is there a way to make this query using doctrine without creating the class ficha_has_tema? I need to do the COUNT.

Thanks!

Answer 1

I assume you are executing DQL query here. It's impossible to use tables that have no associated entities wihthin the SQL queries.

However, you may run good-old SQL with

$q = Doctrine_Manager::getInstance()->getCurrentConnection();
$result = $q->execute("SELECT F.id FROM ficha as F JOIN ficha_has_tema FT ON FT.ficha_id = F.id WHERE FT.tema_id = ? GROUP BY F.id HAVING COUNT(F.id) > 1");

as suggested in "Using Raw SQL with Doctrine"

Or Doctrine Native SQL Support with something like (never tried that personally, might require some modification)

$q = new Doctrine_RawSql();
$q->select("{F.id}")
  ->from("
        From ficha as F 
        JOIN ficha_has_tema FT ON FT.ficha_id = F.id 
        WHERE FT.tema_id = ? 
        GROUP BY F.id 
        HAVING COUNT(F.id) > 1"
    );

$result = $q->execute(array($tema_id));