I got a message from a client complaining that the items in one of their Views was all out of order. I could have sworn that that particular View worked just fine at some point, but I poked with it and couldn't get to behave itself again - it seemed to be ignoring its "Sort criteria." Finally I gave up and recreated the View on my local copy of the site, confirmed that it worked, then exported that View and imported it on the live site (and yes, I disabled the previous View), only to see… the items were out of order again. I compared the queries that Views was building in the "Preview" thingie, and sure enough, they were different. Notably, I've upgraded Views on the live site to 2.7, but I'm still using 2.6 on my local server.

On the live server (2.7):

SELECT node.nid AS nid,
[…]
FROM node node
LEFT JOIN content_type_program node_data_field_rep_airdate ON node.vid = node_data_field_rep_airdate.vid
WHERE (node.type in ('program')) AND (node.status <> 0)

On my local server (2.6):

SELECT node.nid AS nid,
[…]
FROM node node
LEFT JOIN content_type_program node_data_field_rep_airdate ON node.vid = node_data_field_rep_airdate.vid
LEFT JOIN content_type_program node_data_field_airdate ON node.vid = node_data_field_airdate.vid
WHERE (node.type in ('program')) AND (node.status <> 0)
ORDER BY node_data_field_airdate_field_airdate_value DESC, node_sticky DESC

Note how on 2.7, the join for the field we want to order by is completely gone, as is the entire ORDER BY clause.

Please advise. Will the world explode if I downgrade back to 2.6 for now?

CommentFileSizeAuthor
#16 622602-fix-missing-order-by.patch2.42 KBmerlinofchaos
#1 exported-view.txt9.52 KBGarrett Albright

Comments

Garrett Albright’s picture

Garrett Albright commented
StatusFileSize
new exported-view.txt9.52 KB

By the way, if you want to see this happening live, head to http://aworldofpossibilities.org/recent (warning: politically slanted site; I'm just the web guy). It's showing the nodes in the order they were inserted in the database instead of by the "Original airdate" field. It looks like they're in the right order, because if the way they were imported from the old database, but if you look at the last page, you'll see the newest nodes are actually at the end, and in the wrong order.

And if it matters, we're using MySQL 5-point-something.

And if it matters… attached is an export of the View.

jrisberg’s picture

jrisberg commented

I report exactly the same issue. I just upgraded to views 2.7 and now the 'order by' is lost.

dawehner’s picture

dawehner
Primary language German
commented

If you make a backup you can try to go back to 2.6.
I think it would be possible without destroy your site, but please please make backups! Yes restorable backups.

As you saw, the order by is lost.
Perhaps you could try to update to the latest 2.x-dev of cck. Perhaps this helps. BUT make backups!

bitsman’s picture

bitsman commented

I confirm the problem with ORDER BY, and also that backgrade to 2.6 works OK.

Leeteq’s picture

Leeteq commented
Priority: Normal » Critical

Would be nice with a confirmation on whether this is happening only when upgrading older queries, and not a problem if recreating the same view(s) from scratch with 2.7.

sillygwailo’s picture

sillygwailo
Location Toronto, ON
commented

This appears to happen to upgraded views, not views created from scratch. That is, I created an entirely new view, and the sort order is working. Sort orders on older views do not respect what I have in the views.

sillygwailo’s picture

sillygwailo
Location Toronto, ON
commented

Going out on a limb to say that this only affects table views. And that changing the style from table to node, then back to table restores the sort order.

kars-t’s picture

kars-t commented

As described in #7 old Views changed to nodes and than to table will have the correct sortorder again.

Garrett Albright’s picture

Garrett Albright commented

Confirmed that rebuilding the View from the ground up in 2.7 worked. Didn't try switching to nodes and then back to table, but I wish I had before I had gone ahead and rebuilt this damned thing again.

giorgosk’s picture

giorgosk
Location Chios, Greece 🇬🇷
commented

Here what I did and things got back to normal #624042: Sorting of data appears not to be working after update

bdanchilla’s picture

bdanchilla commented

same (albeit 2.5 to 2.7), noticed also that if distinct is set to yes, than 2.7 is

...GROUP by nid and earlier was

SELECT DISTINCT(node.nid)...

gordon.waters’s picture

gordon.waters commented

I report exactly the same issue. I just upgraded to views 2.7 and now the 'order by' is lost.

I also followed this http://drupal.org/node/624042 to get it working again.

merlinofchaos’s picture

merlinofchaos commented

I recently noticed that the checkbox "Override normal sorting if click sorting is used" seems to be overriding normal sorting even *if* the default table sort is set to none. One quick workaround might be to uncheck that box.

bitsman’s picture

bitsman commented

I confirm that unchecking "Override normal sorting if click sorting is used" is working

merlinofchaos’s picture

merlinofchaos commented

Ok, what's crazy is that I saw this happen once while I was doing something else. I took note of it, and when I came back to figure out what was going on, I couldn't make it happen again. Which means I don't know exactly why it was happening. Ugh.

merlinofchaos’s picture

merlinofchaos commented
Status: Active » Fixed
StatusFileSize
new 622602-fix-missing-order-by.patch2.42 KB

Ok, found it and fixed it. Patch is attached for those who need a quick fix.

rickumali’s picture

rickumali commented

Thanks for posting this patch. I just upgraded to latest views this evening, and found this issue. I applied this patch, and all is well. Looking forward to the next release.

Status: Fixed » Closed (fixed)

Automatically closed -- issue fixed for 2 weeks with no activity.