Laravel Eloquent 加入表和计数相关 [英] Laravel Eloquent to join table and count related

问题描述

如何在 Eloquent 中使用 join 并考虑以下表结构:

How do I use join with Eloquent taking in consideration the following table structure:

我有一个属性表

---------------------
ID    | Name 
---------------------
1     | Property Name

比我有房间

----------------------
RoomID | Property
----------------------
A-212  | 1
---------------------- 
F-1231 | 1

这里的属性是外键比我想获取所有属性并计算每个属性有多少个房间

here Property is the foreign key than I want to get all Properties and count how many rooms do they have each

检索所有的查询看起来像

The query which retrives all looks like

   class PropertiesRepository extends EloquentBaseRepository implements PropertiesInterface
{

    use TaggableRepository;

    /**
     * Construct 
     * @param Properties $properties 
     */
    public function __construct( Properties $properties )
    {
        $this->model = $properties;
    }
     /**
     * Get all properties joining rooms
     * @return Properties
     */
    public function getAll()
    {
        return $this->model->get();
    }
}

如何扩展此查询以获得所需的结果?

How do I extend this query to get the desired result?

推荐答案

这更像是一个 MySQL join+group+select 技巧，包括以下步骤.

This is more of a MySQL join+group+select trick which includes following steps.

1. 加入你的关系表(如果你想排除 RoomsCount=0 的行，使用 join，否则使用 leftJoin)
2. 使用 groupBy by primaryKey 避免重复连接.
3. 选择 count 个连接表

1. Join your relation table(use join if you want to exclude rows with RoomsCount=0, else use leftJoin)
2. Use groupBy by primaryKey to avoid duplicates of the join.
3. Select count of joined table

    $this->model->leftJoin('Rooms', 'Properties.ID', '=', 'Rooms.Property')
      ->selectRaw('Properties.*, count(Rooms.RoomID) as RoomsCount')
      ->groupBy('Properties.ID')
      ->get();

这篇关于Laravel Eloquent 加入表和计数相关的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！