php - framework - Cómo usar la unión en zend db

zend framework db limit

Question

En sql estoy usando la unión, no sé cómo escribirla en zend db.

select m.*, 0 as is_shared from test m where user_id = $userId 
union
select m.*,1 as is_shared from test m 
join test_shares ms 
where m.test_id = ms.test_id 
and ms.email_address = $email 
and m.url is not null;

Por favor, ayúdame....

Intenté así, pero no uso

$cols1 = array(''test.*,0 as is_shared'');
$select1 = $db->select ()
    ->from ( ''test'', $cols1 )
    ->where ( ''user_id = ?'', $userId);

$cols2 = array(''test_shares.*'', ''1 as is_shared'');
$select2 = $db->select ()
    ->from ( ''test'', $cols2 )
    ->join ( ''test_shares'', array () )
    ->where ( ''test.test_id = test_shares.test_id'')
    ->where ( ''test_shares.email_address = ?'',  $email)
    ->where ( ''test.url is NOT NULL'');

$select = $db->select()
             ->union(array($select1, $select2))
             ->order(''title'');

Está tomando ''prueba''. ''0'' AS ''is_shared'' así pero necesito este 0 como is_shared. Está tomando ''prueba''. ''1'' AS ''is_shared'' así pero necesito este 1 como is_shared.

Answer 1

De acuerdo con la documentación de Zend_Db_Select , puede crear una consulta para cada miembro de la unión (pueden ser cadenas o objetos Zend_Db_Select ) y luego llamar al método union() de Zend_Db_Select .

Algo como:

$sql1 = FIRSTPARTOFTHEQUERY;
$sql2 = SECONDPARTOFTHEQUERY;
$select = $db->select();
$select->union(array($sql1, $sql2));

Espero que ayude,

Answer 2

No sé que nuestro sql es trabajo. Pero puede hacer el siguiente código.

$userId = 10;
$email = ''[email protected]'';

$select1 = $db->select()
                ->from(array(''m'' => ''test''), array(''*'', ''0 AS is_shared''))
                ->where(''user_id =?'', $userId);

$select2 = $db->select()
                ->from(array(''m'' => ''test''), array(''*'', ''1 AS is_shared''))
                ->join(array(''ms'' => ''test_shares''), ''m.test_id = ms.test_id'', '''')
                ->where(''ms.email_address =?'', $email)
                ->where(''m.url IS NULL'');                  

$select = $this->select()
     ->union(array($select1, $select2))
     ->order(''title'');

echo $select; die;

        /*SELECT `m`.*, `m`.`0` AS `is_shared` 
         * FROM `test` AS `m` 
         * WHERE (user_id =10) 
         * UNION 
         * SELECT `m`.*, `m`.`1` AS `is_shared` 
         * FROM `test` AS `m` 
         * INNER JOIN `test_shares` AS `ms` 
         * ON m.test_id = ms.test_id 
         * WHERE (ms.email_address =''[email protected]'') AND (m.url IS NULL) 
         * ORDER BY `title` ASC*/

Answer 3

Para referencia futura, en Zend Framework 2.3 esto se hace con combinar.

Por ejemplo:

    use /Zend/Db/Sql/Select;
    use /Zend/Db/Sql/Sql;

    $sql = new Sql(/* ADAPTER HERE */);

    $tag1 = new Select( [''a'' => ''articles''] );
    $tag1->columns( [ ''tag'' => ''first_tag''] );
    $tag1->where->in(''a.id'', $articleIds);

    $tag2 = new Select( [''a'' => ''articles''] );
    $tag2->columns( [ ''tag'' => ''second_tag''] );
    $tag2->where->in(''a.id'', $articleIds);
    $tag2->combine($tag1);

    $tag3 = new Select( [''a'' => ''articles''] );
    $tag3->columns( [ ''tag'' => ''third_tag''] );
    $tag3->where->in(''a.id'', $articleIds);
    $tag3->combine($tag2);

    $statement = $sql->prepareStatementForSqlObject($tag3);