Laravel join with where clause

How can I join these tables to retrieve id from users table? I am using Laravel 5.8. I am new to Laravel and finding Laravel Join complicated.

1. users Table

   id | country_id | state_id | city_id | user_role_id | user_rights_id

2. notifications Table

   id | country_id | state_id | city_id | user_rights_id

3. user_roles Table

   id | user_id

4. notification_by_roles Table

   id | notification_id | user_role_id

Now, I want to fetch id from users table for a single result(row) from the notification table.

first of all, only those id's from users table should be selected where user_rights_id from notification table matches with user_rights_id in the users table.

Then a collection should be fetched from notification_by_roles table where notification_id in notification_by_roles table matches with id of notifications table.

Then collections of id's from users table should be filtered out based on the user_role_id in users table which matches with user_role_id in notification_by_roles table.

Now this collection should be further filtered out based on the country_id in users table which matches with the country_id in notifications table only if state_id from notification table is null

Otherwise, this collection should be filtered out based on the state_id in users table which matches with the state_id in notifications table only if city_id from notification table is null

Otherwise, this collection should be filtered out based on the city_id in users table which matches with the city_id in notifications table.

After all these filters I should get id from users table if any.

I will be thankful to you.

from Newest questions tagged laravel-5 - Stack Overflow http://bit.ly/2ICepjG
via IFTTT