Zend Framework2 how to make Concat and Left Join

Question

Does not work:

$sql = new Sql($this->adapter);

$select = $sql->select();
$select->from('request')
           ->columns(array('*', new Expression("CONCAT(up1.value,' ',up2.value) as display_name")))
            ->join(array('up1'=>'user_profile'), "up1.user_id = request.request_user_id AND up1.key = 'user_first_name'", array('up1.value'), 'left')
           ->join(array('up2'=>'user_profile'), "up2.user_id = request.request_user_id AND up2.key = 'user_last_name'", array('up2.value'), 'left')
;

return $select;

How to make the right?

Max Ivanov · Answer 1 · 2013-07-14T16:04:29.743

You specified fields for both joins:

        ->join(array('up1'=>'user_profile'), "up1.user_id = request.request_user_id AND up1.key = 'user_first_name'", array('up1.value'), 'left')
       ->join(array('up2'=>'user_profile'), "up2.user_id = request.request_user_id AND up2.key = 'user_last_name'", array('up2.value'), 'left')

When query is converted to actual sql, these fields will be automatically namespaced, so you will get "up1.up1.value" in fields list.

Remove fields references from joins and it should work.

UPD. Right, there's more to it. You can't pass 'user_first_name' as a string value to the "on" parameter of join as it will be interpreted as a column name. So you have to pass an expression:

    $select = $sql->select();
    $select->from('request')
           ->columns(array('*', new Expression('CONCAT(up1.value,"#",up2.value) as display_name')));

    $expressionString = '? = ? AND ? = ?';
    $types = array(Expression::TYPE_IDENTIFIER, Expression::TYPE_IDENTIFIER, Expression::TYPE_IDENTIFIER, Expression::TYPE_VALUE);

    $parameters = array('request.user_id', 'up1.user_id', 'up1.key', 'first_name');
    $expression1 = new Expression($expressionString, $parameters, $types);

    $parameters = array('request.user_id', 'up2.user_id', 'up2.key', 'last_name');
    $expression2 = new Expression($expressionString, $parameters, $types);

    $select->join(array('up1'=>'user_profile'), $expression1, array('value'), 'left')
           ->join(array('up2'=>'user_profile'), $expression2, array('value'), 'left');

->columns(array('*', new Expression('CONCAT(up1.value,"#",up2.value) as display_name'))) ->join(array('up1'=>'user_profile'), 'r.request_user_id = up1.user_id AND up1.key = "user_first_name"', array('value'), 'left') ->join(array('up2'=>'user_profile'), 'r.request_user_id = up2.user_id AND up2.key = "user_last_name"', array('value'), 'left') - does not work — Mikhail Kiselev, Jul 14 '13 at 09:32

score 0 · Answer 2 · answered Jul 17 '13 at 15:04

Y tri to:

$select = $sql->select();
$select->from('request')
           ->columns(array('*', new Expression('CONCAT(up1.value,"#",up2.value) as display_user_name')));

$expressionString = '? = ? AND ? = ?';
$types = array(Expression::TYPE_IDENTIFIER, Expression::TYPE_IDENTIFIER, Expression::TYPE_IDENTIFIER, Expression::TYPE_VALUE);

$parameters1 = array('request.user_id', 'up1.user_id', 'up1.key', 'user_first_name');
$expression1 = new Expression($expressionString, $parameters1, $types);

$parameters2 = array('request.user_id', 'up2.user_id', 'up2.key', 'user_last_name');
$expression2 = new Expression($expressionString, $parameters2, $types);

$select->join(array('up1'=>'user_profile'), $expression1, array('value'), 'left')
           ->join(array('up2'=>'user_profile'), $expression2, array('value'), 'left');

does not work, where the error

Zend Framework2 how to make Concat and Left Join

2 Answers2