CODEWITHSUNDEEP
phpmysqllaraveleloquentlaravel-5.3
iamab.in
iamab.in

Reputation: 2070

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

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.

Upvotes: 1

Views: 1895

Answers (2)

Amit Gupta
Amit Gupta

Reputation: 17658

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();

Upvotes: 1

Jeremy Harris
Jeremy Harris

Reputation: 24549

In your foreach, you need the tags to be compared with OR instead of AND.

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

I'm not sure if the second level of closure is necessary, but it will help if you end up adding on any other WHERE conditions to your query after the fact.

Upvotes: 0

Related Questions