Wordpress custom $wpdb query order by custom field

0
votes

I am using advanced custom fields wordpress plugin and a custom post type called "albums".
In the custom post type I have 2 custom fields.
One is a "user"-type-field called "album_artists" which stores an array with user ids into the database.
The other one is a field called "release_date" which stores a timestamp into the database and is a required field.

Now, in authors.php I get all "album"-posts that contain the current author's id in the "album_artists" field. So far so good. I managed this with a custom query which looks for \"{userID}\" in the serialized array of the "album_artists"-field and I am able to get the posts that I want. But now I want to order the albums-posts (which are currently ordered by post date) by the other custom field's ("release_date") value with the timestamp in it.

Is it possible with a single SQL query or is there any other idea to achieve this? Here's my custom query:

global $wpdb;
$results = $wpdb->get_results("
        SELECT * FROM $wpdb->posts WHERE `ID` IN (
            SELECT `post_id`
            FROM $wpdb->postmeta
            WHERE `meta_key` LIKE 'album_artists'
            AND `meta_value` LIKE '%\"{$curauth->ID}\"%'
        )
        AND `post_type` LIKE 'albums'
        AND `post_status` LIKE 'publish'
        ORDER BY 'post_date' ASC
", OBJECT);
2
phpmysqlwordpress
i believe you need to extract data, serialize and then sort . - arkoak
yeah but this can't be done with SQL alone right? So I'd have to pull out ALL albums which will cause a huge query. But I actually don't think I need to unserialize first because in the database the array looks like this: a:2:{i:0;s:1:"6";i:1;s:1:"3";} (width the ID in quotes) That's the reason why I look for \"{$curauth->ID}\" (width quotes). I think this is safe, isn't it? - Jonathan Holler
you should make sure that irrelevant data must not be formatted as the same pattern when using pattern matching approach - arkoak
Yeah I understand but I think I can use it safely. Thank for the advice. I know it's not the most beautiful way, but well it works - Jonathan Holler

2 Answers

0
votes

I suggest you to use WP_Query with meta key/values to do this. Here is my code, just test it.

$args = array(
'post_type' => 'album',
'orderby' => 'meta_value',
'meta_query' => array(array('key' => 'release_date')),
'order' => 'ASC',
'paged'=>$paged,
'meta_query' => array(
    array(
        'key'     => 'album_artists',
        'value'   => $curauth->ID,
        'compare' => 'LIKE',
    ),
),
);

You can use 'order' => 'ASC', to 'order' => 'DESC', change the order of sorting

0
votes

You can use joins for order by i have joined postmeta again to get the meta_value i.e date for meta_key release_date since meta_value type is text you need to cast your string date value so that it becomes MySQL's date object and can be used in ordering results, in STR_TO_DATE provide your current string date format like if date is stored as 04/26/2015 then format will be '%m/%d/%Y'

SELECT DISTINCT p.*
FROM $wpdb->posts p
JOIN $wpdb->postmeta m ON p.`ID` = m.post_id
JOIN $wpdb->postmeta m1 ON p.`ID` = m1.post_id
WHERE m.`meta_key` LIKE 'album_artists'
AND m.`meta_value` LIKE '%\"{$curauth->ID}\"%'
AND m1.`meta_key` LIKE 'release_date'
AND p.`post_type` LIKE 'albums'
AND p.`post_status` LIKE 'publish'
ORDER BY STR_TO_DATE(m1.meta_value, '%m/%d/%Y') ASC