database - Laravel 8 如何连接多对多关系表并使用 DB 创建查询

Question

我正在使用 Laravel 8，我想通过加入具有多对多关系的表来显示事件列表。

这是我的表格的外观：

Users Table

| id | firstname  | status |
|----|------------|--------|
| 1  | Amy        | 0      |
| 2  | 2 amy      | 0      |
| 3  | 3 amy      | 1      |
| 4  | 4 amy      | 0      |
| 5  | 5 amy      | 1      |
| 6  | 6 amy      | 1      |

这是我的数据透视表

events_users Table

| id | event_id   | user_id |
|----|------------|---------|
| 1  | 123        | 1       | 
| 1  | 123        | 2       |
| 1  | 123        | 3       |
| 1  | 123        | 4       |

这是我的活动表

事件表

| id | eventid    | title   |
|----|------------|---------|
| 1  | 123        | title   |  
| 1  | 124        | title 1 | 
| 1  | 125        | title 2 | 
| 1  | 126        | title 3 | 

这是我获取结果的模型：

$events       = DB::table('events') 
                        ->join('events_users', 'events.eventid', '=', 'events_users.event_id')
                        ->join('users', 'users.id', '=', 'events_users.user_id')
                        ->when($sortBy, function ($query, $sortBy) {
                            return $query->orderBy($sortBy);
                        }, function ($query) {
                            return $query->orderBy('events.created_at', 'desc');
                        })
                        ->when($search_query, function ($query, $search_query) {
                            return $query->where('title', 'like', '%'. $search_query . '%');
                        })
                        ->select(
                            'title', 'eventuid', 'description', 'start_date',
                            'end_date', 'start_time', 'end_time', 'status',
                            'venue', 'address_line_1', 'address_line_2', 'address_line_3',
                            'postcode', 'city', 'city_id', 'country', 'image', 
                            'users.firstname', 'users.lastname', 'users.avatar'

                        )
                        ->simplePaginate(15);

这会导致重复条目：

Current Result:

    {
      "current_page": 1,
      "data": [
        {
          "title": "Who in the newspapers, at the mushroom (she had.",
          "eventuid": "be785bac-70d5-379f-a6f8-b35e66c8e494",
          "description": "I'd been the whiting,' said Alice, 'and why it is I hate cats and dogs.' It was opened by another footman in livery came running out of sight before the trial's over!' thought Alice. 'I'm glad they.",
          "start_date": "2000-11-17",
          "end_date": "1988-02-24",
          "start_time": "1972",
          "end_time": "2062",
          "status": 1,
          "venue": "4379",
          "address_line_1": "Kuhn Expressway",
          "address_line_2": "2295 Kerluke Drive Suite 335",
          "address_line_3": "Fredtown",
          "postcode": "57094",
          "city": "New Cassidyburgh",
          "city_id": 530,
          "country": "Cocos (Keeling) Islands",
          "image": "https://via.placeholder.com/1280x720.png/00dd99?text=repellat",
          "firstname": "Marielle", 
          "lastname": "Tremblay", 
          "avatar": "https://via.placeholder.com/640x480.png/002277?text=eum" 
          
        }, 
    {
      "title": "Who in the newspapers, at the mushroom (she had.",
      "eventuid": "be785bac-70d5-379f-a6f8-b35e66c8e494",
      "description": "I'd been the whiting,' said Alice, 'and why it is I hate cats and dogs.' It was opened by another footman in livery came running out of sight before the trial's over!' thought Alice. 'I'm glad they.",
      "start_date": "2000-11-17",
      "end_date": "1988-02-24",
      "start_time": "1972",
      "end_time": "2062",
      "status": 1,
      "venue": "4379",
      "address_line_1": "Kuhn Expressway",
      "address_line_2": "2295 Kerluke Drive Suite 335",
      "address_line_3": "Fredtown",
      "postcode": "57094",
      "city": "New Cassidyburgh",
      "city_id": 530,
      "country": "Cocos (Keeling) Islands",
      "image": "https://via.placeholder.com/1280x720.png/00dd99?text=repellat",
      "firstname": "Floyd",
      "lastname": "Waelchi",
      "avatar": "https://via.placeholder.com/640x480.png/0033cc?text=inventore"
    },
       ...
  ]
}

我要检索的是这样的：

期待：

{
  "current_page": 1,
  "data": [
    {
      "title": "Who in the newspapers, at the mushroom (she had.",
      "eventuid": "be785bac-70d5-379f-a6f8-b35e66c8e494",
      "description": "I'd been the whiting,' said Alice, 'and why it is I hate cats and dogs.' It was opened by another footman in livery came running out of sight before the trial's over!' thought Alice. 'I'm glad they.",
      "start_date": "2000-11-17",
      "end_date": "1988-02-24",
      "start_time": "1972",
      "end_time": "2062",
      "status": 1,
      "venue": "4379",
      "address_line_1": "Kuhn Expressway",
      "address_line_2": "2295 Kerluke Drive Suite 335",
      "address_line_3": "Fredtown",
      "postcode": "57094",
      "city": "New Cassidyburgh",
      "city_id": 530,
      "country": "Cocos (Keeling) Islands",
      "image": "https://via.placeholder.com/1280x720.png/00dd99?text=repellat",
      "users" : {[
          {
           "firstname": "Marielle",
           "lastname": "Tremblay",
           "avatar": "https://via.placeholder.com/640x480.png/002277?text=eum"
          },
          {
           "firstname": "Amy",
           "lastname": "Bond",
           "avatar": "https://via.placeholder.com/640x480.png/005277?text=eum"
          }
     ]} 
    },
   ...
]
}

标签： databaselaraveleloquent