7 Replies - 748 Views - Last Post: 19 February 2014 - 11:31 AM Rate Topic: -----

#1 cbundgaard   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 5
  • Joined: 19-February 14

WP query filters - help to make it right

Posted 19 February 2014 - 08:22 AM

Hi. I have a problem with a custom WP query on my site.

I have this code:

function joinPostmeta($join) {
    global $wp_query, $wpdb;
    $join .= "LEFT JOIN $wpdb->usermeta umeta ON $wpdb->posts.post_author = umeta.user_id";
    return $join;
}
function wherePostmeta($where) {
    global $wp_query, $wpdb;
    $where .= "AND umeta.meta_key = 'wp-last-login'";
    return $where;
}
function orderbyPostmeta($orderby_statement) {
    global $wp_query, $wpdb;
    $orderby_statement .= "umeta.meta_value DESC";
    return $orderby_statement;
}

add_filter('posts_join', 'joinPostmeta');
add_filter('posts_where', 'wherePostmeta');
add_filter('posts_orderby', 'orderbyPostmeta');

$paged = (get_query_var('paged')) ? get_query_var('paged') : 1;
$args = array(
  'posts_per_page' => 5,
      'paged' => $paged,
  'offset' => 0,
  'orderby' => 'post_date',
  'order' => 'DESC',
  'post_type' => 'post',
  'post_status' => 'publish',
  'suppress_filters' => false 
);
$the_query = new WP_Query($args);


But when I run it, it gives this result in Debug Bar. It's clear that something is wrong in the query, but I don't know how to make it right.

SELECT SQL_CALC_FOUND_ROWS wp_posts.ID 
FROM wp_posts 
INNER JOIN wp_term_relationships 
ON (wp_posts.ID = wp_term_relationships.object_id)
LEFT JOIN wp_usermeta umeta ON wp_posts.post_author = umeta.user_id 
WHERE 1=1 
AND ( wp_term_relationships.term_taxonomy_id IN (3) ) 
AND wp_posts.post_type = 'post' AND (wp_posts.post_status = 'publish')
AND umeta.meta_key = 'wp-last-login' 
GROUP BY wp_posts.ID ORDER BY wp_posts.post_date DESCumeta.meta_value DESC' 
LIMIT 0, 5


What i want to achieve is something that does the same as this query:

SELECT wposts.* 
FROM $wpdb->posts wposts, $wpdb->usermeta umeta
WHERE wposts.post_author = umeta.user_id
AND umeta.meta_key = 'wp-last-login' 
AND wposts.post_type = 'post' 
AND wposts.post_status = 'publish'
ORDER BY umeta.meta_value DESC


Can anybody help me?

Is This A Good Question/Topic? 0
  • +

Replies To: WP query filters - help to make it right

#2 ArtificialSoldier   User is offline

  • D.I.C Lover
  • member icon

Reputation: 2768
  • View blog
  • Posts: 8,077
  • Joined: 15-January 14

Re: WP query filters - help to make it right

Posted 19 February 2014 - 09:15 AM

Is the only problem that you're missing a space in the order by clause? What exactly do you want to change though? What do you think the second query you showed is doing? It looks like it's getting everything from the posts table and joining with the usermeta table on a couple conditions, and ordering by the last login date of the author. What exactly are you trying to accomplish with your first query?
Was This Post Helpful? 0
  • +
  • -

#3 cbundgaard   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 5
  • Joined: 19-February 14

Re: WP query filters - help to make it right

Posted 19 February 2014 - 09:18 AM

Hi

Sorry if I wasn't very clear.

I want to get all the active (= published) posts ordered by the authors last login date. So the user who logged in most recently will have his post at the top.

Does that help? :-) I have added the space, it just missed out in the query. It's not the problem.
Was This Post Helpful? 0
  • +
  • -

#4 ArtificialSoldier   User is offline

  • D.I.C Lover
  • member icon

Reputation: 2768
  • View blog
  • Posts: 8,077
  • Joined: 15-January 14

Re: WP query filters - help to make it right

Posted 19 February 2014 - 09:22 AM

I don't see where you would configure the select part, but right now it's only selecting the post IDs. Maybe that's fine, maybe you send the post IDs to other functions within Wordpress, or maybe you need to find a way to select everything instead of only the IDs. If you want to order by something other than the post date, it looks like you specify which column you want to order by in the $args array, I don't think you need a callback filter for that.
Was This Post Helpful? 0
  • +
  • -

#5 cbundgaard   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 5
  • Joined: 19-February 14

Re: WP query filters - help to make it right

Posted 19 February 2014 - 09:29 AM

First of all I don't know why the query says it's only the ID that it gets. It should get it all, and I can output it all in a loop. I haven't written anymore code than that. It's supposed to be a custom query, but there is a main loop on the page from somewhere in the template that gets the recent posts, but I'm just trying to ignore that.

I'm not ordering by post date - that would be easy to do because it's in the same table. I'm using the authors ID to retrive the "wp-last-login" meta-value from the usermeta table.

I just don't know how to fix the query with the filters so it does the same as the custom mysql query that works well.
Was This Post Helpful? 0
  • +
  • -

#6 ArtificialSoldier   User is offline

  • D.I.C Lover
  • member icon

Reputation: 2768
  • View blog
  • Posts: 8,077
  • Joined: 15-January 14

Re: WP query filters - help to make it right

Posted 19 February 2014 - 09:39 AM

Right now you're telling it to order by post_date:

  'orderby' => 'post_date',
  'order' => 'DESC',



I'm not very familiar with Wordpress, so I'm not sure if you can change that or if it's automatically going to prepend the table name, or if you leave that blank and then handle the order by clause in the filter callback. That's where I would focus though, change that around to see what happens.
Was This Post Helpful? 0
  • +
  • -

#7 cbundgaard   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 5
  • Joined: 19-February 14

Re: WP query filters - help to make it right

Posted 19 February 2014 - 09:46 AM

Oh that's a mistake. Thanks, it's corrected - but it is default value so it doesn't change anything though.
I just found out that the query from the debug bar that I inserted aint right - it was something else, but I can't seem to find the query I'm making in the debug bar (I just set SAVEQUERIES to true, so it shouldn't be that)

I appreciate your help, but I think it's required to be quite familiar with WP (which I'm not either). But look away from the debug bar-query and just try to make it work.
Was This Post Helpful? 0
  • +
  • -

#8 cbundgaard   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 5
  • Joined: 19-February 14

Re: WP query filters - help to make it right

Posted 19 February 2014 - 11:31 AM

Hi again. I found a solution with the "wanted SQL query" and some custom PHP/SQL code. Thanks.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1