Why doesn't a column alias work in a doctrine?

Question

My script looks like this:

$query = Doctrine_Query::create () ->select('count(p.product_id) as num_a') ->from ( 'ProductComments p' ) ->groupBy('p.product_id') ->having('num_a =2 '); 

And the generated sql:

 SELECT COUNT(i.product_id) AS i__0 FROM productcomments i GROUP BY i.product_id HAVING num_a=2 

Thus, I get an error when sql is executed.

I have two questions:

• why is it an alias of table 'i' instead of 'p' ?

• why is 'num_a' in that the sentence is not replaced by 'i__0' , how to fix it?

Thank you for your offer...

Answer 1

1: why the table alias is 'i' instead of 'p'?

2: why 'num_a' is that the sentence is not replaced by 'i__0', how to fix it?

Both questions simply answer: Doctrine uses its own aliases for the request. You do not need to know these aliases, as they will not affect you, and you will not have to work with it.

Despite the fact that Doctrine calls the alias i__0 , you can access this attribute using your custom alias, for example. $yourObject->num_a will have the correct value, namely the result of count(p.product_id) .

To see that the result of your query is a useful debugging function, but relying on the inside of your application does not make sense, since these values ​​are used only for Doctrine's internal mechanisms.

Answer 2

I also had a problem setting up an alias. I had to set an alias and then use "ORDER BY" with that alias. The following solution worked for me:

 $myQuery->addSelect('(<my select>) AS my_alias'); $myQuery->orderBy('my_alias'); 

As a result, the query looked like this: "... () AS p_0 ... ORDER BY p_0". Hope this helps someone.

Answer 3

This is not valid SQL.

The standard SQL state that SELECT will logically execute after having . So you need to repeat the code with an alias in having .

Good advice. As long as you work with SQL-consuming databases, stick to SQL as closely as possible.