Ordering posts by Custom Field

I’ve searched some more and changed my query a bit. it’s now:

<?php

$wpdb->flush();
 $querystr = "
SELECT * FROM $wpdb->posts
LEFT JOIN $wpdb->postmeta AS eventdate1 ON(
$wpdb->posts.ID = eventdate1.post_id
AND eventdate1.meta_key = 'eventdate1'
)
LEFT JOIN $wpdb->term_relationships ON($wpdb->posts.ID = $wpdb->term_relationships.object_id)
LEFT JOIN $wpdb->term_taxonomy ON($wpdb->term_relationships.term_taxonomy_id = $wpdb->term_taxonomy.term_taxonomy_id)
LEFT JOIN $wpdb->terms ON($wpdb->term_taxonomy.term_id = $wpdb->terms.term_id)
WHERE $wpdb->terms.name = '$cat1'
AND $wpdb->term_taxonomy.taxonomy = 'category'
AND $wpdb->posts.post_status = 'publish'
ORDER BY eventdate1.meta_value ASC
 ";
 $pageposts = $wpdb->get_results($querystr, OBJECT);

?>

This has partially solved my problem. it’s sorting by the correct custom field, however it is sorting the numbers wrong. For example, the order it produces is:

1,11,12,13…2,21,22,23,24,25…

It seems to be sorting by the first number and not recognizing the number as a number. Any ideas on how to fix this fix this? is it a bug?

I have a similar issue. It seems all custom field values are strings. If there were some way to cast or convert the custom fields value as a number (float in my case, int in yours) the the order by would work.
I am trying to order by coin denominations. Values are:
.01
.05
.10
.25
.50
1.00
2.00
2.50
3.00
5.00
10.00
20.00

Order by below sorts as follows (similar to your dates):
.01
.05
.10
.25
.50
1.00
10.00
2.00
2.50
20.00
3.00
5.00

Obviously, the 10.00 and 20.00 are out of order. Is there a MySQL/SQL/WP guru who can help with this?

SELECT * FROM $wpdb->posts
	LEFT JOIN $wpdb->postmeta AS coindenom ON(
	$wpdb->posts.ID = coindenom.post_id
	AND coindenom.meta_key = 'coin-denom'
	)
	LEFT JOIN $wpdb->postmeta AS coindate ON(
	$wpdb->posts.ID = coindate.post_id
	AND coindate.meta_key = 'coin-date'
	)
	LEFT JOIN $wpdb->postmeta AS coinprice ON(
	$wpdb->posts.ID = coinprice.post_id
	AND coinprice.meta_key = 'coin-price'
	)
	LEFT JOIN $wpdb->term_relationships ON($wpdb->posts.ID = $wpdb->term_relationships.object_id)
	LEFT JOIN $wpdb->term_taxonomy ON($wpdb->term_relationships.term_taxonomy_id = $wpdb->term_taxonomy.term_taxonomy_id)
	WHERE $wpdb->term_taxonomy.term_id = 4
	AND $wpdb->term_taxonomy.taxonomy = 'category'
	AND $wpdb->posts.post_status = 'publish'
	ORDER BY coindenom.meta_value, coindate.meta_value, coinprice.meta_value ASC

Calling all Guru’s. ??

Yow!

One reason I added a database table to my plugin. More than one custom field means big time query headaches.

For sorting discrete items, this example snippet of SQL might help:

ORDER BY
FIELD(wposts.post_status,'publish','draft'),
FIELD(listings.status,1,4,2,5,3,6)

