Need help from an MySQL Master for Custom Ordering

  • Hi all,

    I wrote a plugin to do sorting/ordering of posts by Custom Fields. My original attempt was to add a filter to the_posts filter, then sort it based on multiple queries to the db. It was very very inefficient, but had a bigger problem – the_posts returns posts after pagination, so it was sorting on each page seperately.

    So I’ve decided that the only way to do this is to use the
    posts_orderby
    filter…but I don’t know how to formulate the query because I need to order by data from another table, wp_meta, and while it looks like ORDER BY can be used with user supplied procedures I’m not sure of the best way to go about this.

    Any pointers to get me in the right direction ?

    Thanks in advance!

Viewing 1 replies (of 1 total)
  • Thread Starter master_kato

    (@master_kato)

    Ok, I figured this out. I need to use two filters
    posts_orderby
    posts_join

    So I join the metadata table, and use aliases so I can join it multiple times for each field. The problem is that once I add the second LEFT JOIN, my db performance goes through the roof (from 0.5 seconds, to 30 seconds). Turns out MySQL doesn’t like multiple JOINS that much. Anyone have any ideas on how I can optimize this? Here’s a stripped down example:

    SELECT DISTINCT * FROM wp_posts
    LEFT JOIN wp_postmeta AS Field1 ON wp_posts.ID = Field1.post_id AND Field1.meta_key = ‘Field 1’
    LEFT JOIN wp_postmeta AS Field2 ON wp_posts.ID = Field2.post_id AND Field2.meta_key = ‘Field 2’

    Then this is followed by
    ORDER BY Field1.meta_value ASC, Field2.meta_value ASC

    and so on for however many custom fields you want to sort by. I thought I could combine the multiple JOIN clauses but I don’t know if it’s possible while using alises and JOINS to the same table.

    ?

Viewing 1 replies (of 1 total)
  • The topic ‘Need help from an MySQL Master for Custom Ordering’ is closed to new replies.