举杯邀月

yii2中链表查询、子查询使用方法

摘要:在写复杂的查询时,个人的习惯一般是先把逻辑列出来,然后写出原生的sql查询验证,最后在转成yii框架语法,今天在工作中遇到了一个比较复杂的查询,然后整理下记录下来

在写复杂的查询时,个人的习惯一般是先把逻辑列出来,然后写出原生的sql查询验证,最后在转成yii框架语法,今天在工作中遇到了一个比较复杂的查询,然后整理下记录下来

写了一上午,其中用到了四个表以及子查询的查询方式,最终的sql的格式如下:

SELECT
	*
FROM
	(
		SELECT
			t1.field_1,
			t2.field_2
		FROM
			table_1 AS t1
		LEFT JOIN table_2 AS t2 ON t1.id = t2.id
		WHERE
			t1. STATUS = 1
		ORDER BY
			t1.id DESC
	) AS result_1
LEFT JOIN (
	SELECT
		t3.field_3,
        t4.field_4
	FROM
		(
			SELECT
			    field_3	
			FROM
				table_3
			WHERE
				STATUS = 1
			GROUP BY
				date
		) AS t4
	LEFT JOIN table_4 AS t4 ON t3.id = t4.id
) AS result_2 ON result_1.field_1 = result_2.field_3
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32

最终转成yii2框架格式如下

use yii\db\Query;
$query_1 = Table1::find()
            ->alias('t1')
            ->leftJoin(Table2::tableName().' t2', 't1.id = t2.id')
            ->where(['t1.status'=>1])
            ->orderBy('t1.id ASC')
            ->select('t1.field_1,t2.field_2');

$query_2 = Table3::find()
            ->where(['status'=>1])
            ->select('field_3')
            ->groupBy("date");

$subQuery = (new Query())
            ->from(['t3'=>$query_2])
            ->leftJoin(Table4::tableName().' t4', 't3.id = t4.id')
            ->select("t3.field_3,t4.field_4")
            ->groupBy("date");

$tdata = (new Query())
            ->from(['result_1'=>$query_1])
            ->leftJoin(['result_2'=>$subQuery], 'result_1.field_1 = result_2.field_3')
            ->all();
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

作者:举杯邀月

出处: http://www.hug-code.cn/archives/5fc0b109ade4b.html

2020-03-27 标签: phpyii2