How to not get some data based on records in another table with laravel eloquent?

Question

Here's my Database Structure:

Products : ['id','name','image']
Request : ['id','marketer_id','distributor_id']
RequestItems : ['id','request_id','product_id','quantity']

Now this is just a short example of structure. what I'm trying to do is that, I have a page with request, which in this page I'm getting items in a request base on RequestItems table, and i want to add a button in my page to add product to this request, but i want to show products that are not exist in RequestItems.

I can make a condition to check before adding product to make sure user won't add 1 product 2 times in a request, but i also want to make it clear that user see products in add button which it's already exist in his request items.

I just need help for Query, I'm developing with Laravel & vue.js for SPA.

My Solution (But looking for better Solution) :

public function getRequestRepresentativeSideProducts(RepresentativesRequests $id, Request $request){

    $products = DistributorProducts::where(
        'distributor_id', $request->distributor_id
    )
        ->latest()
        ->get();


    $data = $id->items()->latest()->get();

    $myArray = array();

    for ($i = 0; $i < $data->count(); $i++)
    {
        $myArray[] = $data[$i]->product_id;
    }

    return $products->except($myArray);

}

Edit 01 : I've managed to get response with below query, but It's taking all data

    $data = DistributorProducts::doesntHave('requestRepresentativeSideItems', 'and', function ($query){
        $query->where('representative_request_id', '=', 1);
    })->where('distributor_id', $request->distributor_id)
        ->latest()
        ->get();

    $data = DistributorProducts::whereDoesntHave('requestRepresentativeSideItems', function (Builder $query) use ($id) {
        $query->where('representative_request_id', 'like', $id->id);
    })->where('distributor_id', $request->distributor_id)
        ->latest()
        ->get();

    $data = DistributorProducts::whereDoesntHave('requestRepresentativeSideItems', function (Builder $query) use ($id) {
        $query->where('representative_request_id', $id->id);
    })->where('distributor_id', $request->distributor_id)
        ->latest()
        ->get();

I have 3 products for distributor_id which 2 of them are in request items, so my query should show 1 but will above query still I'm getting all products.

Edit 02: I've checked where from $query and used like too and changed request_id but didn't changed result at all. looks like it's just ignoring that part.

umm what you have tried? i could only think off doing it in like two queries, one to fetch all `product_id` you already have in a given `request` then filter the existing out using [`except`](https://laravel.com/docs/6.x/eloquent-collections#method-except). thats the gist, hope it help. — Bagus Tesa, Jan 23 '20 at 01:53
@BagusTesa well, i didn't tried anything since i didn't knew how to handle it. i've thought about using multiple queries and `except`, but still was a bit complicated, and also i saw people doing amazing things with 1 query with eloquent, since i'm not expert i don't wanna do some weird logic. i'm trying to work on multiple query to achieve this, but still wanna find a better way. — Atlas-Pio, Jan 23 '20 at 02:11
curious question, does you represent `RequestItems` with its own model or did you treat it as a pivot table without model? i vaguely thinking we can use [`doesntHave`](https://laravel.com/api/6.x/Illuminate/Database/Eloquent/Builder.html#method_doesntHave) with closure to restrict it to certain `request_id`. — Bagus Tesa, Jan 23 '20 at 03:04

score 0 · Answer 1 · answered Jan 23 '20 at 03:17

Presuming you have the id for the Request you wanted to filter out, the SQL query will be roughly looked like

SELECT * FROM `products` WHERE NOT EXISTS 
         (SELECT * FROM `request_items` where `products`.`id` = `product_id` and `request_id` = ? )

I had to make additional assumtion as you didnt provide the model, i'd just assume the following

class Product extends Model
{
     public function requestItems()
     {
       return $this->hasMany(RequestItems::class);
     }
}

class RequestItems extends Model
{
     public function products()
     {
       return $this->belongsTo(Product::class);
     }
}

Then we can use doesntHave query criteria to find products that dont have relationship with the RequestItems for given request id (in this example its 1).

Products::doesntHave('requestItems', 'and',
                     function($query){ $query->where('request_id', '=', 1); }
                    );

Hope it helps. I havent tried it myself, but i believe it would works. You can use toSql method to see its resulting SQL query.

Note: some insight on the differece of IN and EXISTS

thanks for answer, i need time to check this, but i'll notice you whatever happened. i hope it work. — Atlas-Pio, Jan 23 '20 at 03:20
Hey man, I've managed to use your query, but still it's getting all products. check my question to see how I used your query. — Atlas-Pio, Jan 24 '20 at 00:28
hi @Atlas-Pio, i dont quite understand your requirement now.. the `doesntHave` properly exclude `product`s that dont have relation to current `request` as shown on this [test project](https://bitbucket.org/BagusTesa/sto-answers/src/249bec12dc680acd78a6a98f7eaa8de772d3f3d6/?at=answer%2F59870597). i am not sure why now `distributor_id` (and `representative_request_id`) is involved in play. could you provide example data and the expected output so we can figure this out? — Bagus Tesa, Jan 31 '20 at 02:24

How to not get some data based on records in another table with laravel eloquent?

1 Answers1