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

[Jossnaz]

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

related_post_id2:

`
"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

NOT EXISTS does:

`
"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
or
null for related_post_id2

does that make sense?

[Jossnaz]

my full query is this:

<?php
      $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);

[valentinbora]

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) );'

[SergeyBiryukov]

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.

[SergeyBiryukov]

Related: #19653