Sql error on displaying category posts (The ORDER BY clause is invalid in views, inline functions, derived tables, ..)

I am using tailor, I put this in the page

 {% set posts = blog.whereRelation('categories', 'slug', category.slug).paginate(2) %}

and I get

An exception has been thrown during the rendering of a template ("SQLSTATE[42000]: [Microsoft][ODBC Driver 18 for SQL Server][SQL Server]The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP, OFFSET or FOR XML is also specified. (SQL: select count(*) as aggregate from [xc_3d7b918a4e2f43a1bee3a9df84dbbf9fc] where [is_enabled] = 1 and exists (select * from [xc_11b6f8fd289d49509ed560ff2da3779ec] where [xc_3d7b918a4e2f43a1bee3a9df84dbbf9fc].[categories_id] = [xc_11b6f8fd289d49509ed560ff2da3779ec].[id] and [slug] = marketing-list and [xc_11b6f8fd289d49509ed560ff2da3779ec].[draft_mode] = 1 and [xc_11b6f8fd289d49509ed560ff2da3779ec].[is_version] = 0 order by [nest_left] asc) and [xc_3d7b918a4e2f43a1bee3a9df84dbbf9fc].[draft_mode] = 1 and [xc_3d7b918a4e2f43a1bee3a9df84dbbf9fc].[is_version] = 0)").

is this an ms sql server issue? or am i doing something wrong?

It’s a query strictness issue. It doesn’t like the ORDER BY clause in this subquery:

and exists (select * from [xc_11b6f8fd289d49509ed560ff2da3779ec] where ... order by [nest_left] asc)

Something we can fix, but not test, on our end. Will let you know when to update and try again.

1 Like

the help is very much appreciated

@daftspunky I apologize for bugging you all the time, just informationally, is there an ETA on this?

Sure, the patch is on its way. For now, you can test it by opening the file vendor/october/rain/src/Database/Builder.php and adding this new method definition to it:

Or you can wait for the next patch coming in the next couple of days.

1 Like