Pagination breaks sort order

Need Help
#1

I have a Tailor collection archives with a sort order property defined on date desc. This code works fine, the items are displayed in the requested order:

{% for item in archives %}
     <div>{{ item.title }} {{ item.date }}</div>
{% endfor %}

Even when I copy the records into a variable, it works (passing the variable to a partial works too):

{% set items = archives %} 
{% for item in items %}
     <div>{{ item.title }} {{ item.date }}</div>
{% endfor %}

However, adding pagination, the sort order is broken.

{% set items = archives.paginate(10) %} 
{% for item in items %}
     <div>{{ item.title }} {{ item.date }}</div>
{% endfor %}

Any idea how to present the records paginated and sorted?

#2

Hi @carnecro

The archives variable contains a pre-built collection based on the component properties.

However, calling a method on this variable transforms it into a new query with no constraints; it means you would have to set the .orderBy('date desc') on that query.

It works this way for simplicity. Although, the expectation for the sort order property be applied appears to be a valid one…

#3

Thanks for the tip. I ended up with the following code

{% set items = archives.orderByDesc('date').paginate(15) %}

Please note the function .orderByDesc('date'), your hint .orderBy('date desc') did not work.

Just a question: does it mean that there are two queries fired on the database? One for the collection and one for the pagination? I don’t think that’s very efficient. We have about 1000 articles in the archive, it is not such a problem for the database, but for a really large amount of data it could be a noticeable delay.

#4

I can confirm that paginate is the only function to produce a query. The orderByDesc is similar to where which “builds” the query, by adding constraints to it. This is how Laravel’s query builder functions.

I hope this helps!

#5

Looks like I’ll have to try some spying to see when the database queries are fired. I always thought that collection is a variable that already contains data from the database and the for loop just iterates over the data.

[collection mycollection]
handle = "MyHandle"
==

{% for item in mycollection %}
   {# do someting with item #}
{% endfor %}

Does it mean, that a database query is only fired when the collection is used in the for statement and the collection contains only some kind of metadata used by the query builder to build the proper database query?

1 Like
#6

When you access mycollection directly as a variable (property access), it performs the default query lookup and hits the database. For example:

{% for item in mycollection %}

When you access mycollection using a method call (method access). It returns a new query constraint and does not hit the database, for example:

{% set query = mycollection.orderByDesc('date') %}

When you call the paginate method, this explicitly produces a query result and hits the database, for example:

{% set result = query.paginate(15) %}

We might consider the first example as the “simple usage” and the second/third as “advanced usage”.