Zend_Db_Select query with UNION and IN operator

Question

How can i write this query with Zend_Db_Select objects?

SELECT
    `v1`.`id`,
    `v1`.`title`,
    `v1`.`duration`,
    `v1`.`img`
FROM 
    `videos` AS `v1` 
WHERE 
    v1.id IN (
        SELECT id_video FROM videos_categories WHERE id_video NOT IN(
            select id_video from videos_categories where id_category=34
            UNION
            select id_video from videos_categories where id_category=20
        )
    )

I tried something like this but nothing works, i've got an error page. I use datamappers

$objQuery = $this->getDbTable()->select()
    ->from(array('v'=>'videos'),array('v.id','v.title','v.duration','v.img'))

$tableVC1 = new Application_Model_DbTable_VideosCategories(); 
$tableVC2 = new Application_Model_DbTable_VideosCategories();
$tableVC3 = new Application_Model_DbTable_VideosCategories();
$tableVC4 = new Application_Model_DbTable_VideosCategories();

// select id_video from videos_categories where id_category=20
$tableVC4->select()->from(array("vc"=>"videos_categories"),array("id_video"))
    ->where("vc.id_category=20");

// select id_video from videos_categories where id_category=34
$tableVC3->select()->from("videos_categories","id_video")
    ->where("id_category=34");

// union between previous queries
$tableVC2->select()->union(array($tableVC4,$tableVC3));

$tableVC1->select()->from("videos_categories","id_video")
    ->where("id_video NOT IN ?",$tableVC2);

$objQuery->where("v.id IN ?",$tableVC1);

Thx for helping me.

Application error ... nothing shows, i tried to do an $objQuery->__toString() but nothing again — Alex P.
– Alex P., Commented Apr 13, 2012 at 15:20

Liyali · Accepted Answer · 2012-04-14 03:23:57Z

1

My guess is that you're trying to send an object to your union, when it expects a string.

Try this:

$objQuery = $this->getDbTable()
                 ->select()
                 ->from(array('v' => 'videos'),
                        array('v.id', 'v.title', 'v.duration', 'v.img'))

$tableVC1 = new Application_Model_DbTable_VideosCategories(); 
$tableVC2 = new Application_Model_DbTable_VideosCategories();
$tableVC3 = new Application_Model_DbTable_VideosCategories();
$tableVC4 = new Application_Model_DbTable_VideosCategories();

// select id_video from videos_categories where id_category=20
$select4 = $tableVC4->select()
                    ->from(array("vc" => "videos_categories"),
                           array("id_video"))
                    ->where("vc.id_category=20");

// select id_video from videos_categories where id_category=34
$select3 = $tableVC3->select()
                    ->from("videos_categories", "id_video")
                    ->where("id_category=34");

// union between previous queries
$select2 = $tableVC2->select()
                    ->union(array($select4, $select3));

$select1 = $tableVC1->select()
                    ->from("videos_categories", "id_video")
                    ->where("id_video NOT IN ?", $select2);

$objQuery->where("v.id IN ?", $select1);

echo $objQuery; should output the expected query.

edited Apr 14, 2012 at 3:23

answered Apr 13, 2012 at 15:23

Liyali

5,6832 gold badges29 silver badges41 bronze badges

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

2 Comments

Alex P. Over a year ago

Thx Liyali!!! it works ... can you explain me please why now it works? i'm passing the same obj to the $select var

Liyali Over a year ago

$select[1|2|3|4] are instance of Zend_Db_Select, which implements a __toString()method. If you pass directly $tableVC1 to your union (or where clause) this mean that you are trying to pass an instance of Application_Model_DbTable_VideosCategories.

ryu · Accepted Answer · 2012-04-13 14:55:15Z

0

You have to make brackets around the ?. => (?) ... and the variable has to be a select object.

For example in this line:

$tableVC2 = $xyz->select()...

$tableVC1->select()->from("videos_categories","id_video")
        ->where("id_video NOT IN (?)",$tableVC2);

answered Apr 13, 2012 at 14:55

ryu

3123 silver badges6 bronze badges

1 Comment

ryu Over a year ago

I posted another answer, maybe this helps you.

ryu · Accepted Answer · 2012-04-13 15:04:30Z

0

Try build your SELECT statements like this, without models - it should work this way:

$db = Zend_Db_Table::getDefaultAdapter();
$select = $db->select()
    ->from(
        // base table
    )
    ->joinLeft(
        // join sth
    )
    ->where('x = ?', $x)
    ->where('y = ?', $y)
    ->order('z DESC')
    ->limit(10, 0);
$q = $db->query($select);
$rowSet = $q->fetchAll();

answered Apr 13, 2012 at 15:04

ryu

3123 silver badges6 bronze badges

2 Comments

Alex P. Over a year ago

i have more than 1 million records database, the problem is that i need UNION and IN operator, the same query with a join takes 2 minutes, with IN and UNION only 0,020 seconds

ryu Over a year ago

I didn't mean you should use join's. I meant you should use this kind of building your select statement. This way you can use the variable $select (the complete select object) inside the where method call of another select statement. If you do it with models Zend complains about table restrictions - in my experience.

Collectives™ on Stack Overflow

Zend_Db_Select query with UNION and IN operator

3 Answers 3

2 Comments

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related