Make WordPress Core

Opened 14 years ago

Last modified 5 years ago

#15861 new defect (bug)

Sorting users by post count

Reported by: scribu's profile scribu Owned by:
Milestone: 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 13 years ago.

Download all attachments as: .zip

Change History (16)

#1 @scribu
14 years ago

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

#2 @scribu
14 years ago

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

#3 @scribu
13 years ago

  • Keywords needs-patch added; 3.2-early removed

#4 @sirzooro
13 years ago

  • Cc sirzooro added

#5 @sirzooro
13 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
13 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
13 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
13 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
13 years ago

  • Keywords has-patch added; needs-patch removed

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

Replying to SergeyBiryukov:

How exactly can we move users table to a separate database?

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.

Last edited 13 years ago by scribu (previous) (diff)

#11 follow-up: @scribu
13 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
11 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
11 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
9 years ago

  • Keywords needs-refresh added; has-patch removed

#15 @SergeyBiryukov
9 years 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.