Laravel syntax error due to nonaggregated column with select DB:raw and GROUP BY

Question

I have the following 4 SQL tables:

studios
integer id
string name
integer last_movie_id

movies
integer id
integer studio_id
string type

actor_movie
integer movie_id
integer actor_id

actors
integer id
string name

I'm trying to build a query that gives me for a specific actor, the list of the studios he worked for, including the last movie of the studio (wether the actor played in it or not):

• name of the studio
• date of the last movie from this studio
• type of the last movie from this studio

My code is the following (in ActorController):

$actor = \App\Actor::findOrFail($id);
$studios = \DB::table('actor_movie')
            ->leftjoin('movies', 'actor_movie.movie_id', '=', 'movies.id')
            ->leftjoin('studios', 'studios.id', '=', 'movies.studio_id')
            ->select(
                'studios.name',
                'studios.last_movie_date'
                \DB::raw('(SELECT movies.type FROM movies WHERE movies.id = studios.last_movie_id') as type')
            ->where('actor_movie.actor_id', $actor->id)
            ->groupBy('studios.name', 'studios.last_movie_date', 'type')
            ->get();

But I have the following error: SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #3 of SELECT list is not in GROUP BY clause and contains nonaggregated column [...] this is incompatible with sql_mode=only_full_group_by

I don't want to set the sql_strict_mode to false in order to circumvent this error. I would like to understand what's wrong with my query. I tried using aggregation functions like "ANY_VALUE" but nothing worked out.

Answer 1

You have written the query wrongly - specifically

\DB::raw('(SELECT movies.type FROM movies WHERE movies.id = studios.last_movie_id') as type which is in select statement. This query gives you value(s) not COLUNM NAME which should be in select statement like select col1Name, col2Name, .... from .....

As you did not provide full table structures & relation(i.e-you mentioned studios.last_movie_date in query but not in question) it is very hard for anyone to give you correct query but the below should work fine -

$actor = \App\Actor::findOrFail($id);
$studios = \DB::table('actor_movie')
            ->leftjoin('movies', 'actor_movie.movie_id', '=', 'movies.id')
            ->leftjoin('studios', 'studios.id', '=', 'movies.studio_id')
            ->leftjoin('movies as mov2', 'studios.last_movie_id', '=', 'mov2.id')
            ->select(
                'studios.name',
                'studios.last_movie_date',
                'mov2.type'
                )
            ->where('actor_movie.actor_id', $actor->id)
            ->groupBy('studios.name', 'studios.last_movie_date', 'type')
            ->get();

For other queries I think you got the idea.

How would I do it -

create a migration for below query -

create view actors_studio as 
select am.actor_id, mov.studio_id, std.name as studio_name, std.last_mov_id, mov2.type as last_mov_type
from actor_movie as am
left join movies as mov on am.movie_id = mov.id
left join studios as std on std.id = mov.studio_id
left join movies as mov2 on std.last_movie_id = mov2.id
group by  actor_id, studio_id, studio_name, last_mov_id, last_mov_type
order by actor_id, studio_id;

Then run the migrate command and then create a model for view actors_studio i.e VwActorStudios and then a simple query

$studios = VwActorStudios::where('actor_id', $actor->id)->get();