Laravel SQL Query. Help me

I have a table name “Offers” with 3 (public, request, private ) types of data. Now I want to show directly to the user end public and request offers. and private offers not be shown but when admin will add user_id and offer_id in a new table (offer_access) then the private offer should be available to user end for only these users.

$offer = Offer::where('status', 'public')->orWhere('status', 'request')->latest()->get();

I wrote this one and getting public and private offers for user end. Now I want to show private offers to a user when it available on “OfferAccess” table.

So now, How can I write the SQL query for that?

Answer

You can try blow:

$offer = Offer::whereIn('status', ['public', 'request'])
        ->orWhere(function($query) {
            $query->where('status', 'private')
            ->whereHas('OfferAccess', function($qry) {
                $qry->where('user_id', auth()->user()->id);
            });
        })->get();

If you need only the latest results can use ->latest()->get() instead of only ->get(). Also if you need to get it for any other user than the current logged in user, then you need to pass user_id to closure function. so you need to use updated orWhere like as:

->orWhere(function($query) use ($user_id) {
            $query->where('status', 'private')
            ->whereHas('OfferAccess', function($qry) use ($user_id) {
                $qry->where('user_id', $user_id);
            });
        })

It should give you the desired results.