'WordPress Subquery returns more than 1 row on SELECT

I am executing this query with core cron by custom Wordpress plugin:

// MAKE SQL CALL
    $SQL = "SELECT ".$wpdb->prefix."postmeta.post_id FROM ".$wpdb->prefix."postmeta
    INNER JOIN ".$wpdb->prefix."posts ON (".$wpdb->prefix."posts.ID = ".$wpdb->prefix."postmeta.post_id )       
     WHERE ".$wpdb->prefix."postmeta.meta_key = '".$core_admin_values['listing_expiration']['key']."' 
     AND ".$wpdb->prefix."posts.post_status = 'publish'
     AND ".$wpdb->prefix."postmeta.post_id = (SELECT ".$wpdb->prefix."postmeta.post_id FROM ".$wpdb->prefix."postmeta WHERE ".$wpdb->prefix."postmeta.meta_key = 'listing_status' AND ".$wpdb->prefix."postmeta.meta_value != 1)
     AND ".$wpdb->prefix."posts.post_type = '".$core_admin_values['listing_expiration']['taxonomy']."_type'
     AND DATE(".$wpdb->prefix."postmeta.meta_value) < DATE(NOW())";     
    $expired_listings = (array)$wpdb->get_results($SQL);

But return this error:

FastCGI sent in stderr: "PHP message: Database error of WordPress Subquery returns more than 1 row for SELECT

How to solve this? I tried some solutions by similar problems here in Stack, but it still fails.

phpmysqlsqlwordpresssubquery


Solution 1:[1]

For the sake of legibility I reformatted your query. 

SELECT 
  postmeta.post_id 
FROM 
  postmeta
  INNER JOIN posts 
    ON (posts.ID = postmeta.post_id )       
WHERE 
  postmeta.meta_key = '".$core_admin_values['listing_expiration']['key']."' 
  AND posts.post_status = 'publish'
  AND postmeta.post_id = 
  (
      SELECT 
        postmeta.post_id 
      FROM 
        postmeta 
      WHERE 
        postmeta.meta_key = 'listing_status' 
        AND postmeta.meta_value != 1
  )
  AND posts.post_type = '".$core_admin_values['listing_expiration']['taxonomy']."_type'
  AND DATE(postmeta.meta_value) < DATE(NOW())";

I suspect your issue stems from the subquery in your where clause.

      SELECT 
        postmeta.post_id 
      FROM 
        postmeta 
      WHERE 
        postmeta.meta_key = 'listing_status' 
        AND postmeta.meta_value != 1

And that this chunk is returning more than one result. If you want to match against more than one result, change from "=" to "in" like so:

  AND postmeta.post_id in
  (
      SELECT 
        postmeta.post_id ...
  )

Solution 2:[2]

try to add LIMIT on subquery

// MAKE SQL CALL

 $SQL = "SELECT ".$wpdb->prefix."postmeta.post_id FROM ".$wpdb->prefix."postmeta
    INNER JOIN ".$wpdb->prefix."posts ON (".$wpdb->prefix."posts.ID = ".$wpdb->prefix."postmeta.post_id )       
     WHERE ".$wpdb->prefix."postmeta.meta_key = '".$core_admin_values['listing_expiration']['key']."' 
     AND ".$wpdb->prefix."posts.post_status = 'publish'
     AND ".$wpdb->prefix."postmeta.post_id = (SELECT ".$wpdb->prefix."postmeta.post_id FROM ".$wpdb->prefix."postmeta WHERE ".$wpdb->prefix."postmeta.meta_key = 'listing_status' AND ".$wpdb->prefix."postmeta.meta_value != 1 LIMIT 1)
     AND ".$wpdb->prefix."posts.post_type = '".$core_admin_values['listing_expiration']['taxonomy']."_type'
     AND DATE(".$wpdb->prefix."postmeta.meta_value) < DATE(NOW())";     
    $expired_listings = (array)$wpdb->get_results($SQL);

Solution 3:[3]

similar problem several years after, but, in my case, I wasn't able to find the query to make the change, so, I decided to remove the warning message (because it was just a warning) and the way that I used for did that was adding this lines to the wp-config.php file:

ini_set('display_errors','Off');
ini_set('error_reporting', E_ALL );
define('WP_DEBUG', false);
define('WP_DEBUG_DISPLAY', false);

Regards!

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Beardo
Solution 2 Always Sunny
Solution 3 Martín Mori

Related Questions