Laravel fromSubquery()语法翻译。Postgres语句
Laravel fromSubquery()语法翻译。Postgres语句
提问于 2020-06-17 16:34:03
从laravel 6开始，可以添加From Subquery语句。
https://github.com/laravel/framework/pull/29602
给出的语法示例如下：
$query->select('name')
->from(function ($query) {
$query->something();
}, 'users')
->get();
DB::table(function ($query) {
$query->something();
}, 'users')->get();我非常困惑这两者之间的区别以及如何实现它。给定以下postgresSQL查询，以查找用户执行会话的streaks (连续日期)。
SELECT COUNT(*) streak, SUM(amount) streakAmount, MIN(date) startDate, MAX(date) endDate, dateMinusRow dateMinusRow
FROM (
SELECT COUNT(*) amount, date_trunc('day', start) date,
date_trunc('day', start) - INTERVAL '1' DAY * DENSE_RANK() OVER (ORDER BY date_trunc('day', start)) dateMinusRow
FROM sessions
WHERE user_id = ".$this->user->id."
GROUP BY date_trunc('day', start)
) groupedDays
GROUP BY dateMinusRow我在理解如何添加周围的查询时遇到了问题，我想我已经理解了from()部分：
DB::table(function ($query) {
$query->selectRaw("
COUNT(*) amount,
date_trunc('day', start) date,
date_trunc('day', start) - INTERVAL '1' DAY * DENSE_RANK() OVER (ORDER BY date_trunc('day', start)) dateMinusRow"
)
->from('sessions')
->where('user_id', $this->user->id)
->groupByRaw("date_trunc('day', start)");
}, 'groupedDays')但是我不明白应该如何将顶级的select和groupBy子句
回答 1
Stack Overflow用户
发布于 2020-06-17 21:39:11
您不是被强制使用该语法的。
使用subquery.
table()时，from()用于指定表使用subquery.table()指定要查询的主表。
您可以在这两个方法中传递一个可选的别名作为第二个参数。
示例：
DB::table('users', 'u')->select('id as ID')->get();
// select id as ID from users as u
DB::table(function($sq) {
$sq->select('id')->from('users'); // must specify from when using subquery
})->select('id')->get();
// select id from (select id from users)
DB::table(function($sq) {
$sq->select('id'); // not specifying from leads to a bad query
})->select('id')->get();
// select id from (select id)如果您希望将查询彼此分开，也可以这样做
$subquery = DB::table('users')->select('id'); // $subquery will be a Query Builder instance you can pass to table() or from() instead of a Closure.
$query = DB::table($subquery, 'alias')->select('id');
$results = $query->get();
// select id from (select id from users) as alias我的建议是:使用toSql()方法来确保查询是正确的。首先，只执行子查询。
SELECT
COUNT(*) streak,
SUM(amount) streakAmount,
MIN(date) startDate,
MAX(date) endDate,
dateMinusRow
FROM
# Start Subquery
(
SELECT
COUNT(*) amount,
date_trunc('day', start) date,
date_trunc('day', start) - INTERVAL '1' DAY * DENSE_RANK() OVER (ORDER BY date_trunc('day', start)) dateMinusRow
FROM sessions
WHERE user_id = ".$this->user->id."
GROUP BY date_trunc('day', start)
) groupedDays
# End Subquery
GROUP BY dateMinusRow$subquery = DB::table('sessions')
->selectRaw(
"COUNT(*) as amount, "
."date_trunc('day', start) as date, "
."date_trunc('day', start) - INTERVAL '1' DAY * DENSE_RANK() OVER (ORDER BY date_trunc('day', start)) as dateMinusRow "
)
->where('user_id', $this->user->id)
->groupByRaw("date_trunc('day', start)");
// Verify the subquery is correct by dumping $subquery->toSql();
$query = DB::table($subquery, 'groupedDays')
->selectRaw(
"COUNT(*) as streak, "
."SUM(amount) as streakAmount, "
."MIN(date) as startDate, "
."MAX(date) as endDate, "
."dateMinusRow "
)
->groupBy('dateMinusRow');
// Verify the query is correct by dumping $query->toSql();
$results = $query->get();我不是postgres的专家，所以我假设原始查询是有效的。
编辑
from()没有出现在我最初的答案中，因为我没有使用闭包子查询，而是传入了一个Query Builder实例。如果我使用from()，它会是这样的。在我看来，使用它会更加混乱。
$query2 = DB::table(function($subquery) {
$subquery->from('sessions')
->selectRaw(
"COUNT(*) as amount, "
."date_trunc('day', start) as date, "
."date_trunc('day', start) - INTERVAL '1' DAY * DENSE_RANK() OVER (ORDER BY date_trunc('day', start)) as dateMinusRow "
)
->where('user_id', $this->user->id)
->groupByRaw("date_trunc('day', start)");
}, 'groupedDays')
->selectRaw(
"COUNT(*) as streak, "
."SUM(amount) as streakAmount, "
."MIN(date) as startDate, "
."MAX(date) as endDate, "
."dateMinusRow "
)
->groupBy('dateMinusRow');
// Verify the query is correct by dumping $query2->toSql();
$results = $query->get();转储这两个查询时，我在Sql中得到的唯一区别似乎是使用了反引号。
>>> $query->toSql()
=> "select COUNT(*) as streak, SUM(amount) as streakAmount, MIN(date) as startDate, MAX(date) as endDate, dateMinusRow from (select COUNT(*) as amount, date_trunc('day', start) as d
ate, date_trunc('day', start) - INTERVAL '1' DAY * DENSE_RANK() OVER (ORDER BY date_trunc('day', start)) as dateMinusRow from "sessions" where "user_id" = ? group by date_trunc('day
', start)) as "groupedDays" group by "dateMinusRow""
>>> $query2->toSql()
=> "select COUNT(*) as streak, SUM(amount) as streakAmount, MIN(date) as startDate, MAX(date) as endDate, dateMinusRow from (select COUNT(*) as amount, date_trunc('day', start) as d
ate, date_trunc('day', start) - INTERVAL '1' DAY * DENSE_RANK() OVER (ORDER BY date_trunc('day', start)) as dateMinusRow from `sessions` where `user_id` = ? group by date_trunc('day
', start)) as `groupedDays` group by `dateMinusRow`"查询的问题可能与数据库驱动程序的选项之一有关。我不确定，但举个例子，一些group by查询在Laravel上不起作用，即使生成的sql代码是正确的，除非mysql驱动程序中禁用了严格模式。
至于我为什么使用as关键字，我使用的数据库通常不是mysql就是postgresql，所以我倾向于使用它们共享的符号。
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/62424770
复制相关文章
相似问题
腾讯云开发者
