Make WordPress Core

Opened 4 years ago

Last modified 4 years ago

#49429 new enhancement

There seems to be no way to check query value for NULL

Reported by: jossnaz's profile Jossnaz Owned by:
Milestone: Awaiting Review Priority: normal
Severity: normal Version: 5.3.2
Component: Query Keywords: 2nd-opinion dev-feedback
Focuses: Cc:


NOT EXISTS and EXISTS both don't do what I want

I want to check WHERE meta_value IS NOT NULL

exists does this, applied to meta field


"where" "mt5.meta_key = 'related_post_id2'"
"join" " INNER JOIN wp_2_postmeta AS mt5 ON ( wp_2_posts.ID = mt5.post_id )"

that is not what I want


"where" "mt5.post_id IS NULL"
"join" " LEFT JOIN wp_2_postmeta AS mt5 ON (wp_2_posts.ID = mt5.post_id AND mt5.meta_key = 'related_post_id2' )"

well, thats not what I want either, because now it checks "if there is a row with related_post_id2".

I want

"A row where the meta_value is not null"

in reality what I want is

Any entry, that has:
No value for related_post_id2
empty string for related_post_id2
null for related_post_id2

does that make sense?

Change History (5)

#1 @Jossnaz
4 years ago

my full query is this:

      $args  = [
          'post_type'   => 'notification',
          'numberposts' => -1,
          'meta_query'  => [
              'relation' => 'AND',
                  'key'     => 'send_at',
                  'value'   => Ut::getMysqlTimestamp('now'),
                  'compare' => '<=',
                  'key'     => 'related_post_id',
                  'value'   => $sri,
                  'compare' => '='
                  'key'     => 'notification_type',
                  'value'   => Notification::STORY_REQUEST_MAIN_TYPE,
                  'compare' => '='
                  'relation' => 'OR',
                      'key'     => 'related_post_id2',
                      'value'   => '',
                      'compare' => '='
                      'key'     => 'related_post_id2',
                      'compare' => 'NOT EXISTS'

          'meta_key'    => 'send_at',
          'orderby'     => 'meta_value',
          'order'       => 'DESC'
      $posts = get_posts($args);

Last edited 4 years ago by Jossnaz (previous) (diff)

#2 @SergeyBiryukov
4 years ago

  • Component changed from General to Query

#3 follow-up: @valentinbora
4 years ago

  • Keywords 2nd-opinion dev-feedback added

Related: #18158

I've tested this via WP CLI and haven't so far found a way to use "IS NULL" or "IS NOT NULL":

wp eval 'get_posts([ "meta_query" => [ [ "key" => "my_meta_key", "compare" => "IS NULL" ] ] ]); global $wpdb; print_r( end($wpdb->queries) );'

#4 in reply to: ↑ 3 @SergeyBiryukov
4 years ago

Replying to valentinbora:

I've tested this via WP CLI and haven't so far found a way to use "IS NULL" or "IS NOT NULL"

IS NULL is not a supported value for compare, NOT EXISTS can be used instead, see [21185].

EXISTS can also be used by omitting a value to check, but apparently it's indeed not an equivalent for IS NOT NULL, per the ticket description.

Note: See TracTickets for help on using tickets.