Warning: Please do not give out any FTP or ssh credentials to anyone, unless you trust them completely. Giving out login details is dangerous.

If the asker does not get an answer then they have 10 days to request a refund.

$15
Custom query sorted on two meta fields

Hi, guys. I have a custom post type with two meta fields: "city" and "price". I want to list all posts ordered first by city and then by price even if the city and price fields are not filled in.

This query is VERY close. It returns all posts if both city and price are filled in but doesn't honor the LEFT JOIN element and include posts even if they don't have a city and price value.



	SELECT 

		p.*

	FROM 

		$wpdb->posts p

	LEFT JOIN $wpdb->postmeta pm ON p.ID = pm.post_ID

		LEFT JOIN $wpdb->postmeta pm2 ON p.ID = pm2.post_ID

	WHERE 

		p.post_status = 'publish' AND 

		p.post_type = 'property' AND

		p.post_date < NOW() AND

		pm.meta_key = 'city' AND

		pm2.meta_key = 'price'

	ORDER BY

		pm.meta_value ASC,

		pm2.meta_value DESC,

		p.post_title ASC

What am I doing wrong?

Thanks!

Kevin McGillivray | 01/08/12 at 10:43pm Edit

(3) Possible Answers Submitted...

Note: Kevin McGillivray felt their question was unanswered, so we granted them a refund.

Note: Kevin McGillivray requested a refund. They offered this explanation:

"I solved this question myself. While I appreciate the two guys who posted suggestions, they're ideas weren't pertinent to the solving this particular question."

If no one challenges a refund request, then they are automatically granted and proccessed after 48 hours. Admins of this site only review refund requests if someone challenges the request. If you are curious about how we handled previous refund requests, you may read over all refund requests and their challenges.

See a chronological view of answers?

Warning: Please do not give out any FTP or ssh credentials to anyone, unless you trust them completely. Giving out login details is dangerous.

Last edited:
01/08/12
11:15pm
Arnav Joy says:
so what to do you want in case if there is no value in price and city meta field.
can you show it by giving an example or screenshot..
- 01/08/12 11:26pm
  
  Kevin McGillivray says:
  Well, in this example, it doesn't matter. I'm not returning the values for price and city in the query; I'm just using them for sorting. But if I added those fields to the query, I should get null values in city and/or price with the rest of the post data for each post that matches the rest of the criteria. At least that's what I *think* it should do.
  
  Here's a link to w3school's LEFT JOIN explanation and it shows an example:
  
  http://www.w3schools.com/sql/sql_join_left.asp
  
  Thanks!
  Kevin
- 01/08/12 11:50pm
  
  Arnav Joy says:
  try this
  
  SELECT
  
  p.*
  
  FROM
  
  $wpdb->posts p
  
  LEFT JOIN $wpdb->postmeta pm ON p.ID = pm.post_ID
  
  LEFT JOIN $wpdb->postmeta pm2 ON p.ID = pm2.post_ID
  
  WHERE
  
  p.post_status = 'publish' AND
  
  p.post_type = 'property' AND
  
  p.post_date < NOW() OR
  
  pm.meta_key = 'city' OR
  
  pm2.meta_key = 'price'
  
  ORDER BY
  
  pm.meta_value ASC,
  
  pm2.meta_value DESC,
  
  p.post_title ASC
- 01/09/12 12:25am
  
  Arnav Joy says:
  try it
  
  SELECT DISTINCT
  
  p.*
  
  FROM
  
  $wpdb->posts p
  
  LEFT JOIN $wpdb->postmeta pm ON p.ID = pm.post_ID
  
  LEFT JOIN $wpdb->postmeta pm2 ON p.ID = pm2.post_ID
  
  WHERE
  
  p.post_status = 'publish' AND
  
  p.post_type = 'property' AND
  
  p.post_date < NOW() OR
  
  pm.meta_key = 'city' OR
  
  pm2.meta_key = 'price'
  
  ORDER BY
  
  pm.meta_value ASC,
  
  pm2.meta_value DESC,
  
  p.post_title ASC
- 01/09/12 3:07am
  
  Kevin McGillivray says:
  Neither of your suggestions worked. I figured this out on my own and posted the correct code in another thread below. Thanks.

Last edited:
01/08/12
11:30pm

Kannan C says:

If i correctly understand, you need to use OR in your where condition.



WHERE 

		p.post_status = 'publish' AND 

		p.post_type = 'property' AND

		p.post_date < NOW() AND

		pm.meta_key = 'city' OR

		pm2.meta_key = 'price'

01/08/12 11:40pm

Kevin McGillivray says:
Sorry, Kannan. That's not the issue. I have this same problem even if I only try to join to post_meta once to deal with just, say, the price field. Thanks!
01/08/12 11:48pm

Kannan C says:
Can you detail, what are you trying to output, what's wrong in your output?

01/09/12 12:20am

Kannan C says:

FYI: if you want to list all posts without filtering by meta keys, simply use





WHERE 

p.post_status = 'publish' AND 

p.post_type = 'property'



ORDER BY

pm.meta_value ASC,

pm2.meta_value DESC,

p.post_title ASC

01/09/12 2:32am

Kevin McGillivray says:
The problem is that if either the city or price value for a particular post is empty, that post is left out of the results entirely when it should be included in the results.

Also, I'm not interested in listing all posts without filtering my meta keys. That's not related in any way to my question.

01/09/12 2:54am

Kannan C says:

