Question 254370 in Laravel Question posted in
The official Laravel Documentation can be found here.

Laravel query idea

I’m a bit stuck writing something that should be pretty simple, so I need your help. I have one main table "posts", and two other related tables "categories" and "sectors".

I’m making the filter route, and the logic should be to return all posts, except those that contain sector X along with category Y. Can I write it in one query, or do I need to retrieve all posts, and then later remove those with some array mapping?

Tags:

2

Answers


  1. 0

    I assume that you have corresponding Eloquent Models "Post", "Category" and "Sector" which have also relations defined between themselves. It is then very easy to query for related models in or not in a relation as described in https://laravel.com/docs/10.x/eloquent-relationships#querying-relations

    Login or Signup to reply.
  2. 0

    You could use a combination of whereDoesntHave() queries. Assuming you pass category & sector as query params (single ID in this case), and that you have category() and sector() relations on your models, you can do something like this:

    $query = Post::query();

$query->when($request->input('category'), function ($q) use ($request) {
    return $q->whereDoesntHave('category', function ($category){
        return $category->where('id', $request->input('category'));
    });
});

$query->when($request->input('sector'), function ($q) use ($request) {
    return $q->whereDoesntHave('sector', function ($sector){
        return $sector->where('id', $request->input('sector'));
    });
});

    The when() function just replaces the if-else statement, so you don’t have to check if your params are passed to the request

    If your query params contain more than one category or sector value, just change the last query to:

    return $category->whereIn('id', $request->input('category')); //$request->input('category') has to be array


return $sector->whereIn('id', $request->input('sector')); //$request->input('sector') has to be array
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search