Laravel Query Builder express JSON join is not null

Question

I have the following SQL, and couldn't insert into Laravel's builder:

LEFT JOIN members ON JSON_SEARCH(places.member_ids, 'one', members.id) IS NOT NULL

I tried:

->leftJoin("members", DB::raw("JSON_SEARCH(places.member_ids, 'one', members.id)"), "is", DB::raw("not null"))

But the generated SQL kept becoming:

left join `members` on JSON_SEARCH(places.member_ids, 'one', members.id) = `is`

How can I do joining on JSON is not null?

EDIT

I found a mitigation for my case. I'm not sure why it works but it works.

->leftJoin("members", DB::raw("JSON_SEARCH(places.member_ids, 'one', members.id)"), "!=", DB::raw("''"))

Is it really a left join if you do not allow joining null? From the source code of the query builder and join clause, I'm quite sure what you want to do is not possible. You might be more successful joining a sub query though. — Namoshek
– Namoshek, Commented Aug 16, 2019 at 9:28
@Namoshek I'm not exactly fluent in SQL, but I came from my previous question where I wanted to do something like this. There's even a demo: stackoverflow.com/questions/57519025/… — Daniel Cheung
– Daniel Cheung, Commented Aug 16, 2019 at 9:32

Namoshek · Accepted Answer · 2019-08-16 09:41:15Z

0

I created an updated version of the dbfiddle of your previous question. You basically should be able to use a normal join($table) together with a whereNotNull($constraint):

->join('members')
->whereNotNull(DB::raw("JSON_SEARCH(places.member_ids, 'one', members.id)"))

answered Aug 16, 2019 at 9:41

Namoshek

6,5562 gold badges21 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Daniel Cheung Over a year ago

Thank you for answering. But well, I needed that left join though. Because I still needed to show posts when there are no tags for the post.

Namoshek Over a year ago

Alright, in this case you really need to join, yep.

Collectives™ on Stack Overflow

Laravel Query Builder express JSON join is not null

EDIT

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

EDIT

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related