How use laravel whereNotNull

@MichalOravec

if using ->orWhereNotNull('phone') now it showing all post where value is not Null and dont make check if phone in post 1 is = phone post 2

@www888 Do you understand yourself?

@MichalOravec

I want show related post where phone number is equal. If I use ->orWhereNotNull('phone') it showing all post where filed is not null and don't make check if equal

@www888 Lke this?

$relatedPosts = Post::where('id', '!=', $post->id)
    ->where(function ($query) use ($post) {
        $query->where('name', $post->name)
            ->orWhere(function ($query) use ($post) {
                $query->where('phone', $post->phone)
                    ->whereNotNull('phone');
            });
   })->latest()->paginate(20);

@www888 so you want to check both?

$query->where('name', '=', $post->name)
    ->orWhere(function ($query) use ($post) {
                      $query->where('phone', '=', $post->phone)->whereNotNull('phone');
}) 

@MichalOravec

Not works.

Post 1 - Name: Tom Phone: Post 2 - Name: Bob Phone:

Both Post have Phone field Null In this case this two post is not related post

@www888 null with only uppercase N? Is that just a string Null?

@Sinnbeck Most likely, because the query what we provided is correct.

@Sinnbeck

$table->string('phone')->nullable();

https://i.ibb.co/Qc1byrp/test.png

@www888 Then it makes no sense, as that query should work. Can you show what you are getting? dd($relatedPosts->toArray());

@Sinnbeck

array:13 [▼
  "current_page" => 1
  "data" => array:2 [▶]
  "first_page_url" => "http://127.0.0.1:8000/post/6/edit?page=1"
  "from" => 1
  "last_page" => 1
  "last_page_url" => "http://127.0.0.1:8000/post/6/edit?page=1"
  "links" => array:3 [▶]
  "next_page_url" => null
  "path" => "http://127.0.0.1:8000/post/6/edit"
  "per_page" => 20
  "prev_page_url" => null
  "to" => 2
  "total" => 2
]

@www888 Do you think we see data?

@www888 Ok get the data ..

dd($relatedPosts->items()->toArray()); 

@www888 And show the result of $post as well.

dd($post->toArray());

@MichalOravec

This is result of $post

[▼
  "id" => 5
  "name" => "Test test"
  "phone" => null
  "created_at" => "2022-02-08T20:53:49.000000Z"
  "updated_at" => "2022-02-11T10:50:55.000000Z"
]

This is result of $post $relatedPosts

  "data" => array:2 [▼
    0 => array:27 [▼
      "id" => 6
      "name" => "Tom test 11"
      "phone" => null
      "created_at" => "2022-02-08T20:53:49.000000Z"
      "updated_at" => "2022-02-11T10:50:55.000000Z"
    ]
    1 => array:27 [▼
      "id" => 4
      "name" => "Tets 22"
      "phone" => null
      "created_at" => "2022-02-08T20:47:44.000000Z"
      "updated_at" => "2022-02-08T20:47:44.000000Z"
    ]
  ]

@www888 phone of your post is null...

Now show current query for relatedPosts.

@MichalOravec

This is relatedPosts(ID 6 and 4) for current post (ID5)

@www888 What is your current code for $relatedPosts?

@www888 So if the $post does not have a phone, you only want to check on name

$relatedPosts = Post::where('id', '!=', $post->id)
    ->where(function ($query) use ($post) {
        $query->where('name', $post->name)
           ->when($post->phone, function($query, $phone) {
            $query->orWhere(function ($query) use ($post) {
                $query->where('phone', $post->phone)
                    ->whereNotNull('phone');
            });
          });

   })->latest()->paginate(20);

@Sinnbeck This will show relatedPosts if post have same name. but if post have different name and same phone not showing as related. relatedPost logic is show If name is equal OR phone equal OR both name/phone equal

@www888 Give an example. The example you gave before, the $post didnt have a phone at all..

@Sinnbeck

Post id: 1 name: Bob phone: null

Post id: 2 name: Joe phone: null

Post id: 3 name: Joe phone: 777556622

Post id: 4 name: Joe phone: 777556622

Post id: 5 name: UserT phone: 777556622

For Post ID 2 related ID 3 (name = name ) For Post ID 3 related ID 4 (name = name and phone = phone) For Post ID 4 related ID 5 (phone = phone)

When Value is Null it can not be related For ex. Post ID 1 is not related ID 2

@www888 One more time...

What is your current code for $relatedPosts?

@MichalOravec This is what I am using, but tested all codes what was shared here

        $relatedPosts = Post::where('id', '!=', $post->id)
        ->where(function($query) use ($post) {
                 $query->where('name', '=', $post->name)
                      ->orWhere('phone', '=', $post->phone);
       })
       ->latest()->paginate(20);

@www888 Can you see the difference?

$relatedPosts = Post::where('id', '!=', $post->id)
    ->where(function ($query) use ($post) {
        $query->where('name', $post->name)
            ->orWhere(function ($query) use ($post) {
                $query->where('phone', $post->phone)
                    ->whereNotNull('phone');
            });
   })->latest()->paginate(20);

@MichalOravec Yes this but when using this code

Post id: 1 name: Bob phone: 444 Post id: 2 name: Joe phone: 444

If only post phone value is equal it not showing post as related

It works if: name = name name = name and phone=phone This not works: phone=phone

@jorpedito This may lead to inconsistent results depending on how the database engine handles NULL comparisons. If $post->phone is null then it may pull in unrelated posts that also have null in the phone column. The null check is necessary.

E.g. for MS SQL

When SET ANSI_NULLS is ON, a comparison in which one or more of the expressions is NULL does not yield either TRUE or FALSE; it yields UNKNOWN. When ANSI_NULLS is OFF, comparisons such as ColumnA = NULL return TRUE when ColumnA contains a null value and FALSE when ColumnA contains some value besides NULL.

https://stackoverflow.com/questions/11662651/comparing-a-value-to-a-null-in-t-sql

@jbloomstrom Yeah you are absolute right , forgot to handle that condition when was replying, this would be my fixed query.

$relatedPosts = Post::whereKeyNot($post->id)
        ->where(function($query) use ($post) {
                 $query->whereName($post->name)
                 ->orWhere(function ($query) use  ($post) {
						$query->wherePhone($post->phone)->whereNotNull($post->phone);
       			});
		 })
       ->latest()->paginate(20);