Laravel雄辩地加入vs [英] Laravel Eloquent join vs with

问题描述

我看到联接是(默认情况下是内部联接)，它返回所有列，但与关键字的时间几乎相同，只需要1000个数据.

I see that join is (by default inner join) and its returning all columns but it takes almost the same time as with keyword for just 1000 data.

$ user-> join('profiles'，'users.id'，'='，'profiles.user_id')-生成以下查询.

$user->join(‘profiles’, ‘users.id’, ‘=’, ‘profiles.user_id’) - generates the below query.

select * from `users` inner join `profiles` on `users`.`id` = `profiles`.`user_id` where `first_name` LIKE '%a%'`

User :: with('profile')-这个急切的加载输出以下查询

User::with(‘profile’) - this eager loading outputs the below query

select * from `users` where exists (select * from `profiles` where `users`.`id` = `profiles`.`user_id` and `first_name` LIKE '%a%')

哪种是返回带有REST API分页的用户列表的最佳方法?渴望加载似乎很有希望，但是它带有子查询.

which is the best way to return a list of users with a pagination for a REST API ? eager loading seems promising but its with a sub query.

如果渴望加载，这就是我将进行过滤的方式.需要使用其中有

if do with eager loading, this is how i will be filtering. need to use whereHas

if($request->filled('first_name')){
        $query->whereHas('profile',function($q) use ($request){
            $q->where('first_name','like','%'.request('first_name').'%');
        });
    }

但如果使用Join，则其代码行更少.

but if used Join, its less lines of code.

  if ($request->filled('first_name')) {
            $users = $users->where('first_name', 'LIKE', "%$request->first_name%");
        }

laravel版本是5.7

laravel version is 5.7

推荐答案

雄辩是Laravel对Active Record模式的实现，它具有所有优点和缺点.当您以CRUD方式处理单个实体时，即从数据库中读取或创建一个新实体，然后保存或删除它，这是一个很好的解决方案.您将从Eloquent的功能中受益匪浅，例如脏检查(仅针对已更改的字段发送SQL UPDATE)，模型事件(例如，在有人创建新帐户时发送管理警报或更新统计信息计数器)，特征(时间戳，软删除，自定义特征)渴望/延迟加载等.

Eloquent is Laravel's implementation of Active Record pattern and it comes with all its strengths and weaknesses. It is a good solution to use when you process a single entity in a CRUD manner - that is, read from database or create a new entity and then save it or delete. You will benefit a lot from Eloquent's features such as dirty checking (to send SQL UPDATE only for the fields which have been changed), model events (e.g. to send administrative alert or update statistics counter when someone has created a new account), traits (timestamps, soft deletes, custom traits) eager/lazy loading etc.

但是，正如您已经知道的那样，它带有一些性能价格.当您处理一个或几个记录时，无需担心.但是对于您读取大量记录的情况(例如，用于数据网格，用于报告，用于批处理等)，纯DB是更好的方法.

But, as you already know, it comes with some performance price. When you process a single or just a few records, there is nothing to worry about. But for cases when you read lots of records (e.g. for datagrids, for reports, for batch processing etc.) the plain DB is better approach.

对于我们的应用程序，我们正是这样做的-在Web表单中使用Laravel的Eloquent处理单个记录，并使用DB(带有SQL视图)检索网格数据，导出数据等.

For our application we are doing exactly that - using Laravel's Eloquent in web forms to process a single record and using DB (with SQL views) to retrieve data for grids, export etc.

在性能和应用程序增长方面，为便于比较，请在以下表格中进行掠夺:

When it comes to performance and the application grows, for the sake of comparison, take a loot at the following tables:

比较雄辩的ORM和原始SQL之间的选择操作平均响应时间

ORM的平均响应时间

Joins | Average (ms) 

1     | 162,2
3     | 1002,7
4     | 1540,0 

口才ORM的选择操作平均响应时间的结果

Result of select operation average response time for Eloquent ORM

原始SQL平均响应时间

Joins | Average (ms)
1     | 116,4 
3     | 130,6 
4     | 155,2

Raw SQL的选择操作平均响应时间的结果

Result of select operation average response time for Raw SQL

这篇关于Laravel雄辩地加入vs的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！