CODEWITHSUNDEEP
phpmysqlsqlwordpress
Ayman Hussein
Ayman Hussein

Reputation: 3857

Order by custome value in wordpress

I Have this data:

id      meta_key   meta_value
1       d_t_w      4 weeks
2       d_t_w      2 Days
3       d_t_w      3 Weeks
4       d_t_w      4 Days
5       d_t_w      1 Week

WP query:

$query['orderby'] = 'd_t_w'; 
$query['order'] = ASC;  
$query = new WP_Query($query);

The Result:

  id      meta_key   meta_value
  5       d_t_w      1 Week 
  2       d_t_w      2 Days   
  3       d_t_w      3 Weeks  
  4       d_t_w      4 Days
  1       d_t_w      4 weeks

but what i expected:

id      meta_key   meta_value
2       d_t_w      2 Days
4       d_t_w      4 Days
5       d_t_w      1 Week
3       d_t_w      3 Weeks
1       d_t_w      4 weeks

How can apply order by on custom value like on (1 day, 2 days, 3 days, ..., 3 weeks, 4 weeks)

Upvotes: 0

Views: 85

Answers (5)

Ayman Hussein
Ayman Hussein

Reputation: 3857

I solved the issue by adding Filter:

add_filter( 'posts_orderby', 'deliver_to_weeks', 11, 2 );
    function deliver_to_weeks( $orderBy, $query ) {  

        $orderBy = str_ireplace('wp_postmeta.meta_value', "field(wp_postmeta.meta_value, '1 Day', '2 Days', '3 Days', '4 Days', '5 Days' , '1 Week', '2 Weeks', '3 Weeks', '4 Weeks')", $orderBy);
        return $orderBy;
    }

Upvotes: 0

Abhishek Sharma
Abhishek Sharma

Reputation: 6661

if in you table meta_value suffix are same like weeks,days you can use this approach 

SELECT meta_value,
REPLACE(
REPLACE(
REPLACE(REPLACE(meta_value,' weeks','00'),' week','00'),' Days','0'),' Day','0'
    ) as odr 
 FROM `product` 
 ORDER by CAST(odr AS UNSIGNED)

Upvotes: 1

Vivek Sharma
Vivek Sharma

Reputation: 59

You can query using normal date/time order_by ASC/DESC and then convert meta value into d_t_w.

Upvotes: 0

Atif Tariq
Atif Tariq

Reputation: 2772

Your query should be like this:

$args = array(
'orderby'   => 'meta_value',
'meta_key'  => 'meta_value',
'order'     => 'ASC',
);
$query = new WP_Query( $args );

Reference web-page: https://codex.wordpress.org/Class_Reference/WP_Query

Upvotes: 0

silly
silly

Reputation: 7887

in raw query language (wordpress indipendent), you can extract your field and calculate days of the string to order the query

like this

SELECT
    *
FROM (
    SELECT '1 Week' as c
    UNION ALL SELECT '2 Days'
    UNION ALL SELECT '3 Days'
    UNION ALL SELECT '3 Weeks'
    UNION ALL SELECT '1 Day'
    ) as t
ORDER BY TRIM(SUBSTR(t.c, 1, LOCATE(' ', t.c))) *
    IF(TRIM(SUBSTR(LOWER(t.c), LOCATE(' ', t.c))) = 'week', 7,
        IF(TRIM(SUBSTR(LOWER(t.c),LOCATE(' ', t.c))) = 'weeks', 7,
            IF(TRIM(SUBSTR(LOWER(t.c), LOCATE(' ', t.c))) = 'day', 1,
                IF(TRIM(SUBSTR(LOWER(t.c), LOCATE(' ', t.c))) = 'days', 1,
                    1)))) ASC;

Upvotes: 0

Related Questions