ZEND FRAMEWORK => CONCAT to Select

Question

I have this query:

->join(array('p'=>'persona'),'u.persona_id=p.id',array('nomp'=> new Zend_Db_Expr("CONCAT(p.nombre, ' ', p.apellidos)")))

I concatenated (p.nombre y p.apellidos) and give them that has the name of "nomp"

When I do the "Where" and give it a "LIKE" I look for the column "nomp" zend tells me that column does not exist.

You know any other way to concatenate.

Answer 1

Hi you can try this

 ->join(array('us' => 'user_subscriptions'), "user_name" => new Expression("CONCAT(first_name,' ',last_name)"), array('user_end_date' => 'end_date', 'sso_subscription_status' => 'subscription_status'), 'left');

i hope it will help you

Answer 2

Consider this SQL:

Select x.id, CONCAT(fname,' ',lname) as name 
from 
(Select 1 as id) x
inner join 
(Select 1 as id,"Tom" as fname,"Johns" as lname) p on x.id = p.id
WHERE name = 'Tom Johns'

It is similar to what you are actually generating. Third parameter in join is part of main select list that will be added for that table. As you see above variable name is defined in select list and therefor you cannot use it in WHERE. Your solution would be to change SQL to something like this.

WHERE CONCAT(fname,' ',lname) like 'Tom Johns'