Incompatible with jordanmiguel/laravel-popular using Mysql 8

[Benjaber-98]

I upgraded to Mysql 8 from Mysql 5.7, The application was working correctly but now its giving me this SQL error

Column not found: 1054 Unknown column 'visits_count' in 'window order by' (SQL: select * from (select `ads`.*, (select count(*) from `visits` where `ads`.`id` = `visits`.`visitable_id` and `visits`.`visitable_type` = App\\Models\\Ad and `date` >= 2022-03-30) as `visits_count`, row_number() over (partition by `ads`.`category_id` order by `visits_count` desc) as laravel_row from `ads` where  `ads`.`category_id` in (1)) as laravel_table where laravel_row <= 10 order by laravel_row)

My code is this

$category->load(['popularAdsLastWeek' => fn($q) => $q->limit(10)]);

//Model Relation
public function popularAdsLastWeek()
    {
        return $this->ads()->popularWeek();
    }

[staudenmeir]

Do you have an easy way to switch back to MySQL 5.7 for a moment to log the original query that worked?

[Benjaber-98]

I forced it to use the useLegacyGroupLimit($query) "version < 8.0.11" as in Grammars\Traits\CompilesMySqlGroupLimit line 36 and this is what executed

select
  laravel_table.*,
  @laravel_row := if(
    @laravel_partition = `category_id`,
    @laravel_row + 1,
    1
  ) as laravel_row,
  @laravel_partition := `category_id`
from
  (
    select
      @laravel_row := 0,
      @laravel_partition := 0
  ) as laravel_vars,
  (
    select
      `ads`.*,
      (
        select
          count(*)
        from
          `visits`
        where
          `ads`.`id` = `visits`.`visitable_id`
          and `visits`.`visitable_type` = 'App\Models\Ad'
          and `date` >= '2022-03-30'
      ) as `visits_count`
    from
      `ads`
    where
      `ads`.`category_id` in (1)
    order by
      `ads`.`category_id` asc,
      `visits_count` desc
  ) as laravel_table
having
  laravel_row <= 10
order by
  laravel_row

[staudenmeir]

Unfortunately, window functions can't access derived/computed columns like visits_count. The fallback query for MySQL 5.7 only worked because it has a completely different (and more inefficient) structure.

You need to run the subquery inside the ORDER BY clause: https://github.com/staudenmeir/eloquent-eager-limit/issues/9#issuecomment-487640882

Theoretically, you could force the package to continue using the old query, but I wouldn't recommend that.