php 如何从 Laravel 中的自定义查询中分块结果
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/22048104/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to chunk results from a custom query in Laravel
提问by Donnie
I have a custom query that grabs data from the old system and maps it to models in the new system. The query looks like this:
我有一个自定义查询，它从旧系统中获取数据并将其映射到新系统中的模型。查询如下所示：
$companies = DB::connection('legacy')->select("...");
$companies = DB::connection('legacy')->select("...");
And since it's a lot of data, I'd like to use Eloquent's chunk feature (just sample code copied from their docs):
由于数据量很大，我想使用 Eloquent 的块功能（只是从他们的文档中复制的示例代码）：
User::chunk(200, function($users)
{
foreach ($users as $user)
{
//
}
});
How do I implement this?
我该如何实施？
Edit: My code now looks like this, which results in no response:
编辑：我的代码现在看起来像这样，导致没有响应：
DB::connection('legacy')->select("SELECT * FROM companies")->chunk(200, function($companies) {
foreach ($companies as $company) {
// dd($company);
$entity = Entity::firstOrNew(['external_id' => $company->companyKey]);
$entity->name = $company->companyName;
$entity->save();
}
});
采纳答案by deyes
Try something like this:
尝试这样的事情：
<?php
$max = 100;
$total = DB::connection('legacy')->select("...")->count();
$pages = ceil($total / $max);
for ($i = 1; $i < ($pages + 1); $i++) {
$offset = (($i - 1) * $max);
$start = ($offset == 0 ? 0 : ($offset + 1));
$legacy = DB::connection('legacy')->select("...")->skip($start)->take($max)->get();
/* Do stuff. */
}
Basically duplicates what Laravel's Paginator does without the extra overhead.
基本上复制了 Laravel 的分页器所做的事情，而没有额外的开销。
回答by AlexM
The chunkfeature is only available for Eloquent models and QueryBuilder requests, e.g.
该chunk功能仅适用于 Eloquent 模型和 QueryBuilder 请求，例如
DB::table('tbl')->where('num', '>', 3)->chunk(500, function($rows) {
// process $rows
});
But it won't work for DB::select('...')request.
You need to either use a QueryBuilder request, or use an underlying PDO object to query the database, e.g:
但它不适用于DB::select('...')请求。您需要使用 QueryBuilder 请求，或使用底层 PDO 对象来查询数据库，例如：
$pdo = DB::getPdo();
$sth = $pdo->prepare("SELECT ....");
$sth->execute();
while ($row = $sth->fetch(PDO::FETCH_ASSOC))
{
// ...
}
回答by Aiden Moon
deyes's answer has a bug.
deyes 的回答有一个错误。
AS-IS
原样
if 'legacy' table has 'id' column and there's 1,2,3,4,5 ... 100 numbered data.
如果 'legacy' 表有 'id' 列并且有 1,2,3,4,5 ... 100 个编号的数据。
<?php
$max = 10;
$total = DB::connection('legacy')->select("...")->count();
$pages = ceil($total / $max);
for ($i = 1; $i < ($pages + 1); $i++) {
$offset = (($i - 1) * $max);
$start = ($offset == 0 ? 0 : ($offset + 1));
$legacy = DB::connection('legacy')->select("...")->skip($start)->take($max)->get();
/* Do stuff. */
$legacyIds = $legacy->lists("id");
echo "i = " . $i . ": \n";
print_r($legacyIds);
}
//Result
i = 1:
Array
(
[0] => 1
[1] => 2
[2] => 3
[3] => 4
[4] => 5
[5] => 6
[6] => 7
[7] => 8
[8] => 9
[9] => 10
)
i = 2:
Array
(
[0] => 12
[1] => 13
[2] => 14
[3] => 15
[4] => 16
[5] => 17
[6] => 18
[7] => 19
[8] => 20
[9] => 21
) ...
TO-DO
去做
$max = 10;
$total = DB::connection('legacy')->select("...")->count();
$pages = ceil($total / $max);
for ($i = 1; $i < ($pages + 1); $i++) {
$offset = (($i - 1) * $max);
$legacy = DB::connection('legacy')->select("...")->skip($offset)->take($max)->get();
/* Do stuff. */
}
回答by Artur K?pp
Update:March 2018 a new function was added to the query builder class. It's possible to achieve the same now using fromSub:
更新：2018 年 3 月，查询构建器类中添加了一个新函数。现在可以使用fromSub以下方法实现相同的目标：
$subQuery = DB::table('users')->where(...);
DB::query()->fromSub($subQuery, 'alias')->orderBy('alias.id')->chunk(200, function ($chunk) {
// Do something
});
And to use a different connection start with DB::connection('legacy')->query()
并使用不同的连接开始 DB::connection('legacy')->query()
Old answer:Found this question by accident, but a little trick that might come handy in some cases.
旧答案：偶然发现这个问题，但在某些情况下可能会派上用场的小技巧。
$query = 'SELECT * FROM ... JOIN ... UNION ... WHATEVER ... GROUP BY';
// This is the important part:
$query = '(' . $query . ') somealias';
DB::connection('legacy')->table(DB::raw($query))->chunk(1000, function($rows){
// Do something
});
The query laravel executes then goes like this:
查询 laravel 执行然后是这样的：
select * from (...) somealias LIMIT ... OFFSET ...
This should work at least in Laravel 5.1. But I don't see a reason why it shouldn't work in 4+.
这应该至少在 Laravel 5.1 中有效。但我不明白为什么它不应该在 4+ 中工作。
回答by SamBremner
A lot of answers on this page do a lookup of all records in order to workout how many 'pages' there are. This can be slow and is not needed as we are not paginating we are chunking. We only need to know the total number of pages when we are paginating to show the user.
此页面上的许多答案都会查找所有记录，以计算有多少“页面”。这可能很慢并且不需要，因为我们没有分页我们正在分块。我们只需要在分页显示用户时知道总页数。
So an alternative to getting a count of all records at the start is to do the following:
因此，在开始时获取所有记录计数的替代方法是执行以下操作：
$recordsRemaining = true;
$lookupsCompleted = 0;
$chunkSize = 200;
while($recordsRemaining){
$companies = DB::connection('legacy')->select("...")->skip($chunkSize*$lookupsCompleted)->take($chunkSize)->get();
if($legacy->count() < $chunkSize){
$recordsRemaining = false;
}
foreach($companies as $company){
//Do something
}
$lookupsCompleted++;
}
This does exactly the same as the accepted answer but is more efficient.
这与接受的答案完全相同，但效率更高。
回答by Maciej Swic
None of these answers worked for me. I created my own function based on @deyes answer.
这些答案都不适合我。我根据@deeyes 答案创建了自己的函数。
private static function chunk($query, $max, $function) {
$counter = preg_replace('/SELECT (.*?) FROM/', 'SELECT COUNT(*) FROM', $query);
$total = DB::connection('legacy')->select($counter)[0];
$total = (array)$total;
$total = $total['COUNT(*)'];
$pages = ceil($total / $max);
for ($i = 1; $i < ($pages + 1); $i++) {
$offset = (($i - 1) * $max);
$start = ($offset == 0 ? 0 : ($offset + 1));
$items = DB::connection('legacy')->select($query . ' LIMIT ' . $offset . ', ' . $max);
$function($items);
unset($items);
}
}
Usage
用法
YourClass::chunk('SELECT * FROM tablename', 50, function($items) {
//Work with $items.
});
Please note that this a simple quick fix and your query probably has to be fairly simple as I'm using search-replace to build a count query and I'm just tacking on LIMIT X, Y to the end of the query but it works for me.
请注意，这是一个简单的快速修复，您的查询可能必须相当简单，因为我正在使用搜索替换来构建计数查询，而我只是将 LIMIT X, Y 附加到查询的末尾，但它有效为了我。
回答by David Normington
I believe you can use chunkon a query builder. E.g.
我相信您可以chunk在查询构建器上使用。例如
DB::connection('legacy')->select("...")->chunk(200, function($companies){
//do something with $companies
});
回答by Onkar
Try using the orderByclause:
尝试使用orderBy子句：
DB::table('tbl')->where('num', '>', 3)->orderBy('id')->chunk(500, function($rows) {
// process $rows
});
