Laravel and CTE's

Question

I'm trying to convert the following SQL query into Laravel code using taudenmeirs' laravel-cte package.

WITH `cte` AS (
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY channel_id ORDER BY created_at DESC) AS row_number
      FROM `Videos`
)
SELECT `Channels`.*, `cte`.*
FROM `Channels`
LEFT JOIN `cte`
       ON `Channels`.`id` = `cte`.`channel_id`
WHERE `cte`.`row_number` = 1;

The problem is I keep getting the following response from the server:

Illuminate\Database\QueryException: SQLSTATE[42000]: Syntax error or access violation: 1140 Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause (SQL: with cte as (select , ROW_NUMBER() OVER (PARTITION BY channel_id ORDER BY created_at DESC) AS row_number from Videos) select Channels. from Channels left join cte on Channels.id = cte.channel_id where cte.rn = 1) in file /Users/mark/Workspace/api.site/vendor/laravel/framework/src/Illuminate/Database/Connection.php on line 665

@MadhurBhaiya absolutely. I thought I had. Fixed. – Mark D Oct 03 '19 at 16:31 — Mark D, Oct 03 '19 at 16:31

score 0 · Accepted Answer · answered Jan 16 '20 at 05:28

I actually got this answer from taudenmeirs over on github where I submitted this issue as a bug. It turns out that the error is caused by a bug in MariaDB: https://jira.mariadb.org/browse/MDEV-17785

The ONLY_FULL_GROUP_BY mode is enabled in Laravel by default. You can disable it by setting 'strict' => false in your config/database.php file.

This actually does solve the issue. However it would be better if the fine folks over at Maria could solve the underlying bug.

Laravel and CTE's

1 Answers1