Объедините два запроса вместе sortByDesc и paginate

Я создаю систему отчетов, в которой пользователи могут сообщать о контенте, который нарушает правила модерируемого сайта.

Таблицы

Сообщения

  • идентификатор (число)
  • user_id (число)
  • содержание (текст)
  • заголовок (текст)
  • создано в

Комментарий

  • идентификатор (число)
  • содержание (целое)
  • user_id (число)
  • post_id (число)
  • создано в

Отчет

  • идентификатор (число)
  • user_id (число)
  • содержание (текст)
  • reportable_id (целое)
  • reportable_type (строка)

Пользователь

  • идентификатор (число)
  • имя (строка)

Что я пытаюсь сделать

Я пытаюсь сделать запрос как к сообщениям, так и к комментариям с отчетами, с нетерпением ждать загрузки отчетов, объединить сообщения и комментарии вместе, sortByDesc('created_at') в модели сообщений и комментариев и, наконец, разбить объединенный запрос на страницы.

Попытка ReportController

public function index()
{

    $posts = Post::whereHas('reports')
    ->with('reports.user')
    ->get();

    $comments  = Comment::whereHas('reports')
    ->with('reports.user', 'post')
    ->get();

    //I have no idea how to paginate this. 
    return $postAndCommentReports = $posts->merge($comments)->sortByDesc('created_at');
}

Вывод запроса

