Merge two queries together sortByDesc and paginate
0
I'm building a reports system where users can report content that violates the rules of the site to be moderated.
Tables
Posts
- id (int)
- user_id (int)
- content ( text)
- title (text)
- created_at
Comment
- id (int)
- content (int)
- user_id (int)
- post_id (int)
- created_at
Report
- id (int)
- user_id (int)
- content (text)
- reportable_id (int)
- reportable_type (string)
User
- id (int)
- name (string)
What I'm trying to do
What I'm trying to do is query both the Posts and Comments that have reports, eager load the reports, merge Posts and Comments together, sortByDesc('created_at') on the Post and Comments model and finally paginate the merged query.
ReportController Attempt
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');
}
Query Output
{
"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":"hadley17@example.com",
"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":"breanne09@example.com",
"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":"beth.dach@example.org",
"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":"emard.rene@example.org",
"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":"tiffany99@example.org",
"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":"rigoberto.vonrueden@example.org",
"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":"gleason.kris@example.org",
"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"
}
}
}
As you can see from the output sortByDesc('created_at); is not working and I have absolutely no idea how to paginate this.
Attempt 2
I've also tried to query all reports and group the reports together.
$reports = Report::with('reportable','user')->latest()->paginate();
return $reports->groupBy('reportable_id');
Output:
{
"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":"breanne09@example.com",
"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":"beth.dach@example.org",
"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":"hollis.ferry@example.com",
"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":"hadley17@example.com",
"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":"amya.daniel@example.com",
"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":"priscilla.hamill@example.net",
"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":"emard.rene@example.org",
"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":"tiffany99@example.org",
"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":"gleason.kris@example.org",
"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":"rigoberto.vonrueden@example.org",
"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"
}
}
]
}
Here is an image that shows the output I am trying to obtain:
Is there a way to do this that's relatively efficient? Do I have to do a raw query on the DB and use Union and join like this other thread? and some how paginate the output? Is there a way I can structure or break up my reports table to handle this better?
laravel eloquent
asked Nov 28 '18 at 21:20
user3325126user3325126
355216
add a comment |
0
I'm building a reports system where users can report content that violates the rules of the site to be moderated.
Tables
Posts
- id (int)
- user_id (int)
- content ( text)
- title (text)
- created_at
Comment
- id (int)
- content (int)
- user_id (int)
- post_id (int)
- created_at
Report
- id (int)
- user_id (int)
- content (text)
- reportable_id (int)
- reportable_type (string)
User
- id (int)
- name (string)
What I'm trying to do
What I'm trying to do is query both the Posts and Comments that have reports, eager load the reports, merge Posts and Comments together, sortByDesc('created_at') on the Post and Comments model and finally paginate the merged query.
ReportController Attempt
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');
}
Query Output
{
"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":"hadley17@example.com",
"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":"breanne09@example.com",
"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":"beth.dach@example.org",
"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":"emard.rene@example.org",
"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":"tiffany99@example.org",
"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":"rigoberto.vonrueden@example.org",
"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":"gleason.kris@example.org",
"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"
}
}
}
As you can see from the output sortByDesc('created_at); is not working and I have absolutely no idea how to paginate this.
Attempt 2
I've also tried to query all reports and group the reports together.
$reports = Report::with('reportable','user')->latest()->paginate();
return $reports->groupBy('reportable_id');
Output:
{
"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":"breanne09@example.com",
"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":"beth.dach@example.org",
"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":"hollis.ferry@example.com",
"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":"hadley17@example.com",
"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":"amya.daniel@example.com",
"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":"priscilla.hamill@example.net",
"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":"emard.rene@example.org",
"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":"tiffany99@example.org",
"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":"gleason.kris@example.org",
"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":"rigoberto.vonrueden@example.org",
"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"
}
}
]
}
Here is an image that shows the output I am trying to obtain:
Is there a way to do this that's relatively efficient? Do I have to do a raw query on the DB and use Union and join like this other thread? and some how paginate the output? Is there a way I can structure or break up my reports table to handle this better?
laravel eloquent
asked Nov 28 '18 at 21:20
user3325126user3325126
355216
add a comment |
0
0
0
I'm building a reports system where users can report content that violates the rules of the site to be moderated.
Tables
Posts
- id (int)
- user_id (int)
- content ( text)
- title (text)
- created_at
Comment
- id (int)
- content (int)
- user_id (int)
- post_id (int)
- created_at
Report
- id (int)
- user_id (int)
- content (text)
- reportable_id (int)
- reportable_type (string)
User
- id (int)
- name (string)
What I'm trying to do
What I'm trying to do is query both the Posts and Comments that have reports, eager load the reports, merge Posts and Comments together, sortByDesc('created_at') on the Post and Comments model and finally paginate the merged query.
ReportController Attempt
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');
}
Query Output
{
"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":"hadley17@example.com",
"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":"breanne09@example.com",
"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":"beth.dach@example.org",
"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":"emard.rene@example.org",
"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":"tiffany99@example.org",
"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":"rigoberto.vonrueden@example.org",
"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":"gleason.kris@example.org",
"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"
}
}
}
As you can see from the output sortByDesc('created_at); is not working and I have absolutely no idea how to paginate this.
Attempt 2
I've also tried to query all reports and group the reports together.
$reports = Report::with('reportable','user')->latest()->paginate();
return $reports->groupBy('reportable_id');
Output:
{
"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":"breanne09@example.com",
"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":"beth.dach@example.org",
"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":"hollis.ferry@example.com",
"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":"hadley17@example.com",
"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":"amya.daniel@example.com",
"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":"priscilla.hamill@example.net",
"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":"emard.rene@example.org",
"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":"tiffany99@example.org",
"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":"gleason.kris@example.org",
"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":"rigoberto.vonrueden@example.org",
"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"
}
}
]
}
Here is an image that shows the output I am trying to obtain:
Is there a way to do this that's relatively efficient? Do I have to do a raw query on the DB and use Union and join like this other thread? and some how paginate the output? Is there a way I can structure or break up my reports table to handle this better?
laravel eloquent
asked Nov 28 '18 at 21:20
user3325126user3325126
355216
I'm building a reports system where users can report content that violates the rules of the site to be moderated.
Tables
Posts
- id (int)
- user_id (int)
- content ( text)
- title (text)
- created_at
Comment
- id (int)
- content (int)
- user_id (int)
- post_id (int)
- created_at
Report
- id (int)
- user_id (int)
- content (text)
- reportable_id (int)
- reportable_type (string)
User
- id (int)
- name (string)
What I'm trying to do
What I'm trying to do is query both the Posts and Comments that have reports, eager load the reports, merge Posts and Comments together, sortByDesc('created_at') on the Post and Comments model and finally paginate the merged query.
ReportController Attempt
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');
}
Query Output
{
"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":"hadley17@example.com",
"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":"breanne09@example.com",
"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":"beth.dach@example.org",
"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":"emard.rene@example.org",
"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":"tiffany99@example.org",
"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":"rigoberto.vonrueden@example.org",
"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":"gleason.kris@example.org",
"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"
}
}
}
As you can see from the output sortByDesc('created_at); is not working and I have absolutely no idea how to paginate this.
Attempt 2
I've also tried to query all reports and group the reports together.
$reports = Report::with('reportable','user')->latest()->paginate();
return $reports->groupBy('reportable_id');
Output:
{
"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":"breanne09@example.com",
"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":"beth.dach@example.org",
"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":"hollis.ferry@example.com",
"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":"hadley17@example.com",
"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":"amya.daniel@example.com",
"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":"priscilla.hamill@example.net",
"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":"emard.rene@example.org",
"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":"tiffany99@example.org",
"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":"gleason.kris@example.org",
"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":"rigoberto.vonrueden@example.org",
"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"
}
}
]
}
Here is an image that shows the output I am trying to obtain:
Is there a way to do this that's relatively efficient? Do I have to do a raw query on the DB and use Union and join like this other thread? and some how paginate the output? Is there a way I can structure or break up my reports table to handle this better?
laravel eloquent
laravel eloquent
asked Nov 28 '18 at 21:20
user3325126user3325126
355216
asked Nov 28 '18 at 21:20
user3325126user3325126
355216
edited Nov 28 '18 at 21:46
user3325126
asked Nov 28 '18 at 21:20
user3325126user3325126
355216
asked Nov 28 '18 at 21:20
user3325126user3325126
355216
asked Nov 28 '18 at 21:20
user3325126user3325126
355216
355216
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
1
Try using Constraining Eager Loads assuming all your relations are setup correctly and with reverse look up.
- Post hasMany Report
- Comment HasMany Report
- Report belongsTo Post
- Report belongsTo Comment
https://laravel.com/docs/5.7/eloquent-relationships
Look at part on Defining The Inverse Of The Relationship
$reports = Report::with([
'posts' => function($query) {
$query->latest();
},
'comments' => function($query) {
$query->latest();
}
])
->latest()
->paginate(15);
return $posts;
answered Nov 28 '18 at 22:14
Marc NewtonMarc Newton
2,0231426
Thank you for the attempt, but the comments reports are only output if they have a post that has been reported. Output of query
– user3325126
Nov 28 '18 at 22:49
However since the Report model has a morphTo relationship with the post and comment modelreportable()you might be able to use Constrain eager loading in the same way. I'll make another attempt in main post.
– user3325126
Nov 28 '18 at 22:55
Correction I don't believe that will work.
– user3325126
Nov 28 '18 at 23:33
Ohh, i see.. You want to do an inverse relation lookup, I update my suggestion. Hope that sets you in the correct direction, its been over a year since I done those sorts of models, I can't remember all the details.
– Marc Newton
Nov 29 '18 at 9:11
add a comment |
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53528288%2fmerge-two-queries-together-sortbydesc-and-paginate%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
1
Try using Constraining Eager Loads assuming all your relations are setup correctly and with reverse look up.
- Post hasMany Report
- Comment HasMany Report
- Report belongsTo Post
- Report belongsTo Comment
https://laravel.com/docs/5.7/eloquent-relationships
Look at part on Defining The Inverse Of The Relationship
$reports = Report::with([
'posts' => function($query) {
$query->latest();
},
'comments' => function($query) {
$query->latest();
}
])
->latest()
->paginate(15);
return $posts;
answered Nov 28 '18 at 22:14
Marc NewtonMarc Newton
2,0231426
Thank you for the attempt, but the comments reports are only output if they have a post that has been reported. Output of query
– user3325126
Nov 28 '18 at 22:49
However since the Report model has a morphTo relationship with the post and comment modelreportable()you might be able to use Constrain eager loading in the same way. I'll make another attempt in main post.
– user3325126
Nov 28 '18 at 22:55
Correction I don't believe that will work.
– user3325126
Nov 28 '18 at 23:33
Ohh, i see.. You want to do an inverse relation lookup, I update my suggestion. Hope that sets you in the correct direction, its been over a year since I done those sorts of models, I can't remember all the details.
– Marc Newton
Nov 29 '18 at 9:11
add a comment |
1
Try using Constraining Eager Loads assuming all your relations are setup correctly and with reverse look up.
- Post hasMany Report
- Comment HasMany Report
- Report belongsTo Post
- Report belongsTo Comment
https://laravel.com/docs/5.7/eloquent-relationships
Look at part on Defining The Inverse Of The Relationship
$reports = Report::with([
'posts' => function($query) {
$query->latest();
},
'comments' => function($query) {
$query->latest();
}
])
->latest()
->paginate(15);
return $posts;
answered Nov 28 '18 at 22:14
Marc NewtonMarc Newton
2,0231426
Thank you for the attempt, but the comments reports are only output if they have a post that has been reported. Output of query
– user3325126
Nov 28 '18 at 22:49
However since the Report model has a morphTo relationship with the post and comment modelreportable()you might be able to use Constrain eager loading in the same way. I'll make another attempt in main post.
– user3325126
Nov 28 '18 at 22:55
Correction I don't believe that will work.
– user3325126
Nov 28 '18 at 23:33
Ohh, i see.. You want to do an inverse relation lookup, I update my suggestion. Hope that sets you in the correct direction, its been over a year since I done those sorts of models, I can't remember all the details.
– Marc Newton
Nov 29 '18 at 9:11
add a comment |
1
1
1
Try using Constraining Eager Loads assuming all your relations are setup correctly and with reverse look up.
- Post hasMany Report
- Comment HasMany Report
- Report belongsTo Post
- Report belongsTo Comment
https://laravel.com/docs/5.7/eloquent-relationships
Look at part on Defining The Inverse Of The Relationship
$reports = Report::with([
'posts' => function($query) {
$query->latest();
},
'comments' => function($query) {
$query->latest();
}
])
->latest()
->paginate(15);
return $posts;
answered Nov 28 '18 at 22:14
Marc NewtonMarc Newton
2,0231426
Try using Constraining Eager Loads assuming all your relations are setup correctly and with reverse look up.
- Post hasMany Report
- Comment HasMany Report
- Report belongsTo Post
- Report belongsTo Comment
https://laravel.com/docs/5.7/eloquent-relationships
Look at part on Defining The Inverse Of The Relationship
$reports = Report::with([
'posts' => function($query) {
$query->latest();
},
'comments' => function($query) {
$query->latest();
}
])
->latest()
->paginate(15);
return $posts;
answered Nov 28 '18 at 22:14
Marc NewtonMarc Newton
2,0231426
edited Dec 1 '18 at 16:53
answered Nov 28 '18 at 22:14
Marc NewtonMarc Newton
2,0231426
answered Nov 28 '18 at 22:14
Marc NewtonMarc Newton
2,0231426
answered Nov 28 '18 at 22:14
Marc NewtonMarc Newton
2,0231426
2,0231426
Thank you for the attempt, but the comments reports are only output if they have a post that has been reported. Output of query
– user3325126
Nov 28 '18 at 22:49
However since the Report model has a morphTo relationship with the post and comment modelreportable()you might be able to use Constrain eager loading in the same way. I'll make another attempt in main post.
– user3325126
Nov 28 '18 at 22:55
Correction I don't believe that will work.
– user3325126
Nov 28 '18 at 23:33
Ohh, i see.. You want to do an inverse relation lookup, I update my suggestion. Hope that sets you in the correct direction, its been over a year since I done those sorts of models, I can't remember all the details.
– Marc Newton
Nov 29 '18 at 9:11
add a comment |
Thank you for the attempt, but the comments reports are only output if they have a post that has been reported. Output of query
– user3325126
Nov 28 '18 at 22:49
However since the Report model has a morphTo relationship with the post and comment modelreportable()you might be able to use Constrain eager loading in the same way. I'll make another attempt in main post.
– user3325126
Nov 28 '18 at 22:55
Correction I don't believe that will work.
– user3325126
Nov 28 '18 at 23:33
Ohh, i see.. You want to do an inverse relation lookup, I update my suggestion. Hope that sets you in the correct direction, its been over a year since I done those sorts of models, I can't remember all the details.
– Marc Newton
Nov 29 '18 at 9:11
Thank you for the attempt, but the comments reports are only output if they have a post that has been reported. Output of query
– user3325126
Nov 28 '18 at 22:49
Thank you for the attempt, but the comments reports are only output if they have a post that has been reported. Output of query
– user3325126
Nov 28 '18 at 22:49
However since the Report model has a morphTo relationship with the post and comment model
reportable() you might be able to use Constrain eager loading in the same way. I'll make another attempt in main post.– user3325126
Nov 28 '18 at 22:55
However since the Report model has a morphTo relationship with the post and comment model
reportable() you might be able to use Constrain eager loading in the same way. I'll make another attempt in main post.– user3325126
Nov 28 '18 at 22:55
Correction I don't believe that will work.
– user3325126
Nov 28 '18 at 23:33
Correction I don't believe that will work.
– user3325126
Nov 28 '18 at 23:33
Ohh, i see.. You want to do an inverse relation lookup, I update my suggestion. Hope that sets you in the correct direction, its been over a year since I done those sorts of models, I can't remember all the details.
– Marc Newton
Nov 29 '18 at 9:11
Ohh, i see.. You want to do an inverse relation lookup, I update my suggestion. Hope that sets you in the correct direction, its been over a year since I done those sorts of models, I can't remember all the details.
– Marc Newton
Nov 29 '18 at 9:11
add a comment |
draft saved
draft discarded
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53528288%2fmerge-two-queries-together-sortbydesc-and-paginate%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
