Filter by multiple custom fields in the loop

  • jayhewitt

    (@jayhewitt)


    13 years, 4 months ago

    Hi,

    Does anyone know how to filter the loop using multiple custom fields? below is the code I’m currently using to filter by one custom field:

    function filter_join($join) {
global $wpdb;
$join .= " JOIN wp_postmeta ON " .
$wpdb->posts . ".ID = wp_postmeta.post_id ";
return $join;
}
add_filter('posts_join', 'filter_join');

function filter_where($where) {
$where .= " AND (wp_postmeta.meta_key = 'gender' AND wp_postmeta.meta_value = 'male') ";
return $where;
}
add_filter('posts_where', 'filter_where');
query_posts($query_string);

    I’ve tried adding the others by just using an AND, but this doesn’t work, I assume because this will be joining only to on row in the wp_postmeta table.

    Thanks!

Viewing 3 replies - 1 through 3 (of 3 total)
  • vtxyzzy

    (@vtxyzzy)

    13 years, 4 months ago

    You are correct in thinking that you are joining to only one row in the wp_postmeta table. You must join to the wp_postmeta table twice (this is UNTESTED – watch for typos):

    function filter_join($join) {
   global $wpdb;
   $join .= " JOIN $wpdb->postmeta pm1 ON ($wpdb->posts.ID = pm1.post_id)
   JOIN $wpdb->postmeta pm2 ON ($wpdb->posts.ID = pm2.post_id)";
   return $join;
}

function filter_where($where) {
   $where .= " AND pm1.meta_key = 'gender' AND pm1.meta_value = 'male'
               AND pm2.meta_key = '???'  AND pm2.meta_value = '???'";
   return $where;
}
    Thread Starter jayhewitt

    (@jayhewitt)

    13 years, 4 months ago

    Thanks!

    I didn’t realise you could link to the same table more than once, although seeing this example, giving it a different alias when you relink, it seems obvious!

    Is there a maximum number of times you can/should do this?

    Thanks!

    vtxyzzy

    (@vtxyzzy)

    13 years, 4 months ago

    MySql probably imposes a limit, but I do not know what it is. I am more familiar with Sybase which (used to, at least) limit you to 16 total tables in a query. EDIT: MySql limits you to 61 JOINS in a query.

    However, you will probably run into severe performance problems before you get anywhere near the limit. Again, Sybase used to quit trying to optimize a query at 5 tables so performance dropped dramatically at that point.

Viewing 3 replies - 1 through 3 (of 3 total)
  • The topic ‘Filter by multiple custom fields in the loop’ is closed to new replies.