在yii2中执行原始查询到查询生成器

ujv3wf0j 于 2022-11-09 发布在其他

关注(0)|答案(2)|浏览(114)

当我使用子查询获取记录时，如何将此原始查询转换为查询构建器yii2：

原始查询：

SELECT *
FROM (
    SELECT DISTINCT `comp`.*,TIMESTAMPDIFF(HOUR,comp.created_date,comp.updated_date) 
    AS hours
    FROM `complaints` `comp`
    INNER JOIN `complaint_log` `comp_log` ON comp_log.`comp_id` = comp.`id`
    WHERE ((comp.name LIKE '%%' OR comp.id LIKE '%%' OR comp.phone_no LIKE '%%') 
    AND DATE(comp.created_date) 
    BETWEEN '2018-01-01' AND '2020-03-20') 
    AND (comp.is_delete = 0)
    ORDER BY `comp`.`id` DESC, `comp`.`created_date` DESC)
    AS cte_name WHERE hours > 120;

Yi2查询

$query = (new \yii\db\Query())
       ->select('comp.*')
       ->from('complaints comp
       ->distinct()
       ->innerJoin('complaint_log as comp_log', 'comp_log.`comp_id` =comp.`id`')
       ->where($search)
       ->andWhere('comp.is_delete = 0')
       ->orderBy("comp.id DESC")
       ->addOrderBy('comp.created_date DESC');

yii

来源：https://stackoverflow.com/questions/60769565/performing-raw-query-into-query-builder-in-yii2

2条答案

按热度按时间

x6h2sr281#

请尝试以下代码：

$subquery =  (new \yii\db\Query())
        ->select(['comp.*', 'TIMESTAMPDIFF(HOUR, comp.created_date, comp.updated_date) AS hours'])
        ->distinct()
        ->from('complaints comp')
        ->innerJoin('complaint_log comp_log', 'comp_log.comp_id = comp.id')
        ->where(['or',
            ['like', 'comp.name', $search_name],
            ['or',
                ['like', 'comp.id', $search_id],
                ['like', 'comp.phone_no', $search_phone],
            ]
        ])
        ->andWhere('DATE(comp.created_date) BETWEEN :cStart AND :cEnd', [':cStart' => '2018-01-01', ':cEnd' => '2020-03-20'])
        ->andWhere(['comp.is_delete' => 0])
        ->orderBy(['comp.id' => SORT_DESC, 'comp.created_date' => SORT_DESC])
    ;
    $query = (new \yii\db\Query())
        ->select('cte_name.*')
        ->from(['cte_name' => $subquery])
        ->where(['>', 'cte_name.hours', 120])
    ;

赞(0）回复(0）举报 2022-11-09

wyyhbhjk2#

可以使用createCommand()函数：请使用下面查询。

$connection = \Yii::$app->db;
$query = $connection->createCommand(
'
  SELECT * FROM (
    SELECT DISTINCT comp.*,TIMESTAMPDIFF(HOUR,comp.created_date,comp.updated_date) AS hours
    FROM complaints comp
    INNER JOIN complaint_log comp_log ON comp_log.comp_id = comp.id
    WHERE ('.$search.')
    AND (comp.is_delete = 0)
    ORDER BY comp.id DESC, comp.created_date DESC
  ) AS cte_name WHERE hours > 120;
');
$data = $query->queryAll();

将原始查询放在createCommand()函数中。这里我使用了您在子查询中使用的$search变量

赞(0）回复(0）举报 2022-11-09

我来回答

在yii2中执行原始查询到查询生成器

2条答案

相关问题

热门标签

最新问答