我正在使用 Laravel 5.7。我有三个 MySQL 表,名称为:movies, movie_directors, movie_actors。我在从这些表中检索数据时遇到问题。看看我的表结构:
movies
|-----------------------------------------------------|
| id | title | release | img_url |
|-----------------------------------------------------|
| 1 | Avengers | 2019 | # |
|-----------------------------------------------------|
| 2 | Avatar | 2009 | # |
|-----------------------------------------------------|
| 3 | Titanic | 1997 | # |
|-----------------------------------------------------|
directors
|-----------------------------------------------------|
| id | movie_id | dir_name | add_date |
|-----------------------------------------------------|
| 1 | 1 | Anthony Russo | 2019/02/18 |
|-----------------------------------------------------|
| 2 | 1 | Joe Russo | 2019/02/18 |
|-----------------------------------------------------|
| 3 | 2 | Cameron | 2019/02/18 |
|-----------------------------------------------------|
actors
|-----------------------------------------------------|
| id | movie_id | act_name | add_date |
|-----------------------------------------------------|
| 1 | 1 | Robert Downey | 2019/02/18 |
|-----------------------------------------------------|
| 2 | 1 | Chris Evans | 2019/02/18 |
|-----------------------------------------------------|
| 3 | 1 | Mark Ruffalo | 2019/02/18 |
|-----------------------------------------------------|
| 4 | 1 | Chris Pratt | 2019/02/18 |
|-----------------------------------------------------|
| 5 | 2 | Worthington | 2019/02/18 |
|-----------------------------------------------------|
| 6 | 2 | Weaver | 2019/02/18 |
|-----------------------------------------------------|
| 7 | 2 | Saldana | 2019/02/18 |
|-----------------------------------------------------|
I Want
|-------------------------------------------------------------------------------------------------------------------------------------------|
| id | title | release | img_url | directors | actors |
|-------------------------------------------------------------------------------------------------------------------------------------------|
| 1 | Avengers | 2019 | # | Anthony Russo, Joe Russo | Robert Downey, Chris Evans, Mark Ruffalo, Chris Pratt |
|-------------------------------------------------------------------------------------------------------------------------------------------|
| 2 | Avatar | 2009 | # | Cameron | Worthington, Weaver, Saldana |
|-------------------------------------------------------------------------------------------------------------------------------------------|
| 3 | Titanic | 1997 | # | |
|-------------------------------------------------------------------------------------------------------------------------------------------|
我正在使用的当前代码:
$movie_list = DB::table('movies')
->select('movies.*', 'movie_directors.*', 'movie_actors.*', 'movie_genres.*', 'movie_links.*', 'movie_types.*')
->join('movie_directors','movies.id', '=','movie_directors.movie_id')
->join('movie_actors', 'movies.id', '=', 'movie_actors.movie_id')
->where('movies.status',1)
->paginate(50)
;
查看代码底部的表格“我想要”。这就是我要的。请不要附上下面的链接作为已经回答的证据。如何在laravel eloquent中从多个表中检索数据与我的要求
不符
为了实现这一点,您需要按movies.id 对结果进行分组,并使用group_concat
逗号连接来提取结果。您的代码应如下所示:
$movie_list = DB::table('movies')
->select(
'movies.*',
DB::raw('group_concat(movie_directors.dir_name) as directors'),
DB::raw('group_concat(movie_actors.act_name) as actors')
)
->join('movie_directors','movies.id', '=','movie_directors.movie_id')
->join('movie_actors', 'movies.id', '=', 'movie_actors.movie_id')
->where('movies.status',1)
->groupBy('movies.id')
->paginate(50)
;
您可以group_concat
在此处阅读更多信息:https : //www.w3resource.com/mysql/aggregate-functions-and-grouping/aggregate-functions-and-grouping-group_concat.php
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句