Ask your WordPress questions! Pay money and get answers fast! (more info)

Sort WP Query by both positive and negative values WordPress

  • SOLVED

Hi!

I have a problem with a query that I need some help with.

I'm sorting posts by votes, with the help of a value in the meta field _point.

The problem is that the values can be positive (1,3 5 for example) if the post has upvotes, but might be negative if the post has negative values (-1, -4, -6 for example).

I have a problem sorting it correctly.

Instead of sorting like this:

4
2
1
0
-1
-2
-4

It sorts like this:

4
2
-2
1
-1
0

This is my args for the query:

<?php
// WP_Query arguments
$args = array (
'parent' => $post->ID,
'posts_per_page' => '-1',
'post_type' => 'user_images',
'post_status' => 'publish',
'meta_query' => array(
'relation' => 'OR',
array(
'key' => '_point',
'compare' => 'NOT EXISTS',
),
array(
'key' => '_point',
'compare' => 'EXISTS',
),
),
'orderby' => array('meta_value_num' => 'DESC', 'meta_value' => 'DESC', 'date' => 'ASC'),
);


Thanks in advance!

Answers (2)

2015-09-29

Andrea P answers:

at a first look I'd say it could be because you've added an unnecessary orderby.

try to take off this:

'meta_value' => 'DESC',

you need only the 'meta_value_num', the other one is messing and also ordering as if they weren't numbers


Jens Filipsson comments:

Yes, that one was a mistake when posting it here. I tried it, but it doesn't work without it either.


Andrea P comments:

mmm..

can I ask you why are you checking for _points to be existent OR to be not existent?


Andrea P comments:

also, what happens if you take off the ordering by date as well?


Jens Filipsson comments:

No change unfortunately


Andrea P comments:

sorry, again, why are you making 2 meta queries which just check if _point exists or not? that meta query will simply always give "true"..

can't you do something like this instead:

// WP_Query arguments

$args = array (
'parent' => $post->ID,
'posts_per_page' => '-1',
'post_type' => 'user_images',
'post_status' => 'publish',
'meta_key' => '_point',
'orderby' => 'meta_value_num',
'order' => 'DESC'

);


Jens Filipsson comments:

Because if no votes (meaning _point is empty), then it wont show up.


Andrea P comments:

try this then:


<?php

// WP_Query arguments

$args = array (
'parent' => $post->ID,
'posts_per_page' => '-1',
'post_type' => 'user_images',
'post_status' => 'publish',
'meta_key' => '_point',
'orderby' => 'meta_value_num',
'order' => 'DESC',
'meta_query' => array(
'relation' => 'OR',
array(
'key' => '_point',
'compare' => 'NOT EXISTS',
),
array(
'key' => '_point',
'compare' => 'EXISTS',
),
),

);


Jens Filipsson comments:

Then it excludes posts without votes unfortunately. I guess the date part has to be there as well maybe?


Andrea P comments:

what you choose to orderby, shouldn't influence what is fetched.

despite of the order, do you fetch what you need if you use this:


<?php

// WP_Query arguments

$args = array (
'parent' => $post->ID,
'posts_per_page' => '-1',
'post_type' => 'user_images',
'post_status' => 'publish',
'meta_query' => array(
'relation' => 'OR',
array(
'key' => '_point',
'compare' => 'NOT EXISTS',
),
array(
'key' => '_point',
'compare' => 'EXISTS',
),
),

);


Jens Filipsson comments:

You're probably right. I just realized: When field is empty, we cant order by this value, because there are no value. Hmm. That was stupid of me...


Jens Filipsson comments:

Is it possible to add something like this:

if NULL (no votes) add 0 to sorting?


Andrea P comments:

if the previous query is fetching the posts that you need, the query below should order them properly, based on the points, where the post actually has points


<?php

// WP_Query arguments