{  
   "0":{  
      "id":14,
      "user_id":5,
      "post_id":13,
      "content":"Nobis possimus temporibus iure molestiae. Impedit optio nobis quae. Sunt ut voluptatem libero sunt similique sit.",
      "created_at":"1984-02-20 04:45:37",
      "updated_at":"2018-11-28 20:36:19",
      "reports":[  
         {  
            "id":9,
            "is_handled":0,
            "user_id":10,
            "content":"Rule 1 broken on this comment",
            "reportable_id":14,
            "reportable_type":"App\\Comment",
            "created_at":"2017-08-07 20:43:08",
            "updated_at":"2018-11-28 20:36:19",
            "user":{  
               "id":10,
               "name":"Neva Daugherty",
               "email":"[email protected]",
               "email_verified_at":"2018-11-28 20:36:19",
               "created_at":"2018-11-28 20:36:19",
               "updated_at":"2018-11-28 20:36:19"
            }
         }
      ],
      "post":{  
         "id":13,
         "user_id":10,
         "title":"Impedit voluptas incidunt autem a cum voluptatibus alias.",
         "body":"Vel libero corrupti aperiam harum aut. Rerum accusamus et repellendus ipsa quo porro qui nemo. Velit ratione ut ipsam natus ullam repellat sint iure. Cupiditate quis cupiditate voluptas minus nemo nobis quia.",
         "created_at":"1983-04-02 17:55:56",
         "updated_at":"2018-11-28 20:36:19"
      }
   },
   "1":{  
      "id":3,
      "user_id":20,
      "title":"Dolore dicta similique sapiente consequuntur deserunt voluptas vel.",
      "body":"Provident ut aut minima iure veniam. Minus placeat temporibus sit. Quos aliquid natus rerum.",
      "created_at":"1996-01-14 01:25:34",
      "updated_at":"2018-11-28 20:36:19",
      "reports":[  
         {  
            "id":2,
            "is_handled":0,
            "user_id":1,
            "content":"This is a horrible post",
            "reportable_id":3,
            "reportable_type":"App\\Post",
            "created_at":"2017-08-04 20:43:08",
            "updated_at":"2018-11-28 20:36:19",
            "user":{  
               "id":1,
               "name":"Keegan Schultz",
               "email":"[email protected]",
               "email_verified_at":"2018-11-28 20:36:19",
               "created_at":"2018-11-28 20:36:19",
               "updated_at":"2018-11-28 20:36:19"
            }
         }
      ]
   },
   "2":{  
      "id":10,
      "user_id":13,
      "title":"Eaque amet sed quia quo.",
      "body":"Rem ut illum asperiores nostrum. Aut ipsam ut qui quisquam quis iure. Itaque incidunt earum velit est est hic reprehenderit pariatur. Rerum ipsum fuga incidunt omnis repellat.",
      "created_at":"1971-07-08 05:14:57",
      "updated_at":"2018-11-28 20:36:19",
      "reports":[  
         {  
            "id":5,
            "is_handled":0,
            "user_id":15,
            "content":"Rule 2 broken on this post",
            "reportable_id":10,
            "reportable_type":"App\\Post",
            "created_at":"2017-08-10 20:43:08",
            "updated_at":"2018-11-28 20:36:19",
            "user":{  
               "id":15,
               "name":"Fritz Champlin",
               "email":"[email protected]",
               "email_verified_at":"2018-11-28 20:36:19",
               "created_at":"2018-11-28 20:36:19",
               "updated_at":"2018-11-28 20:36:19"
            }
         }
      ]
   },
   "3":{  
      "id":31,
      "user_id":1,
      "post_id":17,
      "content":"Dolores voluptatem eveniet animi tempora et accusantium. Aut dolores molestias reiciendis in accusamus ex. Ab hic quia non corrupti fuga.",
      "created_at":"2001-05-19 22:21:47",
      "updated_at":"2018-11-28 20:36:19",
      "reports":[  
         {  
            "id":7,
            "is_handled":0,
            "user_id":17,
            "content":"This is a horrible comment",
            "reportable_id":31,
            "reportable_type":"App\\Comment",
            "created_at":"2017-08-03 20:43:08",
            "updated_at":"2018-11-28 20:36:19",
            "user":{  
               "id":17,
               "name":"Lacey Walter",
               "email":"[email protected]",
               "email_verified_at":"2018-11-28 20:36:19",
               "created_at":"2018-11-28 20:36:19",
               "updated_at":"2018-11-28 20:36:19"
            }
         }
      ],
      "post":{  
         "id":17,
         "user_id":9,
         "title":"Quia sit aut incidunt nulla minima odit nesciunt.",
         "body":"Qui aliquam odio aut eum quaerat deserunt rerum. Molestiae veritatis eaque aut excepturi et. Enim perferendis eaque asperiores laudantium occaecati. Amet fugit cumque ut et reprehenderit maiores deleniti.",
         "created_at":"2007-06-14 12:01:36",
         "updated_at":"2018-11-28 20:36:19"
      }
   },
   "4":{  
      "id":34,
      "user_id":17,
      "post_id":12,
      "content":"Deleniti dolorem esse exercitationem illum quidem. Quia inventore molestias tenetur officiis. Distinctio facilis corporis omnis molestiae. Ut corporis culpa error atque consequatur.",
      "created_at":"1994-04-11 09:26:17",
      "updated_at":"2018-11-28 20:36:19",
      "reports":[  
         {  
            "id":10,
            "is_handled":0,
            "user_id":6,
            "content":"Rule 2 broken on this comment",
            "reportable_id":34,
            "reportable_type":"App\\Comment",
            "created_at":"2017-08-09 20:43:08",
            "updated_at":"2018-11-28 20:36:19",
            "user":{  
               "id":6,
               "name":"Bernita Hahn",
               "email":"[email protected]",
               "email_verified_at":"2018-11-28 20:36:19",
               "created_at":"2018-11-28 20:36:19",
               "updated_at":"2018-11-28 20:36:19"
            }
         }
      ],
      "post":{  
         "id":12,
         "user_id":13,
         "title":"Placeat ad nihil veritatis.",
         "body":"Dolor nam tempore suscipit quia ut. Ab exercitationem hic ut in ut temporibus ab. Et voluptatum dolores expedita enim ullam quia. Qui optio quia exercitationem quo molestias a minus.",
         "created_at":"2010-10-02 08:36:20",
         "updated_at":"2018-11-28 20:36:19"
      }
   },
   "5":{  
      "id":49,
      "user_id":20,
      "post_id":8,
      "content":"Quis commodi a aut aut. Assumenda sit officiis placeat ipsam sit veritatis vel qui. Corporis soluta distinctio quibusdam dicta totam.",
      "created_at":"1984-11-22 17:20:39",
      "updated_at":"2018-11-28 20:36:19",
      "reports":[  
         {  
            "id":6,
            "is_handled":0,
            "user_id":14,
            "content":"This comment is against the rule",
            "reportable_id":49,
            "reportable_type":"App\\Comment",
            "created_at":"2017-08-01 20:43:08",
            "updated_at":"2018-11-28 20:36:19",
            "user":{  
               "id":14,
               "name":"Dr. Seth Treutel",
               "email":"[email protected]",
               "email_verified_at":"2018-11-28 20:36:19",
               "created_at":"2018-11-28 20:36:19",
               "updated_at":"2018-11-28 20:36:19"
            }
         }
      ],
      "post":{  
         "id":8,
         "user_id":6,
         "title":"Quia perspiciatis aperiam quos quasi.",
         "body":"Dolore corrupti voluptatibus voluptate perferendis totam eos ut. Suscipit ea voluptas consectetur aut sed natus quia quam. Ut omnis dolore aliquid.",
         "created_at":"2013-08-12 14:19:59",
         "updated_at":"2018-11-28 20:36:19"
      }
   },
   "6":{  
      "id":42,
      "user_id":7,
      "post_id":7,
      "content":"Laborum occaecati voluptatem sed quisquam. Culpa labore voluptatibus ut dolorem. Rerum est labore dolorum unde.",
      "created_at":"1977-05-16 18:17:46",
      "updated_at":"2018-11-28 20:36:19",
      "reports":[  
         {  
            "id":8,
            "is_handled":0,
            "user_id":19,
            "content":"Not a great comment for the site",
            "reportable_id":42,
            "reportable_type":"App\\Comment",
            "created_at":"2017-08-05 20:43:08",
            "updated_at":"2018-11-28 20:36:19",
            "user":{  
               "id":19,
               "name":"Fritz Beer",
               "email":"[email protected]",
               "email_verified_at":"2018-11-28 20:36:19",
               "created_at":"2018-11-28 20:36:19",
               "updated_at":"2018-11-28 20:36:19"
            }
         }
      ],
      "post":{  
         "id":7,
         "user_id":11,
         "title":"Ducimus et commodi blanditiis nam sed eius.",
         "body":"Optio sunt quaerat inventore voluptatem dolorum ullam. Eius optio praesentium quia eos necessitatibus commodi. Eius ut qui dolore repellendus itaque aut corrupti nobis. Libero eligendi omnis enim eveniet incidunt amet consequatur.",
         "created_at":"2005-07-28 04:59:56",
         "updated_at":"2018-11-28 20:36:19"
      }
   }
}

