SQLSTATE[42S22]: Column not found when using cursorPaginate and orderBy alias(withCount)

[chinh2597]

• Laravel Version: v8.70.2
• PHP Version: 7.4.24
• Database Driver & Version: MySQL 8.0.26

Description:

the error occurs when loading the next page, my query looklike:

Story::withCount(['storyLikeRelationship as LikeCountRecent'=>function($query){
    $thresholdDate = Carbon::now()->subHours(24)->format(Constant::$DATE_TIME_FORMAT);
    $query->where('CreatedAt','>',$thresholdDate);
}])->orderBy('LikeCountRecent','desc')->cursorPaginate(10);

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'LikeCountRecent' in 'where clause'
Maybe the cursorPaginate try to query on LikeCountRecent column where (`LikeCountRecent` < 0 or (`LikeCountRecent` = 0 and (`Id` < 246))) order by `LikeCountRecent` desc, `Id` desc limit 21)

Steps To Reproduce:

[cyrildewit]

This looks similar to cyrildewit/eloquent-viewable#263. I think this might be a limitation of the cursorPaginate functionality. simplePaginate will probably work. I wonder if we ever could use the cursorPaginate with such aliased columns. I don't think so, but I'm diving into the cursor logic for the first time, so I have zero experience within this topic.

[gguney]

You should put it to another query as a subquery then you can use its alias

[chinh2597]

but mysql doesn't allow to use alias in where caluse