How can I join a subquery using Zend_Db_Select

11,591
$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)
;
Share:
11,591

Related videos on Youtube

Oleg
Author by

Oleg

Updated on June 04, 2022

Comments

  • Oleg
    Oleg over 1 year

    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
    
  • Ariel Allon
    Ariel Allon almost 12 years
    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.

Related