Как видно из вывода, sortByDesc('created_at); не работает, и я совершенно не знаю, как это разбить на страницы.

Попытка 2

Я также попытался запросить все отчеты и сгруппировать отчеты вместе.

$reports = Report::with('reportable','user')->latest()->paginate();
return $reports->groupBy('reportable_id');

Выход:

{  
   "3":[  
      {  
         "id":2,
         "is_handled":0,
         "user_id":1,
         "content":"This is a horrible post",
         "reportable_id":3,
         "reportable_type":"App\\Post",
         "created_at":"2017-08-04 20:43:08",
         "updated_at":"2018-11-28 20:36:19",
         "reportable":{  
            "id":3,
            "user_id":20,
            "title":"Dolore dicta similique sapiente consequuntur deserunt voluptas vel.",
            "body":"Provident ut aut minima iure veniam. Minus placeat temporibus sit. Quos aliquid natus rerum.",
            "created_at":"1996-01-14 01:25:34",
            "updated_at":"2018-11-28 20:36:19"
         },
         "user":{  
            "id":1,
            "name":"Keegan Schultz",
            "email":"[email protected]",
            "email_verified_at":"2018-11-28 20:36:19",
            "created_at":"2018-11-28 20:36:19",
            "updated_at":"2018-11-28 20:36:19"
         }
      }
   ],
   "10":[  
      {  
         "id":5,
         "is_handled":0,
         "user_id":15,
         "content":"Rule 2 broken on this post",
         "reportable_id":10,
         "reportable_type":"App\\Post",
         "created_at":"2017-08-10 20:43:08",
         "updated_at":"2018-11-28 20:36:19",
         "reportable":{  
            "id":10,
            "user_id":13,
            "title":"Eaque amet sed quia quo.",
            "body":"Rem ut illum asperiores nostrum. Aut ipsam ut qui quisquam quis iure. Itaque incidunt earum velit est est hic reprehenderit pariatur. Rerum ipsum fuga incidunt omnis repellat.",
            "created_at":"1971-07-08 05:14:57",
            "updated_at":"2018-11-28 20:36:19"
         },
         "user":{  
            "id":15,
            "name":"Fritz Champlin",
            "email":"[email protected]",
            "email_verified_at":"2018-11-28 20:36:19",
            "created_at":"2018-11-28 20:36:19",
            "updated_at":"2018-11-28 20:36:19"
         }
      }
   ],
   "14":[  
      {  
         "id":4,
         "is_handled":0,
         "user_id":13,
         "content":"Rule 1 broken on this post",
         "reportable_id":14,
         "reportable_type":"App\\Post",
         "created_at":"2017-08-08 20:43:08",
         "updated_at":"2018-11-28 20:36:19",
         "reportable":{  
            "id":14,
            "user_id":16,
            "title":"Labore alias quaerat placeat et.",
            "body":"Voluptatem sit occaecati excepturi quod est voluptatem. Earum provident ut aut.",
            "created_at":"2018-05-25 02:38:31",
            "updated_at":"2018-11-28 20:36:19"
         },
         "user":{  
            "id":13,
            "name":"Blair Conn",
            "email":"[email protected]",
            "email_verified_at":"2018-11-28 20:36:19",
            "created_at":"2018-11-28 20:36:19",
            "updated_at":"2018-11-28 20:36:19"
         }
      },
      {  
         "id":9,
         "is_handled":0,
         "user_id":10,
         "content":"Rule 1 broken on this comment",
         "reportable_id":14,
         "reportable_type":"App\\Comment",
         "created_at":"2017-08-07 20:43:08",
         "updated_at":"2018-11-28 20:36:19",
         "reportable":{  
            "id":14,
            "user_id":5,
            "post_id":13,
            "content":"Nobis possimus temporibus iure molestiae. Impedit optio nobis quae. Sunt ut voluptatem libero sunt similique sit.",
            "created_at":"1984-02-20 04:45:37",
            "updated_at":"2018-11-28 20:36:19"
         },
         "user":{  
            "id":10,
            "name":"Neva Daugherty",
            "email":"[email protected]",
            "email_verified_at":"2018-11-28 20:36:19",
            "created_at":"2018-11-28 20:36:19",
            "updated_at":"2018-11-28 20:36:19"
         }
      },
      {  
         "id":3,
         "is_handled":0,
         "user_id":7,
         "content":"Not a great post for the site",
         "reportable_id":14,
         "reportable_type":"App\\Post",
         "created_at":"2017-08-06 20:43:08",
         "updated_at":"2018-11-28 20:36:19",
         "reportable":{  
            "id":14,
            "user_id":16,
            "title":"Labore alias quaerat placeat et.",
            "body":"Voluptatem sit occaecati excepturi quod est voluptatem. Earum provident ut aut.",
            "created_at":"2018-05-25 02:38:31",
            "updated_at":"2018-11-28 20:36:19"
         },
         "user":{  
            "id":7,
            "name":"Francesco Kuhic",
            "email":"[email protected]",
            "email_verified_at":"2018-11-28 20:36:19",
            "created_at":"2018-11-28 20:36:19",
            "updated_at":"2018-11-28 20:36:19"
         }
      },
      {  
         "id":1,
         "is_handled":0,
         "user_id":8,
         "content":"This posts is against the rules",
         "reportable_id":14,
         "reportable_type":"App\\Post",
         "created_at":"2017-08-02 20:43:08",
         "updated_at":"2018-11-28 20:36:19",
         "reportable":{  
            "id":14,
            "user_id":16,
            "title":"Labore alias quaerat placeat et.",
            "body":"Voluptatem sit occaecati excepturi quod est voluptatem. Earum provident ut aut.",
            "created_at":"2018-05-25 02:38:31",
            "updated_at":"2018-11-28 20:36:19"
         },
         "user":{  
            "id":8,
            "name":"Dr. Juston Hansen MD",
            "email":"[email protected]",
            "email_verified_at":"2018-11-28 20:36:19",
            "created_at":"2018-11-28 20:36:19",
            "updated_at":"2018-11-28 20:36:19"
         }
      }
   ],
   "31":[  
      {  
         "id":7,
         "is_handled":0,
         "user_id":17,
         "content":"This is a horrible comment",
         "reportable_id":31,
         "reportable_type":"App\\Comment",
         "created_at":"2017-08-03 20:43:08",
         "updated_at":"2018-11-28 20:36:19",
         "reportable":{  
            "id":31,
            "user_id":1,
            "post_id":17,
            "content":"Dolores voluptatem eveniet animi tempora et accusantium. Aut dolores molestias reiciendis in accusamus ex. Ab hic quia non corrupti fuga.",
            "created_at":"2001-05-19 22:21:47",
            "updated_at":"2018-11-28 20:36:19"
         },
         "user":{  
            "id":17,
            "name":"Lacey Walter",
            "email":"[email protected]",
            "email_verified_at":"2018-11-28 20:36:19",
            "created_at":"2018-11-28 20:36:19",
            "updated_at":"2018-11-28 20:36:19"
         }
      }
   ],
   "34":[  
      {  
         "id":10,
         "is_handled":0,
         "user_id":6,
         "content":"Rule 2 broken on this comment",
         "reportable_id":34,
         "reportable_type":"App\\Comment",
         "created_at":"2017-08-09 20:43:08",
         "updated_at":"2018-11-28 20:36:19",
         "reportable":{  
            "id":34,
            "user_id":17,
            "post_id":12,
            "content":"Deleniti dolorem esse exercitationem illum quidem. Quia inventore molestias tenetur officiis. Distinctio facilis corporis omnis molestiae. Ut corporis culpa error atque consequatur.",
            "created_at":"1994-04-11 09:26:17",
            "updated_at":"2018-11-28 20:36:19"
         },
         "user":{  
            "id":6,
            "name":"Bernita Hahn",
            "email":"[email protected]",
            "email_verified_at":"2018-11-28 20:36:19",
            "created_at":"2018-11-28 20:36:19",
            "updated_at":"2018-11-28 20:36:19"
         }
      }
   ],
   "42":[  
      {  
         "id":8,
         "is_handled":0,
         "user_id":19,
         "content":"Not a great comment for the site",
         "reportable_id":42,
         "reportable_type":"App\\Comment",
         "created_at":"2017-08-05 20:43:08",
         "updated_at":"2018-11-28 20:36:19",
         "reportable":{  
            "id":42,
            "user_id":7,
            "post_id":7,
            "content":"Laborum occaecati voluptatem sed quisquam. Culpa labore voluptatibus ut dolorem. Rerum est labore dolorum unde.",
            "created_at":"1977-05-16 18:17:46",
            "updated_at":"2018-11-28 20:36:19"
         },
         "user":{  
            "id":19,
            "name":"Fritz Beer",
            "email":"[email protected]",
            "email_verified_at":"2018-11-28 20:36:19",
            "created_at":"2018-11-28 20:36:19",
            "updated_at":"2018-11-28 20:36:19"
         }
      }
   ],
   "49":[  
      {  
         "id":6,
         "is_handled":0,
         "user_id":14,
         "content":"This comment is against the rule",
         "reportable_id":49,
         "reportable_type":"App\\Comment",
         "created_at":"2017-08-01 20:43:08",
         "updated_at":"2018-11-28 20:36:19",
         "reportable":{  
            "id":49,
            "user_id":20,
            "post_id":8,
            "content":"Quis commodi a aut aut. Assumenda sit officiis placeat ipsam sit veritatis vel qui. Corporis soluta distinctio quibusdam dicta totam.",
            "created_at":"1984-11-22 17:20:39",
            "updated_at":"2018-11-28 20:36:19"
         },
         "user":{  
            "id":14,
            "name":"Dr. Seth Treutel",
            "email":"[email protected]",
            "email_verified_at":"2018-11-28 20:36:19",
            "created_at":"2018-11-28 20:36:19",
            "updated_at":"2018-11-28 20:36:19"
         }
      }
   ]
}

Вот изображение, которое показывает результат, который я пытаюсь получить:

введите описание изображения здесь

Есть ли способ сделать это относительно эффективным? Должен ли я делать необработанный запрос в БД и использовать Union и присоединяться, как этот другой поток ? и как разбить вывод на страницы? Есть ли способ структурировать или разбить таблицу отчетов, чтобы лучше с этим справиться?


laravel eloquent
person user3325126    schedule 28.11.2018    source источник

Ответы (1)


arrow_upward
1
arrow_downward

Попробуйте использовать Ограничение нетерпеливой нагрузки, предполагая, что все ваши отношения настроены правильно и с обратным взглядом вверх.

  • Опубликовать отчет hasMany
  • Комментарий HasMany Report
  • Отчет принадлежитК посту
  • Отчет принадлежитКомментарий

https://laravel.com/docs/5.7/eloquent-relationships

См. Раздел Определение обратной связи

$reports = Report::with([
    'posts' => function($query) {
        $query->latest();
    },
    'comments' => function($query) {
        $query->latest();
    }
])
->latest()
->paginate(15);

return $posts;
person Marc    schedule 28.11.2018
comment
Спасибо за попытку, но отчеты о комментариях выводятся только в том случае, если у них есть сообщение, о котором было сообщено. Вывод запроса - person user3325126; 29.11.2018
comment
Однако, поскольку модель отчета имеет отношение morphTo с моделью публикаций и комментариев reportable(), вы можете аналогичным образом использовать Ограничение нетерпеливой загрузки. Я сделаю еще одну попытку в основном посте. - person user3325126; 29.11.2018
comment
Ох, понятно ... Вы хотите выполнить поиск обратной связи, я обновляю свое предложение. Надеюсь, это направит вас в правильное русло, прошло больше года с тех пор, как я делал такие модели, я не могу вспомнить все детали. - person Marc; 29.11.2018