meta_query - check if 'where' is empty before using

[ashleyfae]

We check if 'join' is empty:

if ( ! empty( $clauses['join'] ) ) {
	$join['meta_query'] = $clauses['join'];
}

But not 'where' :

// Remove " AND " from meta_query query where clause
$where['meta_query'] = preg_replace( $and, '', $clauses['where'] );

I ran into a case where $clauses was just:

array(
    'join' => '',
    'where' => ''
)

and it ended up breaking the whole query due to a stray AND in there where the meta WHERE clause would go.

[JJJ]

Are you able to include the exact SQL query that is causing this error?

I think I'm confused about how your recommended code change fixes the problem you've found, and I'd like to try and replicate it.

---

This bit of code is intended to always remove extra /^\s*AND\s*/ whether $where is empty or not. It should be preventing exactly what you're experiencing.

// Remove " AND " from meta_query query where clause
$where['meta_query'] = preg_replace( $and, '', $clauses['where'] );

WP_Meta_Query (and Berlin's extensions of it) should never generate only the word AND with no other SQL.

Wrapping it in:

if ( ! empty( $clauses['where'] ) ) {

...would only avoid empty clauses in the $this->query_clauses['where'] array. It would not prevent empty ANDs, I don't think?

---

Related, the compare_query and date_query arguments are generated similarly, so we'd want to explore the same type of code change for those arrays as well.

[ashleyfae]

These were the arguments being passed into BerlinDB:

array(8) {
  ["meta_query"]=>
  array(1) {
    ["relation"]=>
    string(3) "AND"
  }
  ["number"]=>
  int(20)
  ["orderby"]=>
  string(2) "id"
  ["type"]=>
  string(4) "sale"
  ["order"]=>
  string(4) "DESC"
  ["payment_key"]=>
  string(32) "8e1ca97d581303aea5a0915a23d2f72a"
  ["email"]=>
  string(17) "email@site.com"
  ["status__in"]=>
  array(11) {
    [0]=>
    string(9) "abandoned"
    [1]=>
    string(9) "cancelled"
    [2]=>
    string(8) "complete"
  }
}

Note the meta_query that isn't quite empty. It has a relation, but no actual clauses.

Generated MySQL query:

SELECT edd_o.id
FROM wp_edd_orders edd_o
WHERE edd_o.status IN ('abandoned', 'cancelled', 'complete')
  AND edd_o.type = 'sale'
  AND edd_o.email = 'email@site.com'
  AND edd_o.payment_key = '8e1ca97d581303aea5a0915a23d2f72a'
  AND
ORDER BY edd_o.id DESC
LIMIT 20

Because of the lack of empty check, $where['meta_query'] was always getting set to an empty string. The extra AND got added here: https://github.com/berlindb/core/blob/master/query.php#L941

[JJJ]

Thank you @ashleyfae! Looking into it...

[JJJ]

I'm all caught up now. 🚀

The trailing AND happens inside get_item_ids():

$where = implode( ' AND ', $this->query_clauses['where'] );

With your arguments, this->query_clauses['where'] ends up with an empty meta_query clause.

Interesting! Will merge your PR asap. Thank you!

[JJJ]

Merged into release/2.0.0, and back-ported to master. 💫