Thread

I think I’ve discovered a bug with EE2 and the orderby parameter. When I use the following:

{exp:channel:entries channel="sessions" dynamic="off" orderby="entry_date|expiration_date" sort="asc|asc" show_expired="yes" show_future_entries="yes" start_on="2011-11-09 1:00" stop_before="2011-11-09 23:00"}

and I have output profiler turned on so I can see the SQL queries being run, I can see that it ignores expiration_date in the order by clause in the SQL query. If I change the above to this:

{exp:channel:entries channel="sessions" dynamic="off" orderby="expiration_date|entry_date" sort="asc|asc" show_expired="yes" show_future_entries="yes" start_on="2011-11-09 1:00" stop_before="2011-11-09 23:00"}

(where expiration_date is the first field to be sorted) I can see expiration_date is included in the SQL query as a field to order by.

I’d rather not have to use exp:query just to get around this, but I need the entries sorted on both entry and expiration. Further complicating this is the fact that EE artificially changes the entry_date and expiration_date by a few seconds so even though two entries have the same date entered in the control panel, they won’t be viewed as having the same entry_date in the SQL query due to the timestamps being off.

dfravel,

Lets examine the doc’s example

if you wish to order by screen_name alphabetically and then have the most recent entries first, you would use the following parameters:

orderby="screen_name|date" sort="asc|desc"

This makes sense but in your example you are using two dates so one is going to override the other.
You can see that happening in your profiler when you swap them around.

Can you put this in context for us please with an example what you are using this for. It sounds like an edge case but hearing what you are using it for will help

Also the correct value is dynamic=“no”

Thanks John.

As a sidenote, I did know the dynamic=“no” is the correct value but when I first started using EE2 (possibly an earlier build), I noticed that this didn’t always work but dynamic=“off” was consistently working so I stuck with that.

For some background on the example I provided, we are using the entry_date and expiration_date as the start/end times for entries. Some entries may share a start time but have different ending times (and vice versa). In order to display the entries in the proper order, I need to sort by both the entry and expiration dates in ascending order.

My point is that the expiration_date is completely ignored if it is not the first value in the orderby parameter (since it does not show up in the query at all). The second example I provided

{exp:channel:entries channel="sessions" dynamic="off" orderby="expiration_date|entry_date" sort="asc|asc" show_expired="yes" show_future_entries="yes" start_on="2011-11-09 1:00" stop_before="2011-11-09 23:00"}

results in this query displayed in the profiler:

SELECT t.entry_id FROM exp_channel_titles AS t
                LEFT JOIN exp_channels ON t.channel_id = exp_channels.channel_id LEFT JOIN exp_members AS m ON m.member_id = t.author_id WHERE t.entry_id !='' AND t.site_id IN ('3') AND (t.channel_id = '4' OR t.channel_id = '10' ) AND t.entry_date >= '1320818445' AND t.entry_date < '1320897645' AND t.status = 'open' ORDER BY t.sticky desc, t.expiration_date asc, t.entry_date asc, t.entry_id asc LIMIT 0, 100

So it is correctly sorting on both entry_date and expiration_date. The problem is that I want the entry_date to be the first field sorted on, not the second.

Hey dfravel–

I think the conflict you’re seeing here, and why EE can’t use two date fields to sort, is because even two entries with the “same” start date don’t actually have the same start date timestamp. Even if your start date’s hour and minute are the same, the seconds probably aren’t, so EE is sorting right down to the timestamp.

Does that make sense?

Kevin

Hi Kevin,

Missed your reply on June 8 and just revisited the issue today. I did modify the entry_date and expiration_date timestamps so that two entries with the same entry_date do have the same timestamp and I’m still seeing the same result as above - the expiration_date is being completely ignored in the SQL query if it appears after entry_date in the orderby=”” parameter.

Here’s a sample with two entries:

entry #1 has an entry_date timestamp of 1320845405 and an expiration of 1320852605
entry #2 has an entry_date timestamp of 1320845405 and an expiration of 1320849005

Using orderby=“entry_date|expiration_date” sort=“asc|asc” I *should* see entry #2 listed first, and then entry #1 since it has a later expiration date. But since expiration_date is *ignored* in the orderby parameter and doesn’t show up in the SQL query at all (if it isn’t first), this isn’t working.

Hey dfravel,

I’m going to bring in one of the developers on this to see what’s going on here. Thanks for your patience!

Thanks so much Kevin. I was able to work around this for the time being by using {exp:query} but look forward to hearing the outcome of this issue.

Sounds good, dfravel. Glad you have a workaround at the moment, we’ll add to the thread as we get additional information from the dev team. Thanks in advance for your patience.

Sorry for the long wait dfravel,

There’s a bit of logic in that code that looks for ‘date’, but not ‘entry_date’. No doubt an attempt to make simple sorting more intuitive. It stops sorting after any that it doesn’t recognize, so anything after ‘entry_date’ is ignored.

I’ll change it to look for both in the next release, but for now using orderby=“date|expiration_date” should force it to be included.

Let us know how that works out 😊 .