Why does column alias not work in doctrine?

Question

My script is 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 is:

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 execute the sql.

I have two questions:

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

2. why is the 'num_a' in having clause not replaced with 'i__0',how to fixed it?

Thanks for your suggestion...

Answer 1

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

2: why is the 'num_a' in having clause not replaced with 'i__0',how to fixed it?

Both questions are simply answered: Doctrine uses it's own aliases for the query. You do no need to know these aliases since they will not affect you nor will you need to work with it.

Even though Doctrine names the alias i__0 you can access the attribute with your custom alias, e.g. $yourObject->num_a will have the proper value, namely the result of count(p.product_id).

To see the output of your query is a useful debug feature, but relying on in inside your application is non-sense since these values are only used for the internal mechanisms of Doctrine.

Answer 2

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

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

In the result query looked like "...() AS p_0 ... ORDER BY p_0". I hope it will help someone.

Answer 3

This would not be valid SQL.

SQL standard state that SELECT will be logically executed after having. So You need to repeat aliased code in having.

Good advice. As long as You work with SQL consuming DBs stick as closely to SQL as possible.