Search based on many to many relation with multiple condition on same column

Question

I have a many to many relation on my laravel 5.3 project. There is two models associated with this problem. They are Job and Tag. The model Job can have many Tag associated with it and also the model Tag related to many Job. I have assigned the relation Many To Many using the key word "belongsToMany" in both model classes. The database of the relation is : table: jobs

________________
 id |  title    |
____|___________|
  1 |  Developer|
____|___________|
  2 |  Designer |
____|___________|
  3 |  Tester   |
____|___________|
  4 |  manager  |
____|___________|

table : tags

_________________
 id |  tag_name |
____|___________|
  1 |  php      |
____|___________|
  2 |  html     |
____|___________|
  3 |  css      |
____|___________|

and the pivot table : job_tag

_____________________
 job_id |  tag_id   |
________|___________|
  1     |   1       |
________|___________|
  1     |   2       |
________|___________|
  2     |   1       |
________|___________|
  3     |   1       |
________|___________|
  3     |   2       |
________|___________|
  4     |   2       |
________|___________|

The user can pass multiple tag names to retrieve the jobs which are related to the both tags which are passed by the user.(AND condition)

Example: when a user passes the tag_name "php" and "html" i want to display the details of the jobs [id:1 title:developer] and [id:2 title:designer].

Hint : retrieve the job records which have the tags both "php" and "html"

NB: the number of the tag names passed by the user is not defined ie, user can pass as much as they can.

---

Here is something i have tried:

$jobs = Job::whereHas('tags', function ($query) use($params) {
                    foreach ($params['tags'] as $tag) {
                        $query->where('tag', $tag);
                    }
                });

The above code doesnt returns anything but generates the following sql:

select * from `jobs` where exists (select * from `tags` inner join `job_tag` on `tags`.`id` = `job_tag`.`tag_id` where `job_tag`.`job_id` = `jobs`.`id` and (`tag` = css) and (`tag` = javascript))

I was tried to get results based on this sql, which doesn't work.

Hope you people understand my problem and will help me find a solution. Thanks in advance.

Answer 1

One option is to use multiple whereHas as:

$query = Job::query();

foreach ($params['tags'] as $tag) {
    $query->whereHas('tags', function ($q) use($tag) {
        $q->where('tag', $tag);
    });
}

$jobs = $query->get();

OR

Assuming you have an array of tag id as:

$tag_ids = [2, 4];

Then you can try as:

Job::whereHas('tags', function($q) use($tag_ids) {
        $q->whereIn('tag_id', $tag_ids)
          ->groupBy('job_id')
          ->havingRaw('COUNT(DISTINCT tag_id) = '.count($tag_ids));
    })->get();