[trendoman]

I found some strange undesired behaviour. Please, help me find out the right way.
First, I use this code to list all pages in my clonable template:

Code: Select all

<cms:pages >
    <cms:show k_page_id /> - <cms:show k_page_date /><br>
</cms:pages>
---
Result:
19 - 2016-03-10 03:21:56
20 - 2016-03-10 03:21:56
17 - 2016-03-10 03:21:55
18 - 2016-03-10 03:21:55
16 - 2016-03-10 03:21:49
15 - 2016-03-10 00:31:18

notice the order of ids: 19, 20, 17, 18, 16, 15
Now, I put explicit date of the last element and get the same order:

Code: Select all

<cms:pages start_on='2016-03-10 00:31:18' >
    <cms:show k_page_id />,
  </cms:pages>
Result: 19, 20, 17, 18, 16, 15,

But what happens if I set date to the next value - date of page with id=16?

Code: Select all

<cms:pages start_on='2016-03-10 03:21:49' >
    <cms:show k_page_id />,
  </cms:pages>
Result: 20, 19, 18, 17, 16,

The order is different and I don't like it.

Any ideas?

[KK]

The list, by default, is being sorted by the publish_date.

If you notice, the publish_dates of 19 and 20 are the same -

19 - 2016-03-10 03:21:56
20 - 2016-03-10 03:21:56

Same is the case for 17 and 18

17 - 2016-03-10 03:21:55
18 - 2016-03-10 03:21:55

So, as far as SQL is concerned, the set of pages being fetched are sorted correctly (i.e. by publish_date). When the dates are the same, there is no guarantee as to how the IDs are fetched (after all we are not sorting by IDs).

Hope that explains it.

[trendoman]

Not really. cms:pages also uses sql, but the results are never different - every time the order is the same, though arbitrary.

[KK]

I'll try to explain a bit more.

When any SQL server is given a query, it internally creates a query plan that is optimized for performance. It only guarantees you to fetch pages in the order specified by the 'ORDER BY' parameter.

In your example the 'ORDER BY' is 'publish_date'.
So the only guarantee is that pages will be sorted according to the dates.
Obviously when the dates are the same for two pages it makes no difference as to which of the two is fetched before the other of the same date.

This can lead to the IDs being not identical for two different queries but the end result (as far as SQL server is concerned) is to fetch pages in the right order of their publish_date and not the IDs.

When you are adding parameters to cms:pages, you are altering the SQL query. This will result in a different plan and can lead to a different order of IDs (but not publish_date).

If you want the pages to always be sorted according to their IDs, you need to specify that in the SQL 'ORDER BY' clause - unfortunately cms:pages won't do this for you as it does not support ID as a valid parameter.

[trendoman]

What you are trying to explain is that 'cms:pages' and 'cms:pages start_on' are 2 different queries, so you basically don't have control over what's pop out, unless you bring more chips to the game. The same pages are fetched in both operations, but the mechanics are different each time. I got you. People wake up every morning, but no morning is the same (almost there).