I have the below raw sql query which works as expected

SELECT T1.category, T1.group, T1.series, T1.name, T2.cover
FROM (
    SELECT category, MAX(COALESCE(image)) as cover
    FROM stones
    GROUP BY `category`
    HAVING COUNT(DISTINCT category) = 1
    ) T2
    INNER JOIN stones T1 on T2.category = T1.category;

While trying to convert it to Laravel Query Builder syntax, I tried the following

DB::table('stones', 't1')
    ->select('t1.category', 't1.group', 't1.series', 't1.name', 't2.cover')
    ->from(DB::raw('(SELECT category, MAX(COALESCE(image)) as cover) FROM stones GROUP BY `category` HAVING COUNT(DISTINCT category)=1) t2'))
    ->join('stones as t2', 't2.category', '=', 't1.category')
    ->get();

It gives an error

Illuminate\Database\QueryException with message 'SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FROM stones GROUP BY `category` HAVING COUNT(DISTINCT category)=1) t2 inner join' at line 1 (SQL: select `t1`.`category`, `t1`.`group`, `t1`.`series`, `t1`.`name`, `t2`.`cover` from (SELECT category, MAX(COALESCE(image)) as cover) FROM stones GROUP BY `category` HAVING COUNT(DISTINCT category)=1) t2 inner join `stones` as `t2` on `t2`.`category` = `t1`.`category`)'

When I try to dump the sql generated by toSql(), I do not see any difference with the raw sql, however am unable to spot the mistake.

# dump from toSql()

"select `t1`.`category`, `t1`.`group`, `t1`.`series`, `t1`.`name`, `t2`.`cover` from (SELECT category, MAX(COALESCE(image)) as cover) FROM stones GROUP BY `category` HAVING COUNT(DISTINCT category)=1) t2 inner join `stones` as `t2` on `t2`.`category` = `t1`.`category`"

CodePudding user response：

This could be done with direct select like the following:

$results = DB::select('you full query');
dd($results);

This will be much easier.

For more information check this

you can use the following facade methods instead of building eloquent query:

• DB::select for select complex query
• DB::statement for CRUD queries
• DB::insert for full insert queries directly
• DB::update for full update queries directly
• DB::delete for full delete queries directly

Note: all above methods should receive parameter bindings if needed

If you don't wanna bind parameters use DB::unprepared('query')

CodePudding user response：

Seems that in Query builder the ")" after "cover" should no be there, if you compare the output query and the one that toSql spills taht is the diference.

CodePudding user response：

Thanks @ChanYungKeat & @adevel for pointing out the correction "extra )". Removing that and swapping stones as t2 with stones as t1 in the join() makes it work. Below is the working query for reference

DB::table('stones', 't1')
    ->select('t1.category', 't1.group', 't1.series', 't1.name', 't2.cover')
    ->from(DB::raw('(SELECT category, MAX(COALESCE(image)) as cover FROM stones GROUP BY `category` HAVING COUNT(DISTINCT category)=1) t2'))
    ->join('stones as t1', 't2.category', '=', 't1.category')
    ->get();

EDIT:

My use case need to group the query results by category > group > series and prepare the data for serialization for consumption on front end. So I need an extra step to map over each result entry and convert it to an array (as stdClass objects returned from Query Builder may not serialize properly)

DB::table('stones', 't1')
    ->select('t1.category', 't1.group', 't1.series', 't1.name', 't2.cover')
    ->from(DB::raw('(SELECT category, MAX(COALESCE(image)) as cover FROM stones GROUP BY `category` HAVING COUNT(DISTINCT category)=1) t2'))
    ->join('stones as t1', 't2.category', '=', 't1.category')
    ->get()

    /* Here I need to map over and convert the stdClass objects 
     * returned by Query  Builder to array for proper serialization
     */
    ->map(function ($stone) {
        return ['category' => $stone->category, 'group' => $stone->group, 'series' => $stone->series, 'name' => $stone->name, 'ccover' => $stone->cover];
    })

    ->groupBy(['category', 'group', 'series'])
    ->toArray();

//OR using DB::select() as suggested by @Faesal

return collect(DB::select("SELECT t1.category, t1.group, t1.series, t1.name, t2.cover FROM stones as t1 JOIN (SELECT category, MAX(COALESCE(image)) as cover FROM stones GROUP BY `category` HAVING COUNT(DISTINCT category)=1) t2 ON t2.category = t1.category"))

    /* Here I need to map over and convert the stdClass objects 
     * returned by Query  Builder to array for proper serialization
     */
    ->map(function ($stone) {
        return ['category' => $stone->category, 'group' => $stone->group, 'series' => $stone->series, 'name' => $stone->name, 'ccover' => $stone->cover];
    })
    ->groupBy(['category', 'group', 'series'])
    ->toArray();

I also found that it can be done using Eloquent (instead of Query Builder). Since Eloquent returns objects of Illuminate\Database\Eloquent\Model there's not need for the extra step to map into array as serialization is taken care by the Model class.

Stone::query()
    ->select('t1.category', 't1.group', 't1.series', 't1.name', 't2.cover')
    ->from(DB::raw('(SELECT category, MAX(COALESCE(image)) as cover FROM stones GROUP BY `category` HAVING COUNT(DISTINCT category)=1) t2'))
    ->join('stones as t1', 't2.category', '=', 't1.category')
    ->get()
    ->groupBy(['category', 'group', 'series'])
    ->toArray();

Hope this helps someone in a similar situation.