Drupal version: 7.43
MySQL version: 5.7.13-0ubuntu0.16.04.2

The admin/content page is being sorted by nid, instead of the updated date.

Details:

  1. I just finished setting up a Drupal site on a newer PC I received at work.
  2. I noticed the Content page was not being sorted by the UPDATED column. Clicking on this column made no difference.
  3. I went to the Administration: Nodes (Content) view, and copied and pasted the query into a sql tool that I use. The rows were being sorted by nid
  4. I removed the LIMIT 50 OFFSET 0 line, and then the result set was ordered correctly.

Below is the query:

SELECT DISTINCT node.nid AS nid, node.title AS node_title, node.language AS node_language, history.timestamp AS history_timestamp, node.created AS node_created, node.changed AS node_changed, node.type AS node_type, users_node.name AS users_node_name, users_node.uid AS users_node_uid, node.status AS node_status
FROM 
{node} node
LEFT JOIN {users} users_node ON node.uid = users_node.uid
LEFT JOIN {taxonomy_index} taxonomy_index ON node.nid = taxonomy_index.nid
LEFT JOIN {taxonomy_term_data} taxonomy_term_data_node ON taxonomy_index.tid = taxonomy_term_data_node.tid
LEFT JOIN {history} history ON node.nid = history.nid AND history.uid = '123'
ORDER BY node_changed DESC
LIMIT 50 OFFSET 0

Thank you for this very helpful module. We use it in production with MySQL 5.5 and we've never had a problem.

CommentFileSizeAuthor
#10 admin_views-n2802533-10.patch708 bytesDamienMcKenna
#4 admin_views-sort_criteria_is_not_working-2802533-4.patch748 bytesschifazl
Support from Acquia helps fund testing for Drupal Acquia logo

Comments

ann b created an issue. See original summary.

ann b’s picture

ann b CreditAttribution: ann b commented
Issue summary: View changes
selvanathan’s picture

selvanathan CreditAttribution: selvanathan commented

To solve this issue, enable aggregation in the view's Advanced configuration.
Simple and works well now.
Thanks to schifazl, he had mentioned the solution here in another issue.

schifazl’s picture

schifazl CreditAttribution: schifazl commented
Status: Active » Needs review
FileSize
admin_views-sort_criteria_is_not_working-2802533-4.patch748 bytes

Since the other issue was closed and only maintainers can reopen it, I'm posting the patch here, please review!

ann b’s picture

ann b CreditAttribution: ann b commented

RTBC +1. Thank you schifazl.

DigitalFrontiersMedia’s picture

DigitalFrontiersMedia
Location Sarasota, FL
CreditAttribution: DigitalFrontiersMedia commented

I applied the patch in #4 but it didn't fix the problem for me. I was using the default node content admin view that comes with the module configured to sort in descending node_changed order. However, as in #3, schifazi's suggestion in #2821908: Sort criteria is not working to turn aggregation on DID work.

With regard to future compatibility of Administration Views with MySQL >5.7.4, it appears that other things can force it to sort correctly (at least that is the case in my experimentation). So there may be other alternatives to consider as the basis of a patch. They all probably have pros and cons in how they work and performance. These are some things I found worked:
• Removing 'DISTINCT' from the query (as noted previously adds duplicates due to LEFT JOINs BUT they DO appear in the CORRECT ORDER and duplicates could be filtered out using other modules/methods/hooks).
• Removing all foreign table selections and LEFT JOINs resulting in a single node table query
• Adding a secondary sort like node_title ASC
• Changing the ORDER BY column to be a function/expression instead of just a normal column reference (MySQL will not use/rely on an index on an ORDER BY clause that is a function), e.g. 'ORDER BY node_changed+0 DESC' instead of 'ORDER BY node_changed DESC'.

DigitalFrontiersMedia’s picture

DigitalFrontiersMedia
Location Sarasota, FL
CreditAttribution: DigitalFrontiersMedia commented

I'll also note that this problem will likely fall squarely on admin_views module maintainers instead of MySQL maintainers since they claim this behavior is not a bug and won't fix (https://bugs.mysql.com/bug.php?id=33087, https://bugs.mysql.com/bug.php?id=88814) even though I found evidence that the former issue (10 years old) is NOT the same issue as the latter (which is what we're experiencing). The difference is subtle but their results are similar enough that you'd have to take the time to provide the evidence to convince the MySQL folks that those aren't duplicates. I tried but believe it or not, I got database issues when trying to register and login there at Oracle. :-P

Chris Matthews’s picture

Chris Matthews CreditAttribution: Chris Matthews as a volunteer commented
Version: 7.x-1.6 » 7.x-1.x-dev
Category: Support request » Task
Status: Needs review » Reviewed & tested by the community
Related issues: +#2821908: Sort criteria is not working

The two year old patch in #4 that was a follow up to #2821908: Sort criteria is not working applied cleanly to the latest 7.x-1.x-dev and fixes this issue for me.

Chris Matthews’s picture

Chris Matthews CreditAttribution: Chris Matthews as a volunteer commented
Parent issue: » #3018626: Plan for Administration Views 7.x-1.8 release
DamienMcKenna’s picture

DamienMcKenna
Location NH, USA
CreditAttribution: DamienMcKenna at Mediacurrent commented
FileSize
admin_views-n2802533-10.patch708 bytes

Rerolled.

  • DamienMcKenna committed ebf89b2 on 7.x-1.x authored by schifazl 
    Issue #2802533 by schifazl, DamienMcKenna, ann b, DigitalFrontiersMedia...
DamienMcKenna’s picture

DamienMcKenna
Location NH, USA
CreditAttribution: DamienMcKenna at Mediacurrent commented
Status: Reviewed & tested by the community » Fixed

Committed. Thanks.

Status: Fixed » Closed (fixed)

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