CODEWITHSUNDEEP
mysqlyii2
ii--L--ii
ii--L--ii

Reputation: 207

Yii2 - Union of two queries

Good morning Everyone,

I need a little help. I have 2 SQL queries that i need to unite. This is my first query.. (sorry if this is gonna be long)

$query1 = new Query();
$query1->params([':category' => $category, ':ownerId' => $ownerId]);
$query1->select('tmp.id, tmp.title , tmp.description, asst.thumbAssetUrl, b1.paramVal as duration, b2.paramVal as clips, asst.fileUrl as video')
    ->from('listdb.baseData tmp')
    ->innerJoin(
        'listdb.tag tag',
        'tag.baseDataId = tmp.id and tag.tag = :category and tag.status = "active"'
    )
    ->innerJoin(
        'listdb.baseParam b0',
        'b0.baseDataId = tmp.id
        and ((b0.paramName = "role"
        and (b0.paramValue = "public"))
        or ((select count(*) from listdb.baseParam temp
        where temp.baseDataId = tmp.id and paramName = "role" )=0))
        or (b0.paramName = "role" and b0.paramValue = "public" and tmp.owner = :ownerId)'
    )
    ->leftJoin(
        'listdb.baseParam b1',
        'b1.baseDataId = tmp.id and b1.paramName="duration" and b1.status = "active"'
    )
    ->leftJoin(
        'listdb.baseParam b2',
        'b2.baseDataId = tmp.id and b2.paramName="itemCount" and b2.status = "active"'
    )
    ->leftJoin(
        'listdb.baseParam b3',
        'b3.baseDataId = tmp.id and b3.paramName="previewUrl" and b3.status = "active"'
    )
    ->leftJoin('assetdb.baseData asst', 'asst.id = b3.paramValue and asst.status = "active"')
    ->where('tmp.status = "active" and tmp.application = "template" and tmp.role = "public"')
    ->groupBy('tmp.id')
    ->orderBy(['tmp.upTime' => SORT_DESC]);

and my second query is this..

$query2 = new Query();
$query2->params([':category' => $category, ':ownerId' => $ownerId]);
$query2->select('tmp.id, tmp.title , tmp.description, asst.thumbAssetUrl, b1.paramValue as duration, b2.paramValue as clips, asst.fileUrl as video')
    ->from('listdb.baseData tmp')
    ->innerJoin(
        'listdb.tag tag',
        'tag.baseDataId = tmp.id and tag.tag = :category and tag.status = "active"'
    )
    ->innerJoin(
        'listdb.baseParam b0',
        'b0.baseDataId = tmp.id
        and ((b0.paramName = "role"
        and (b0.paramValue = "private" or b0.paramValue = "" and b0.paramValue != "public"))
        or ((select count(*) from listdb.baseParam temp
        where temp.baseDataId = tmp.id and paramName = "role" )=0))
        or (b0.paramName = "role" and b0.paramValue = "public" and tmp.owner = :ownerId)'
    )
    ->leftJoin(
        'listdb.baseParam b1',
        'b1.baseDataId = tmp.id and b1.paramName="duration" and b1.status = "active"'
    )
    ->leftJoin(
        'listdb.baseParam b2',
        'b2.baseDataId = tmp.id and b2.paramName="item_count" and b2.status = "active"'
    )
    ->leftJoin(
        'listdb.base_parambaseParameter b3',
        'b3.baseDataId = tmp.id and b3.paramName="previewUrl" and b3.status = "active"'
    )
    ->leftJoin('assetdb.baseData asst', 'asst.id = b3.paramValue and asst.status = "active"')
    ->innerJoin('listdb.childRestricted cr', 'cr.baseDataId = tmp.id  and cr.status = "active"  and cr.owner = :ownerId')
    ->where('tmp.status = "active" and tmp.application = "template" and tmp.role = "private"')
    ->groupBy('tmp.id')
    ->orderBy(['tmp.upTime' => SORT_DESC]);

i tried using this union 

$query = $query2->union($query1, false);

but it seems that it does not unite the two queries properly because this causes the results to double. What seems to be the problem with this. Thanks in advance.

Upvotes: 0

Views: 2092

Answers (1)

Muhammad Shahzad
Muhammad Shahzad

Reputation: 9652

Try this: 

 $unionQuery = (new \yii\db\Query())
    ->from(['dummy_name' => $query1->union($query2)]);

 print_r($unionQuery);

More detail on Official Site

Upvotes: 2

Related Questions