Yii2 -两个查询的联合

Question

大家早上好,

我需要一点帮助。我有两个SQL查询需要联合。这是我的第一个问题..(抱歉，如果这会很长的话)

$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]);

我的第二个问题是..

$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]);

我试着用这个联盟

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

但它似乎没有正确地合并这两个查询，因为这会导致结果加倍。这似乎有什么问题。提前谢谢。

Answer 1

试试这个：

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

 print_r($unionQuery);

有关官方Site的更多详细信息