OiO.lk Community platform!

Oio.lk is an excellent forum for developers, providing a wide range of resources, discussions, and support for those in the developer community. Join oio.lk today to connect with like-minded professionals, share insights, and stay updated on the latest trends and technologies in the development field.
  You need to log in or register to access the solved answers to this problem.
  • You have reached the maximum number of guest views allowed
  • Please register below to remove this limitation

I need mysql query for wordpress for get ordered list

  • Thread starter Thread starter Y. Sidorkin
  • Start date Start date
Y

Y. Sidorkin

Guest
I need to get ordered list by three fields.

Code:
$sql = "
SELECT 
    SQL_CALC_FOUND_ROWS $wpdb->posts.ID 
FROM 
    $wpdb->posts 
INNER JOIN 
    $wpdb->postmeta ON ( $wpdb->posts.ID = $wpdb->postmeta.post_id )
INNER JOIN 
    $wpdb->postmeta AS mt1 ON ( $wpdb->posts.ID = mt1.post_id ) 

WHERE 
    1=1

AND 
( 
   ( mt1.meta_key = 'pub_series' AND mt1.meta_value = 'Book' ) 
OR ( mt1.meta_key = 'pub_series' AND mt1.meta_value = 'Book chapter' ) 
OR ( mt1.meta_key = 'pub_series' AND mt1.meta_value = 'Journal Article' )
)

/*AND 
( 
   mt3.meta_key = 'forthcoming'
)*/

AND 
    $wpdb->postmeta.meta_key = 'pub_year'        
AND 
    $wpdb->posts.post_type = 'publication' 
AND 
    $wpdb->posts.post_status = 'publish' 
GROUP BY 
    $wpdb->posts.ID 

ORDER BY 

    /*FIELD(mt3.meta_key, 'forthcoming') DESC,
    FIELD(mt3.meta_value, 'null') DESC,*/
    //FIELD($wpdb->postmeta.meta_value,1),

    $wpdb->postmeta.meta_value DESC, 
    $wpdb->posts.post_date DESC 
";

$total = count($wpdb->get_results($sql));
$offset = ( $paged * $posts_per_page ) - $posts_per_page;

$results = $wpdb->get_results( $sql . "
    LIMIT 
        $offset, $posts_per_page" );

This is query ordered list by two fields: [custom_field] pub_year (possible values 2018, 2017...) and [delautl wp post date field] post_date. It is ok.

But now I need to show items with [custom_field] forthcoming = 1. Problem that there three states if this field: - items with forthcoming = 1 - items with forthcoming = 0 - and items where custom field forthcoming not exist

I need to get ordered list with firts items forthcoming = 1 and other should be ordered by pub_year and date. How I can do this. Tell me if you need some more info. Thanks a lot.
<p>I need to get ordered list by three fields.</p>

<pre><code>$sql = "
SELECT
SQL_CALC_FOUND_ROWS $wpdb->posts.ID
FROM
$wpdb->posts
INNER JOIN
$wpdb->postmeta ON ( $wpdb->posts.ID = $wpdb->postmeta.post_id )
INNER JOIN
$wpdb->postmeta AS mt1 ON ( $wpdb->posts.ID = mt1.post_id )

WHERE
1=1

AND
(
( mt1.meta_key = 'pub_series' AND mt1.meta_value = 'Book' )
OR ( mt1.meta_key = 'pub_series' AND mt1.meta_value = 'Book chapter' )
OR ( mt1.meta_key = 'pub_series' AND mt1.meta_value = 'Journal Article' )
)

/*AND
(
mt3.meta_key = 'forthcoming'
)*/

AND
$wpdb->postmeta.meta_key = 'pub_year'
AND
$wpdb->posts.post_type = 'publication'
AND
$wpdb->posts.post_status = 'publish'
GROUP BY
$wpdb->posts.ID

ORDER BY

/*FIELD(mt3.meta_key, 'forthcoming') DESC,
FIELD(mt3.meta_value, 'null') DESC,*/
//FIELD($wpdb->postmeta.meta_value,1),

$wpdb->postmeta.meta_value DESC,
$wpdb->posts.post_date DESC
";

$total = count($wpdb->get_results($sql));
$offset = ( $paged * $posts_per_page ) - $posts_per_page;

$results = $wpdb->get_results( $sql . "
LIMIT
$offset, $posts_per_page" );
</code></pre>

<p>This is query ordered list by two fields: [custom_field] pub_year (possible values 2018, 2017...) and [delautl wp post date field] post_date. It is ok.</p>

<p>But now I need to show items with [custom_field] forthcoming = 1. Problem that there three states if this field:
- items with forthcoming = 1
- items with forthcoming = 0
- and items where custom field forthcoming not exist</p>

<p>I need to get ordered list with firts items forthcoming = 1 and other should be ordered by pub_year and date. How I can do this. Tell me if you need some more info. Thanks a lot.</p>
Continue reading...
 

Latest posts

Top