Then you should create empty meta keys even it's value is not filled. You can use something like this



add_action('save_post', 'save_meta_details');	

function save_meta_details(){

	global $post;

	if ( defined( 'DOING_AUTOSAVE' ) && DOING_AUTOSAVE ) 

	return;

	$city= trim($_POST["city"]);

        $price= trim($_POST["price"]);

        update_post_meta($post->ID, "city", $city);

	update_post_meta($post->ID, "price", $price);

}

however, by doing so, all your posts of type 'property' can have the above meta keys, by which result will return all posts from 'property'. Am i right?

01/09/12 3:06am

Kevin McGillivray says:

That's just unnecessary overhead since that's the whole purpose of the LEFT JOIN clause in SQL.

I figured this out on my own. Apparently the problem was that I was limiting meta_key in the WHERE clause instead of in the LEFT JOIN clause.

If anyone is interested, here's the complete query:



	SELECT 

		p.*, pm.meta_value, pm2.meta_value

	FROM 

		$wpdb->posts p

	LEFT JOIN $wpdb->postmeta pm ON 

		p.ID = pm.post_ID AND 

		pm.meta_key = 'city'

	LEFT JOIN $wpdb->postmeta pm2 ON 

		p.ID = pm2.post_ID AND 

		pm.meta_key = 'price'

	WHERE 

		p.post_status = 'publish' AND 

		p.post_type = 'property' AND

		p.post_date < NOW()

	ORDER BY

		pm.meta_value ASC,

		p.post_title ASC

01/09/12 3:29am

Kevin McGillivray says:

I found a couple typos. Here's the corrected version:



	SELECT 

		p.*, pm.meta_value, pm2.meta_value

	FROM 

		$wpdb->posts p

	LEFT JOIN $wpdb->postmeta pm ON 

		p.ID = pm.post_ID AND 

		pm.meta_key = 'city'

	LEFT JOIN $wpdb->postmeta pm2 ON 

		p.ID = pm2.post_ID AND 

		pm2.meta_key = 'price'

	WHERE 

		p.post_status = 'publish' AND 

		p.post_type = 'property' AND

		p.post_date < NOW()

	ORDER BY

		pm.meta_value ASC,

		pm2.meta_value DESC

Last edited:
01/09/12
3:18am

Fahd Murtaza says:

I would suggest a more generic approach and a function which you can use again and again without any problems. Though the joins will slow down your performance but it works





# ------------------------------------------------------------

// GET POST META VALUE FUNCTION 

// By Jamie Oastler http://idealienstudios.com/blog/wordpress/custom-field-query-quandry

// Modified by Matt Varone http://www.mattvarone.com/web-design/query-multiple-meta-values

# ------------------------------------------------------------



/*



	Function variables:

	

	$aMetaDataList(array) example: array('meta_key' => 'meta_value','meta_key_2' => 'meta_value_2 )

	$szType(string) = "post" or "page"

	$szCategory(string) = example: '1' or '1,2,3'

	$iLimit(integer)

	

*/



function get_post_meta_multiple( $aMetaDataList = array(), $szType = 'post', $szCategory = NULL, $iLimit = NULL ) 

{

	global $wpdb;



	$szQuerystr = "SELECT p.* FROM $wpdb->posts AS p";



	if ( $szCategory != NULL AND is_string($szCategory) )

	{

		$szQuerystr .= " LEFT JOIN $wpdb->term_relationships ON (p.ID = $wpdb->term_relationships.object_id) ";

		$szQuerystr .= " LEFT JOIN $wpdb->term_taxonomy ON ($wpdb->term_relationships.term_taxonomy_id = $wpdb->term_taxonomy.term_taxonomy_id) ";

	}

			

	$szQuerystr .=  " WHERE p.ID IN ( ";



	$szQuerystr .= "SELECT post_id FROM $wpdb->postmeta WHERE ";

	

	$aInnerqry = array();

	

	foreach($aMetaDataList as $szKey => $szValue) 

	{

		$aInnerqry[] = $wpdb->prepare( "(meta_key = %s AND meta_value = %s)", $szKey, $szValue );	

	}



	$szQuerystr .= implode(" OR ", $aInnerqry);



	$szQuerystr .= " GROUP BY post_id ";

	$szQuerystr .= "HAVING count(*) = " . count($aMetaDataList);



	$szQuerystr .= ") AND p.post_status = 'publish' AND p.post_type = '".$szType."'";

	

	if ( $szCategory != NULL AND is_string($szCategory) )

  	$szQuerystr .= " AND $wpdb->term_taxonomy.term_id IN(".$szCategory.")";

	

	$szQuerystr .= " ORDER BY p.post_title ASC";				

	

	if ( $iLimit != NULL AND is_int($iLimit) )

	$szQuerystr .= " LIMIT ".$iLimit;

	

	// echo $szQuerystr;

	

	$aMetaResults = $wpdb->get_results($szQuerystr, OBJECT);

						

	return $aMetaResults;

}

I use it all the time.

01/09/12 3:29am

Kevin McGillivray says:
Thanks, Fahd. I've already found a solution but thanks for submitting.

Kevin

This question has expired.

Current status of this question: Refunded

Warning: Please do not give out any FTP or ssh credentials to anyone, unless you trust them completely. Giving out login details is dangerous.

If the asker does not get an answer then they have 10 days to request a refund.

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

$15 Custom query sorted on two meta fields

(3) Possible Answers Submitted...

$15
Custom query sorted on two meta fields