Complex MYSQL Query

I'm having trouble with a complex custom MYSQL query (the logic is too complex for a regular wp_query)

One of the primary goals of the query is to select posts where either the post_author = $user_id OR the post meta_key 'contacts' contains meta_value $associate_id.

The 'contacts' meta key is a relational custom field stored in the database as an array like this: a:1:{i:0;s:2:"44";}. The custom query I've written works in PHPMyAdmin, but for the life of me I can't get the specific logic I just described to work on the WP frontend.

Here is the full custom query right now:

global $wpdb;
global $post;
$query = "
	SELECT *
	FROM $wpdb->posts, $wpdb->postmeta
	WHERE
		$wpdb->posts.ID = $wpdb->postmeta.post_id
		AND $wpdb->posts.post_status = 'publish'
		AND $wpdb->posts.post_type = 'home'";
if ( !empty($associate_id) ) {
	$query .= " AND (
			$wpdb->posts.post_author = '$user_id'
			OR (
****				$wpdb->postmeta.meta_key = 'contacts'
****				AND $wpdb->postmeta.meta_value LIKE '%$associate_id%'
			)
		)";
}
$query .= " AND (
			$wpdb->postmeta.meta_key = 'status'
			AND $wpdb->postmeta.meta_value = '$arg_status'
		)
	GROUP BY $wpdb->posts.ID
	ORDER BY $wpdb->posts.post_date DESC
";

Lines 14 and 15 (Quadruple starred above) are causing the trouble - if I remove those lines the rest of the query works perfectly. I believe the main issue is with the meta_value comparison on line 15. I've tried using = the full array (copied from the database), using double percentage signs, and all sorts of other syntax combinations without success.

I also tried using a different meta_key and meta_value in place of lines 14 and 15, and that filter did work as intended, which means something is specifically wrong with lines 14 and 15.

Any ideas or feedback? Thanks in advance!

  • Matthew

    If it's helpful to visualize, here is the actual SQL query that the PHP code above outputs (trouble code starred):

    SELECT * FROM dba_posts, dba_postmeta
    WHERE dba_posts.ID = dba_postmeta.post_id
         AND dba_posts.post_status = 'publish'
         AND dba_posts.post_type = 'home'
         AND (
              dba_posts.post_author = '3'
              OR (
    ****           dba_postmeta.meta_key = 'contacts'
    ****           AND dba_postmeta.meta_value LIKE '%44%'
              )
         )
         AND (
              dba_postmeta.meta_key = 'status'
              AND dba_postmeta.meta_value = 'Active'
         )
    GROUP BY dba_posts.ID
    ORDER BY dba_posts.post_date DESC
  • Rupok

    Hi Matthew, hope you had a wonderful day.

    I'm afraid this is beyond my expertise. So I'm pinging our SLS (Second Level Support) team as they can give you better idea regarding this.

    Please keep in mind, our SLS Team people work round the clock and they have to deal with lots of critical issues and other things. So it may take a little while for them to check this and provide a feedback.

    Have a nice day. Cheers!
    Rupok

  • Matthew

    Thanks for the replies Rupok and Panos

    Panos, unfortunately for unrelated reasons 'contacts' has to be stored this way in the DB. The search time is not an issue in this situation.

    I have WP_DEBUG set to true, and $wpdb->show_errors() running, but all it's giving me is this:

    WordPress database error
    	SELECT *
    	FROM dba_posts, dba_postmeta
    	WHERE
    		dba_posts.ID = dba_postmeta.post_id
    		AND dba_posts.post_status = 'publish'
    		AND dba_posts.post_type = 'home' AND (
    			dba_postmeta.meta_key = 'status'
    			AND dba_postmeta.meta_value = 'Active'
    		)
    	GROUP BY dba_posts.ID
    	ORDER BY dba_posts.post_date DESC;
     for query
    	SELECT *
    	FROM dba_posts, dba_postmeta
    	WHERE
    		dba_posts.ID = dba_postmeta.post_id
    		AND dba_posts.post_status = 'publish'
    		AND dba_posts.post_type = 'home' AND (
    			dba_postmeta.meta_key = 'status'
    			AND dba_postmeta.meta_value = 'Active'
    		)
    	GROUP BY dba_posts.ID
    	ORDER BY dba_posts.post_date DESC;
     made by require('wp-blog-header.php'), require_once('wp-includes/template-loader.php'), include('/themes/the-theme/page-templates/page-home.php'), featured_homes

    The server error log says the same thing with no other details given.

  • Hoang Ngo

    Hi Matthew,

    I hope you are well today.

    Could you please try this

    global $wpdb;
    global $post;
    $query = "
    	SELECT *
    	FROM $wpdb->posts, $wpdb->postmeta
    	WHERE
    		$wpdb->posts.ID = $wpdb->postmeta.post_id
    		AND $wpdb->posts.post_status = 'publish'
    		AND $wpdb->posts.post_type = 'home'";
    if ( !empty($associate_id) ) {
    	$query .= " AND (
    			$wpdb->posts.post_author = '$user_id'
    			OR (
    				$wpdb->postmeta.meta_key = 'contacts'
    				AND $wpdb->postmeta.meta_value LIKE '%$associate_id%'
    			)
    		)";
    }
    $query .= " AND (
    			$wpdb->posts.ID IN (SELECT post_id FROM $wpdb->postmeta WHERE meta_key='status' AND meta_value='$arg_status')
    		)
    	GROUP BY $wpdb->posts.ID
    	ORDER BY $wpdb->posts.post_date DESC
    ";

    Because you defined meta_key = 'contacts' first, and then in the below you define meta_key='status', and that will make SQL always ignore the 'contacts', that's why it won't work.

    Best regards,
    Hoang

Thank NAME, for their help.

Let NAME know exactly why they deserved these points.

Gift a custom amount of points.