laravel 4 - 如何限制(接受和跳过)Eloquent ORM?

2021-12-26 00:00:00 limit mysql laravel laravel-4 eloquent

你能像使用 take()skip() 一样限制 Eloquent ORM 查询,这样得到的 mysql 查询也受到限制,而且它不必返回整个数据集?

Can you limit an Eloquent ORM query like using take() and skip() so that the resulting mysql query is also limited, and it doesn't have to return the entire dataset?

如果是这样,你会如何修改:

If so, how would you modify:

$test = User::find(1)->games->toArray();

要包含limit 3 offset 2?

users       games           userGames
-- id       -- id           -- user_id
-- name     -- name         -- game_id
            -- steam_id

型号:

class User extends Eloquent {
    public function games() {
        return $this->belongsToMany('Game', 'userGames', 'user_id', 'game_id');
    }
}

class Game extends Eloquent {
    public function users() {
        return $this->belongsToMany('User', 'userGames', 'user_id', 'game_id');
    }
}

<小时>

查询生成器中的限制

使用常规的 Laravel Query Builder 我可以获得属于 id 1 的 user 的所有 games,并用 take()skip():


Limit in Query Builder

Using the regular Laravel Query Builder I can get all games that belong to user of id 1, and limit the result with take() and skip():

$test = DB::table('games')
    ->join('userGames', 'userGames.game_id', '=', 'games.id')
    ->where('userGames.user_id', '=', '1')->take(3)->skip(2)->get();

通过监听 illuminate.query 事件,我可以看到由此生成的查询是:

By listening to the illuminate.query event I can see that the query generated by this is:

select * from `games`
inner join `userGames`
on `userGames`.`game_id` = `games`.`id`
where `userGames`.`user_id` = ?
limit 3 offset 2

Eloquent ORM 的限制

当我尝试使用 Eloquent 重新创建相同的查询时:

Limit in Eloquent ORM

When I try to recreate the same query with Eloquent:

$test = User::find(1)->games->take(2)->toArray();

我可以使用 take 但添加 skip 会导致错误.此外,结果查询实际上并不包含限制:

I'm able to use take but adding skip causes an error. Also the resulting query does not actually contain the limit:

select `games`.*, `userGames`.`user_id` as `pivot_user_id`,
`userGames`.`game_id` as `pivot_game_id` from `games`
inner join `userGames`
on `games`.`id` = `userGames`.`game_id`
where `userGames`.`user_id` = ?

所以好像是先查询整个结果,在处理大数据集的时候不太理想.

So it seems that the entire result is being queried first, which is not ideal when dealing with large data sets.

是否可以限制一个 Eloquent ORM 查询,以便在 MYSQL 查询级别也限制结果,相当于 limit 3 offset 2?

Is it possible to limit an Eloquent ORM query so that at the MYSQL Query level it also limits the result, equivalent to limit 3 offset 2?

推荐答案

User::find(1)->games()->take(3)->skip(2)->get();

我认为这应该给你你的收藏.:)

I think this should give you your collection. :)

->games 会给你一个集合,其中 ->games() 会提供一个查询构建器实例.

->games will give you a collection, where ->games() will offer a query builder instance.

享受 Laravel!

Enjoy Laravel!

相关文章