Laravel Eloquent多列IN条款

时间：2022-10-25 08:00:49

I have recently started to develop using Laravel, and my first project is a vehicle booking application.

我最近开始使用Laravel进行开发,我的第一个项目是车辆预订应用程序。

I seem to be doing fine, however I am stuck trying to show current bookings using Eloquent.

我似乎做得很好,但我仍然试图用Eloquent显示当前的预订。

The system works by adding vehicles to the vehicles table, and employees to the employees table. A many-to-many relationship with a pivot table is created using employee_vehicle.

该系统的工作原理是将车辆添加到车辆表中,将员工添加到员工表中。使用employee_vehicle创建与数据透视表的多对多关系。

employees

+------------+-------------+------+-----+---------+----------------+
| Field      | Type        | Null | Key | Default | Extra          |
+------------+-------------+------+-----+---------+----------------+
| id         | int(10)     | NO   | PRI | NULL    | auto_increment |
| first_name | varchar(50) | NO   |     | NULL    |                |
| surname    | varchar(50) | NO   |     | NULL    |                |
+------------+-------------+------+-----+---------+----------------+

vehicles

+--------------+-------------+------+-----+---------+----------------+
| Field        | Type        | Null | Key | Default | Extra          |
+--------------+-------------+------+-----+---------+----------------+
| id           | int(10)     | NO   | PRI | NULL    | auto_increment |
| registration | varchar(10) | NO   |     | NULL    |                |
| make         | varchar(50) | NO   |     | NULL    |                |
| model        | varchar(50) | NO   |     | NULL    |                |
+--------------+-------------+------+-----+---------+----------------+

employee_vehicle

+-------------+------------+------+-----+---------+----------------+
| Field       | Type       | Null | Key | Default | Extra          |
+-------------+------------+------+-----+---------+----------------+
| id          | int(10)    | NO   | PRI | NULL    | auto_increment |
| employee_id | int(10)    | NO   |     | NULL    |                |
| vehicle_id  | int(10)    | NO   |     | NULL    |                |
| direction   | varchar(3) | NO   |     | NULL    |                |
| date_booked | datetime   | NO   |     | NULL    |                |
| damaged     | tinyint(1) | NO   |     | NULL    |                |
| mileage     | int(10)    | NO   |     | NULL    |                |
+-------------+------------+------+-----+---------+----------------+

Result

+----+-------------+------------+-----------+---------------------+---------+---------+
| id | employee_id | vehicle_id | direction | date_booked         | damaged | mileage |
+----+-------------+------------+-----------+---------------------+---------+---------+
|  1 |           1 |          1 | OUT       | 2016-06-13 09:08:08 |       0 |    2357 |
|  2 |           1 |          1 | IN        | 2016-06-16 16:29:26 |       0 |    2401 |
|  3 |           2 |          1 | OUT       | 2016-06-20 10:47:28 |       0 |    2470 |
|  4 |           3 |          2 | OUT       | 2016-06-14 09:18:52 |       0 |    1403 |
+----+-------------+------------+-----------+---------------------+---------+---------+

These tables have Vehicle and Employee models that extend Eloquent. These have methods which call belongsToMany.

这些表具有扩展Eloquent的Vehicle和Employee模型。这些方法调用belongsToMany。

What I want to do is get the Vehicle collection and eager load the employees onto it. That way I can show currently booked vehicles, along with who currently has booked them out.

我想要做的是获取车辆收集并急切地将员工加载到它上面。通过这种方式,我可以显示当前已预订的车辆,以及目前已将其预订的车辆。

For example:

$vehicleBookings = Vehicle::with(['employees'])->get();

$ vehicleBookings = Vehicle :: with(['employees']) - > get();

I shall explain the way that a booking works...

我将解释预订的工作方式......

Basically, a vehicle and an employee are linked together using the pivot table. The table also records the date booked, the direction of the booking (e.g. Booked IN, or OUT), and additional data about its condition etc.

基本上,使用枢轴表将车辆和雇员链接在一起。该表还记录了预订日期,预订方向(例如预订IN或OUT)以及有关其状况的其他数据等。

It is important that there is an entry for both IN and OUT so that there is an audit log of the vehicle condition before and after bookings.

重要的是,IN和OUT都有一个条目,以便在预订之前和之后有车辆状况的审核日志。

The problem I had was to be able to get the latest state of a vehicle from its available bookings, as to determine whether it was currently booked out, or available to book. This determines what can be done with a vehicle.

我遇到的问题是能够从其可用的预订中获取最新的车辆状态,以确定它当前是否已预订或可预订。这决定了车辆可以做什么。

After some research I came up with the following query:

经过一番研究后,我想出了以下问题:

SELECT * 
FROM `employee_vehicle` 
WHERE (`vehicle_id`, `date_booked`) IN (
   SELECT `vehicle_id`, MAX(`date_booked`)
    FROM `employee_vehicle`
    GROUP BY `vehicle_id`
)

(I didn't even know it was possible to use multiple columns with an IN clause until now, but it's great!).

(我甚至不知道到现在为止可以使用带有IN子句的多个列,但这很棒!)。

This gives me:

这给了我:

