Doctrine Query help

Question

I need to perform sql query using doctrine. query is really simple

SELECT q.*, COUNT(a.question_id) FROM question AS q LEFT JOIN question_answers AS a ON q.id=a.question_id GROUP BY a.question_id

For this purpose i wrote following code

    $query = Doctrine_Query::create()
             ->select('q.*, COUNT(a.question_id) AS answers')
             ->from('Model_Question q')
             ->leftJoin('Model_QuestionAnswers a ON q.id = a.question_id')
             ->groupBy('a.question_id');

But this is not getting me desired query. Can anybody explain me what I am missing? Query that is generated I checked via $query->buildSqlQuery() is

SELECT q.id AS q__id, q.title AS q__title, q.short_description AS q__short_description, 
q.created_at AS q__created_at, q.updated_at AS q__updated_at, q.is_visible AS 
q__is_visible, q.category_id AS q__category_id, q.user_id AS q__user_id, q.category_name 
AS q__category_name, q.username AS q__username, COUNT(q2.question_id) AS q2__0 FROM 
question q, question_answers q2 GROUP BY q2.question_id

what happens if you remove the ON condition from the join (this should be the default condition judging from your model so there is no reason to specify it - it will be joined on these columns automatically)? — prodigitalson

ybull ybull · Accepted Answer · 2011-04-27T17:39:49

Because you didn't use the standard naming convention between tables and model names, Doctrine is getting confused about the join. I suspect you also might not have defined the relationship between the models/tables so that Doctrine can automatically build joins for you.

Try changing your leftJoin() call in that query to look like this:

->leftJoin('q.Model_QuestionAnswers a')

You may need to explicitly define the relation in your schema.yml file so a simple statement like the above will work. For example, at the end of the definition for Model_QuestionAnswers, you would add:

relations:
  Model_Question: 
    local: question_id
    foreign: id
    foreignAlias: Model_QuestionAnswers

Also a side note: I notice you're using both singular and plural names for your tables and models. It is conventional to choose one or the other and stick to it, especially when you're using frameworks and/or ORMs that need to be able to guess names and autogenerate code. I'm using Doctrine with the Symfony framework. At least in that context, models and table names are singular by convention.

Doctrine Query help

1 Answers