Laravel select all rows if id exists in another table and a column has specific values on that table

I have 4 tables:

notices (id, title, desc, is_published)

notices_to_districts (id, notice_id, district_id)

notices_to_employees (id, notice_id, user_id)

notices_to_establishments (id, notice_id, establishment_id)

I want to get all notices if,

notices_to_districts has a specific district_id, or

notices_to_employees has a specific user_id, or

notices_to_establishments has a specific establishment_id

If anyone of the above 3 conditions meets, I want those rows from notices.

I am trying this but not working -

$notices = Notice::join('notices_to_districts', 'notices_to_districts.notice_id', '=', 'notices.id')
            ->join('notices_to_establishments', 'notices_to_establishments.notice_id', '=', 'notices.id')
            ->join('notices_to_employees', 'notices_to_employees.notice_id', '=', 'notices.id')
            ->where('district_id', '=', $user_district_id)
            ->orWhere('establishment_id', '=', $user_establishment_id)
            ->orWhere('user_id', '=', $user_id)
            ->where('is_published', '=', 1)->get();

Oussama.tn

6 years ago

Level 11

Hi!

I guess you have these relationships on your Notice model:

notices_to_districts, notices_to_employees and notices_to_establishments.

Then try this:

    $notices = Notice::where(function ($q) use ($district_id, $user_id, $establishment_id) {
        $q
            ->orWhereHas('notices_to_districts', function ($q) use ($district_id) {
                $q->where('district_id', $district_id);
            })
            ->orWhereHas('notices_to_employees', function ($q) use ($user_id) {
                $q->where('user_id', $user_id);
            })
            ->orWhereHas('notices_to_establishments', function ($q) use ($establishment_id) {
                $q->where('establishment_id', $establishment_id);
            });
    })->get();

If you want to debug your query and see sql statement:

$query = Notice::where(function ($q) use ($district_id, $user_id, $establishment_id) {
        $q
            ->orWhereHas('notices_to_districts', function ($q) use ($district_id) {
                $q->where('district_id', $district_id);
            })
            ->orWhereHas('notices_to_employees', function ($q) use ($user_id) {
                $q->where('user_id', $user_id);
            })
            ->orWhereHas('notices_to_establishments', function ($q) use ($establishment_id) {
                $q->where('establishment_id', $establishment_id);
            });
    });

dd($query->toSql());

Please or to participate in this conversation.