WordPress.org

Make WordPress Core

Opened 5 years ago

Last modified 8 months ago

#15861 new defect (bug)

Sorting users by post count

Reported by: scribu Owned by:
Milestone: Future Release Priority: normal
Severity: normal Version:
Component: Users Keywords: dev-feedback needs-refresh
Focuses: administration Cc:

Description

Currently, to enable sorting by post count, there's a JOIN made between the users table and the posts table.

This is bad, because users is a global table, which might be stored in a separate database.

Short-term solution for 3.1 is to disable sorting.

Long-term solution is to avoid the JOIN somehow.

Attachments (1)

15861.patch (4.2 KB) - added by SergeyBiryukov 5 years ago.

Download all attachments as: .zip

Change History (16)

#1 @scribu
5 years ago

(In [17024]) Disable sorting by post count for now. See #15861

#2 @scribu
5 years ago

  • Keywords 3.2-early added
  • Milestone changed from 3.1 to Future Release

#3 @scribu
5 years ago

  • Keywords needs-patch added; 3.2-early removed

#4 @sirzooro
5 years ago

  • Cc sirzooro added

#5 @sirzooro
5 years ago

  • Keywords dev-feedback added

Looks that this ticket may stay here for a longer time. What about enabling sorting for single-db config now, and providing fix for single-db and multi-db later?

#6 @audiobahn
5 years ago

like sirzooro said, we could do something now and fix it later. Change sorting to a post-query operation and sort manually. Later on, a patch could be written to replace the post-query operation with something in the query. Then it would always work multi-db.

#7 @nacin
5 years ago

We could potentially do select post_author from $wpdb->posts where post_type = 'post' and post_status = 'publish' group by post_author order by count(ID) desc, then use that data to sort in PHP.

#8 in reply to: ↑ description ; follow-up: @SergeyBiryukov
5 years ago

Replying to nacin:

We could potentially do select post_author from $wpdb->posts where post_type = 'post' and post_status = 'publish' group by post_author order by count(ID) desc, then use that data to sort in PHP.

15861.patch is an attempt to implement this.

Turned out tricky to use proper LIMITs for paging, so I guess this patch is not scalable. However it should work when querying not only user IDs, but other fields too (in which case $this->results is an array of objects rather than numbers).

Replying to scribu:

users is a global table, which might be stored in a separate database.

How exactly can we move users table to a separate database? Perhaps we should check if it's in the same database and fall back to JOIN, since it's more simple.

#9 @edwardw
5 years ago

  • Keywords has-patch added; needs-patch removed

#10 in reply to: ↑ 8 @scribu
4 years ago

Replying to SergeyBiryukov:

How exactly can we move users table to a separate database? Perhaps we should check if it's in the same database and fall back to JOIN, since it's more simple.

But there are several ways you can make queries to a separate DB: create a new WP_DB instance or use SELECT * other_database.wp_users.* etc.

Also, the HyperDB plugin enables this.

Version 0, edited 4 years ago by scribu (next)

#11 follow-up: @scribu
4 years ago

So, it seems the "don't JOIN global tables with non-global tables" rule can be broken in the following case:

if ( !wp_is_large_network( 'users' ) && !defined( 'CUSTOM_USER_TABLE' ) && !file_exists( WP_CONTENT_DIR . '/db.php' ) )

#12 in reply to: ↑ 11 @nacin
2 years ago

  • Component changed from Administration to Users
  • Focuses administration added

Replying to scribu:

So, it seems the "don't JOIN global tables with non-global tables" rule can be broken in the following case:

if ( !wp_is_large_network( 'users' ) && !defined( 'CUSTOM_USER_TABLE' ) && !file_exists( WP_CONTENT_DIR . '/db.php' ) )

I'm OK with that.

#13 @jdgrimes
2 years ago

There were two lines of useless code left in WP_Users_List_Table::get_sortable_columns(). Probably should have been removed in [17024]. It isn't hurting anything, just confused me for a minute.

#14 @chriscct7
8 months ago

  • Keywords needs-refresh added; has-patch removed

#15 @SergeyBiryukov
8 months ago

In 35110:

Remove obsolete code from `WP_Users_List_Table::get_sortable_columns().

Sorting by post count was disabled in [17024].

Props jdgrimes.
See #15861.

Note: See TracTickets for help on using tickets.