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

Need Help
#1

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?

#2

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
#3

the help is very much appreciated

#4

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

#5

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