How can I join a subquery using Zend_Db_Select

Question

How would I construct this query using Zend_Db_Select?:

SELECT users.user_id, email_address, t1.value as 'languages'    
FROM users
LEFT JOIN (
  SELECT
    user_id
    , field_id
    , GROUP_CONCAT(value SEPARATOR ',') AS value
  FROM user_multivalued
  WHERE field_id=25
  GROUP BY user_id, field_id) t1
ON t1.user_id = users.users_id
WHERE list_id = 45

It looks it can be done like this: $subscribers->joinLeft(array($tableAlias => new Zend_Db_Expr('(' . $query . ')' )), $joinCondition, array($fieldInfo['name'] => 'value')); — Oleg
– Oleg, Commented Jan 18, 2011 at 13:42

stollr · Accepted Answer · 2016-10-17 07:19:16Z

35

$user_multivalued = $db
    ->select()
    ->from('user_multivalued', array(
        'user_id',
        'field_id',
        new Zend_Db_Expr("GROUP_CONCAT(value SEPARATOR ',') AS value")
    ))
    ->where('field = ?', 25)
    ->group('user_id')
    ->group('field_id')
;

$select = $db
    ->select()
    ->from('users', array('user_id', 'email_address'))
    ->joinLeft(
        array('t1' => $user_multivalued),
        't1.user_id = users.user_id',
        array('languages'=>'value')
    )
    ->where('list_id = ?', 45)
;

edited Oct 17, 2016 at 7:19

stollr

7,3495 gold badges48 silver badges64 bronze badges

answered Feb 4, 2011 at 16:56

Stephen Fuhry

13.1k6 gold badges59 silver badges57 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Ariel Allon Over a year ago

Important to note (threw me off while I was trying to figure this out until I found this answer): in the first joinLeft() parameter, make sure you pass it as the Zend_Db_Expr object, not the __toString() version of it.

Collectives™ on Stack Overflow

How can I join a subquery using Zend_Db_Select

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related