+----+-------------+------------+-----------+---------------------+---------+---------+
| id | employee_id | vehicle_id | direction | date_booked         | damaged | mileage |
+----+-------------+------------+-----------+---------------------+---------+---------+
|  3 |           2 |          1 | OUT       | 2016-06-20 10:47:28 |       0 |    2470 |
|  4 |           3 |          2 | OUT       | 2016-06-14 09:18:52 |       0 |    1403 |
+----+-------------+------------+-----------+---------------------+---------+---------+

This gives me one row per vehicle that has ever been booked (this is before joining any employees to it).

这给了我每辆车预订一排(这是在加入任何员工之前)。

However now I am really stuck. I just cannot figure out how to write an equivalent query using Eloquent that also joins on the Employees. (It will also need to show only vehicles currently "OUT", but I guess that's the trivial part!)

但是现在我真的被卡住了。我只是无法弄清楚如何使用也加入Employees的Eloquent编写等效查询。 (它还需要只显示当前“OUT”的车辆,但我想这是微不足道的部分!)

I could just write the query using DB::raw('...'), and don't mind manually writing long SQL queries, however I really want to give it a go properly, trying to use Eloquent.

我可以使用DB :: raw('...')编写查询,并且不介意手动编写长SQL查询,但我真的想要正确使用它,尝试使用Eloquent。

I have noticed the whereIn method, but it only accepts a string for one column name rather than an array of multiple columns, so this is a no-go. (Unless I am missing something, or it is planned for a future version of Laravel?)

我注意到了whereIn方法,但它只接受一个列名称的字符串而不是多列的数组,所以这是不行的。 (除非我遗漏了什么,或者计划将来的Laravel版本?)

I was never really a fan of frameworks, and have needed some convincing, and not wishing to be defeated, have stuck with Laravel. This is frustrating me as I feel that it would be quicker just to not mess around with Eloquent. If I can get this to work then I will be more accepting of it.

我从来不是真正的框架粉丝,需要一些令人信服的,而不是希望被击败,与Laravel一起。这让我感到很沮丧,因为我觉得不要乱用Eloquent会更快。如果我可以让它工作,那么我会更加接受它。

1 个解决方案

#1

0  

Let's say You've

让我们说你

Employee model:

class Employee extends Model {
  protected $table = 'employees';
  public $timestamps = false;
}

and Vehicle model:

和车型:

class Vehicle extends Model {
  protected $table = 'employees';
  public $timestamps = false;
}

and You want to get latest logs grouped by vehicle_id.

并且您想获得按vehicle_id分组的最新日志。

So make a model:

所以制作一个模型:

class EmployeeVehicle extends Model {
  protected $table = 'employee_vehicle';
  public $timestamps = false;  

  public function employee() {
    return $this->belongsTo('App\Employee');
  }

  public function vehicle() {
    return $this->belongsTo('App\Vehicle');
  }
}

and You request to DB will be like:

你对DB的要求就像:

$EmployeeVehicles = EmployeeVehicle::with(['employee', 'vehicle'])
                                  ->where('date_booked', '>', date('Y-m-d H:i:s', (time() - 3*30*24*60*60)))
                                  ->groupBy('vehicle_id')
                                  ->selectRaw('*, MAX(`date_booked`) AS `last_date_booked`')
                                  ->paginate(50);

p.s. I've added where('date_booked', '>', date('Y-m-d H:i:s', (time() - 3*30*24*60*60)) because Your query to log database is very heavy job for database engine, and normally it's good to have data from past 3 month for performance safety.

附:我添加了where('date_booked','>',date('Ymd H:i:s',(time() - 3 * 30 * 24 * 60 * 60))因为你对日志数据库的查询很重数据库引擎的工作,通常最好有过去3个月的数据以确保性能安全。

And try to paginate, not to get everything.

并尝试分页,而不是得到一切。

#1

0  

Let's say You've

让我们说你

Employee model:

class Employee extends Model {
  protected $table = 'employees';
  public $timestamps = false;
}

and Vehicle model:

和车型:

class Vehicle extends Model {
  protected $table = 'employees';
  public $timestamps = false;
}

and You want to get latest logs grouped by vehicle_id.

并且您想获得按vehicle_id分组的最新日志。

So make a model:

所以制作一个模型:

class EmployeeVehicle extends Model {
  protected $table = 'employee_vehicle';
  public $timestamps = false;  

  public function employee() {
    return $this->belongsTo('App\Employee');
  }

  public function vehicle() {
    return $this->belongsTo('App\Vehicle');
  }
}

and You request to DB will be like:

你对DB的要求就像:

$EmployeeVehicles = EmployeeVehicle::with(['employee', 'vehicle'])
                                  ->where('date_booked', '>', date('Y-m-d H:i:s', (time() - 3*30*24*60*60)))
                                  ->groupBy('vehicle_id')
                                  ->selectRaw('*, MAX(`date_booked`) AS `last_date_booked`')
                                  ->paginate(50);

p.s. I've added where('date_booked', '>', date('Y-m-d H:i:s', (time() - 3*30*24*60*60)) because Your query to log database is very heavy job for database engine, and normally it's good to have data from past 3 month for performance safety.

附:我添加了where('date_booked','>',date('Ymd H:i:s',(time() - 3 * 30 * 24 * 60 * 60))因为你对日志数据库的查询很重数据库引擎的工作,通常最好有过去3个月的数据以确保性能安全。

And try to paginate, not to get everything.

并尝试分页,而不是得到一切。

标签：

相关文章