Note: no spaces between FIELD and (

or for simpler numeric sorts, you can try some fake math to force conversion

ORDER BY ABS(0 + coindenom.meta_value) ASC

And I should mention it’s really not that difficult to add a table to your plugin. It will make life easier.

Like those above, I’m trying to order by a numerical value (a price). I followed Roger’s advice and used ORDER BY ABS(0 + $wpdb->postmeta.meta_key) ASC

It seemed to work great, but every now and then a single entry will get ordered out of place. I can’t seem to figure out why, but with each of my lists, a single entry doesn’t seem to want to be placed in order.

Not sure what to do about it…

Thanks!

I, too, am trying to order posts on a category page based on a custom field.
What I would like is:
Get all posts for the category. If the custom field with a key of phs-post-order is assigned, order first by that key’s value, then by date.
(The only custom field I have is ‘phs-post-order’)

However, when I remove the where statement restricting the query to only posts with
AND $wpdb->postmeta.meta_key = ‘phs-post-order’
then I get redundant listings of posts in the page.

I’d appreciate any help on the SQL.
This is what I’m using:

$querystr = “
SELECT * FROM $wpdb->posts
LEFT JOIN $wpdb->postmeta ON($wpdb->posts.ID = $wpdb->postmeta.post_id)
LEFT JOIN $wpdb->term_relationships ON($wpdb->posts.ID = $wpdb->term_relationships.object_id)
LEFT JOIN $wpdb->term_taxonomy ON($wpdb->term_relationships.term_taxonomy_id = $wpdb->term_taxonomy.term_taxonomy_id)
WHERE $wpdb->term_taxonomy.term_id = 8
AND $wpdb->term_taxonomy.taxonomy = ‘category’
AND $wpdb->posts.post_status = ‘publish’
AND $wpdb->postmeta.meta_key = ‘phs-post-order’
ORDER BY ABS(0 + $wpdb->postmeta.meta_value) ASC
“;

Thanks!

you all need this

*CAST*

i.e if ‘price’ is the name of the custom field

SELECT *,CAST($wpdb->postmeta.meta_value as DECIMAL) as price FROM $wpdb->posts

Ok, Me too and I’m TOTALLY green at this SQL stuff -never used it – it all looks like Greek to me.

I want to create a rating system from 1 to 5 in a custom field called “Rating”, and sort from highest to lowest rating.

Anyone want to give me some advice on this?

Thankyou!!

This post has been really useful to me in putting together a much more complicated search which takes on multiple query variables.

// Search build

					// Put form input into values
				  $venue_search_value = $_REQUEST['venue'] ;
				  $date_search_value = $_REQUEST['date'] ;
				  $keyword_search_value = $_REQUEST['keywords'] ;
				  $order_results_by_value = $_REQUEST['orderby'] ;

				// If not a search do nothing
				if ( $venue_search_value == '' && $date_search_value == '' && $keyword_search_value == '' ) {
					} else {

					// Start building query string

		if($venue_search_value == '') {
			$venue_search_query = '' ;
		} else {
			$venue_search_query = "AND venue.meta_value = '$venue_search_value'" ;
		}

		if($date_search_value == '') {
			$date_search_query = '' ;
		} else {
			$date_search_query = "AND starttime.meta_value LIKE '$date_search_value%' " ;
		}

// todo: find a way to search post title and post meta by keyword.
		if($keyword_search_value == '') {
			$keyword_search_query = '' ;
		} else {
			$keyword_search_query = "AND $wpdb->posts.post_content  LIKE '%$keyword_search_value%'
				OR $wpdb->posts.post_title LIKE '%$keyword_search_value%'
				OR $wpdb->postmeta.meta_value LIKE '%$keyword_search_value%' " ;
			" ;
		}

		if ($order_results_by_value == 'date') {
			$order_search_query = "starttime.meta_value" ;
		} elseif ($order_results_by_value == 'title') {
			$order_search_query = "$wpdb->posts.post_title" ;
		} else {
			$order_search_query = "starttime.meta_value" ;
		}

						// Query the database for the posts
						$query_string = "
						SELECT *
						FROM $wpdb->posts
						LEFT JOIN $wpdb->postmeta ON ($wpdb->posts.ID = $wpdb->postmeta.post_id)
						LEFT JOIN $wpdb->postmeta AS starttime ON ($wpdb->posts.ID = starttime.post_id AND starttime.meta_key = 'Start time')
						LEFT JOIN $wpdb->postmeta AS venue ON ($wpdb->posts.ID = venue.post_id AND venue.meta_key = 'Venue')
						WHERE $wpdb->posts.post_parent = $post->ID
						$venue_search_query
						$date_search_query
						$keyword_search_query
						AND $wpdb->posts.post_status = 'publish'
						AND $wpdb->posts.post_type = 'page'
						ORDER BY $order_search_query ASC
						";

You’ll have to forgive my awful PHP markup – I’m pretty new to this!

My main problem is with the $keyword_search_query variable. I need to search the Post Title, Post Content and all the Custom Fields Values for the user inputted $keyword_search_value .

However using the AND argument means all these SQL columns need to contain this argument (I only need one of them to contain it for SQL to return it as a result). Whereas using the OR argument in it’s place means all the other AND arguments previously given are all ignored.

Any help would be very much appreciated

Found a solution. I have a feeling there is probably a much more simple way to do this though. So any tips on cleaning up my horredous code would be welcomed!

// Search build

					// Put form input into values
				  $venue_search_value = $_REQUEST['venue'] ;
				  $date_search_value = $_REQUEST['date'] ;
				  $keyword_search_value = $_REQUEST['keywords'] ;
				  $order_results_by_value = $_REQUEST['orderby'] ;

				// If not a search do nothing
				if ( $venue_search_value == '' && $date_search_value == '' && $keyword_search_value == '' ) {
					} else {

					// Start building query string

		if($venue_search_value == '') {
			$venue_search_query = '' ;
		} else {
			$venue_search_query = "AND venue.meta_value = '$venue_search_value'" ;
		}

		if($date_search_value == '') {
			$date_search_query = '' ;
		} else {
			$date_search_query = "AND starttime.meta_value LIKE '$date_search_value%' " ;
		}

		if ($order_results_by_value == 'date') {
			$order_search_query = "starttime.meta_value" ;
		} elseif ($order_results_by_value == 'title') {
			$order_search_query = "$wpdb->posts.post_title" ;
		} else {
			$order_search_query = "starttime.meta_value" ;
		}

		// todo: find a way to search post title and post meta by keyword.
				if($keyword_search_value == '') {
					$keyword_search_query = "$wpdb->posts.post_parent = $post->ID
					$venue_search_query
					$date_search_query
					AND $wpdb->posts.post_status = 'publish'
					AND $wpdb->posts.post_type = 'page'
					" ;
				} else {
					$keyword_search_query = "
					$wpdb->posts.post_content  LIKE '%$keyword_search_value%'
					$venue_search_query
					$date_search_query
					AND $wpdb->posts.post_parent = $post->ID
					AND $wpdb->posts.post_status = 'publish'
					AND $wpdb->posts.post_type = 'page'
					OR $wpdb->posts.post_title LIKE '%$keyword_search_value%'
					$venue_search_query
					$date_search_query
					AND $wpdb->posts.post_parent = $post->ID
					AND $wpdb->posts.post_status = 'publish'
					AND $wpdb->posts.post_type = 'page'
					OR $wpdb->postmeta.meta_value LIKE '%$keyword_search_value%'
					$venue_search_query
					$date_search_query
					AND $wpdb->posts.post_parent = $post->ID
					AND $wpdb->posts.post_status = 'publish'
					AND $wpdb->posts.post_type = 'page' " ;
				}

						// Query the database for the posts
						$query_string = "
						SELECT *
						FROM $wpdb->posts
						LEFT JOIN $wpdb->postmeta ON ($wpdb->posts.ID = $wpdb->postmeta.post_id)
						LEFT JOIN $wpdb->postmeta AS starttime ON ($wpdb->posts.ID = starttime.post_id AND starttime.meta_key = 'Start time')
						LEFT JOIN $wpdb->postmeta AS venue ON ($wpdb->posts.ID = venue.post_id AND venue.meta_key = 'Venue')
						WHERE $keyword_search_query
						ORDER BY $order_search_query ASC
						";

You all could really use this, since no one answered you and the answer is too complex for me to explain, this article tells you how to modify the base query:

https://www.think-press.com/tips/order-posts-by-custom-key-revisited