$args = array (
'parent' => $post->ID,
'posts_per_page' => '-1',
'post_type' => 'user_images',
'post_status' => 'publish',
'meta_query' => array(
'relation' => 'OR',
'no_point_clause' => array(
'key' => '_point',
'compare' => 'NOT EXISTS',
),
'points_clause' => array(
'key' => '_point',
'type' => 'NUMERIC',
'compare' => 'EXISTS',
),
),
'orderby' => 'points_clause',
'order' => 'DESC',

);


Jens Filipsson comments:

This didn't work, but shouldn't no_point_clause also be somewhere?


Andrea P comments:

I think if NULL, it should place them all after the last post with a value for points

so like

4
3
2
1
0
-1
-2
-3
-4
NULL

and no, you can provide a value for the field while you are in the query..
you should do that with the code which creates the custom field, so that it tell it to put 0 as default for posts which haven't got a value set for that custom field.
how is that field created?


Jens Filipsson comments:

Yes, I started to think about that, will take a look at the code. Was a while ago ;)


Andrea P comments:

if _point don't exists, then you can't order by it, so the no_points_clause doesn't make sense as a parameter for orderby..

anyway, what do you mean "it didn't work? what happened?


Jens Filipsson comments:

Everything became random. But now I'm setting up a default value of "0" when publishing a post. This will hopefully sort it out.


Andrea P comments:

ah ok, so if then all the posts will have a value for _point, you don't need the meta queries and you should be able to use these args:


$args = array (
'parent' => $post->ID,
'posts_per_page' => '-1',
'post_type' => 'user_images',
'post_status' => 'publish',
'meta_key' => '_point',
'orderby' => 'meta_value_num',
'order' => 'DESC'
);


Jens Filipsson comments:

Great! Once again I find an answer by talking to you. I like you.


Andrea P comments:

ahah, perfect! glad to be helpful! :)

2015-09-29

dimadin answers:

Have you tried to use

'meta_type' => 'NUMERIC'

in your arguments?


Jens Filipsson comments:

Have tried it, but unfortunately without luck


dimadin comments:

Might be missing

'meta_key' => 'key'


dimadin comments:

BTW, I don't understand your 'meta_query', isn't this always true?


Jens Filipsson comments:

If the post have no votes, then there are no number there.


Jens Filipsson comments:

Key is in the meta_query


dimadin comments:

I am not sure can keys from 'meta_query' be used when ordering via 'meta_value' or 'meta_value_num'.

If nothing works, you might need to queries. First one should be like this, but without ordering and with

'fields' => 'ids'

Then use those IDs in 'post__in' of second query and 'meta_key', 'meta_type' and 'orderby' => 'meta_value'.


Jens Filipsson comments:

Not really sure how you mean, can you provide an example?


dimadin comments:

This is a quick, untested:

$first_args = array (
'parent' => $post->ID,
'posts_per_page' => '-1',
'post_type' => 'user_images',
'post_status' => 'publish',
'fields' => 'ids',
'meta_query' => array(
'relation' => 'OR',
array(
'key' => '_point',
'compare' => 'NOT EXISTS',
),
array(
'key' => '_point',
'compare' => 'EXISTS',
),
),
);
$first_ids = get_posts( $first_args );

$args = array (
'parent' => $post->ID,
'posts_per_page' => '-1',
'post_type' => 'user_images',
'post_status' => 'publish',
'meta_key' => '_point',
'meta_type' => 'NUMERIC',
'orderby' => 'meta_value',
);


Jens Filipsson comments:

Unfortunately, posts without votes was excluded. But shouldn't $first_ids be part of the second query?


dimadin comments:

Sorry, yes, just add to this to $args 'post__id' =>$first_ids


Jens Filipsson comments:

Ok, this is not working. But I just realized: When field is empty, we cant order by this value, because there are no value. Hmm. That was stupid of me...


Jens Filipsson comments:

Is it possible to add something like this:

if NULL (no votes) add 0 to sorting?