Laravel tag search to return images that contain a

Question

I'm developing an application that lists images, and has multiple tags assigned to each image. I'd like to be able to find the images that are tagged with all of the tags being searched for.

images table

- id
- download_url
- image_width
- image_height

tags table

- id
- name

image_tag table

- id
- image_id
- tag_id

In my model for Image, I've got belongsToMany('Tag'), and in my model for Tags, I've got belongsToMany('Image'). This is all working as I hoped, so far.

However, my problem occurs when I try to search for images via their tags. I seem to be able to do an or search with the following code.

     $tags = explode(' ', $tag_text);

     $query = DB::table('images');
     $query->join('image_tag', 'images.id', '=', 'image_tag.image_id');
     $query->join('tags', 'tags.id', '=', 'image_tag.tag_id');
     foreach ($tags as $tag)
     {
          $query->orWhere('tags.name', '=', $tag);
     }

     $result = $query->get();

If I search for (for example) nyc skyscraper, I get a list of images that have the tag nyc or the tag skyscraper. However, I'd like to only show the images which have the tag nyc and the tag skyscraper.

I tried changing $query->orWhere('tags.name', '=', $tag); to $query->where('tags.name', '=', $tag);, but that then doesn't return any results (presumably because it's messing up the tag name search). The query Laravel is running "select * from 'images' inner join 'image_tag' on 'images'.'id' = 'image_tag'.'image_id' inner join 'tags' on 'tags'.'id' = 'image_tag'.'tag_id' where 'tags'.'name' = ? and 'tags'.'name' = ?" with the bindings nyc and skyscraper.

I wondered about doing an image search for each tag individually (which in the above example would return images with the tag nyc and then return images the tag skyscraper), and then pick out the images which occur in both results, but I'm not sure what the best way to implement this would be, and whether it's the most efficient way.

I suspect there's an easy way of doing this using the query builder that I'm currently missing!

What complicates it a little further (and currently doesn't work), is that as well as the tag search, I'd like to show images with (for example), a certain width. So after the tag code, I've included

    $query->where('image_width', '>', 1000);

which returns all images with a width over 1000 pixels, but only when I don't include the tag search code. If I do include the tag search code, it ignores the image width part of the search.

So, ideally, I'd like to return the images which are tagged nyc and are tagged skyscraper, and are over 1000 pixels wide.

Answer 1

Manual count and having would work, but you can use simple whereHas instead:

// let it be array of tag ids:
$tagsIds;

$images = Image::whereHas('tags', function ($q) use ($tagsIds) {
   $q->whereIn('tags.id', $tagsIds);
}, '=', count($tagsIds))->get();

However mind that both mine and @user3158900's solutions will work only, when you have no duplicates on the pivot table. Using Eloquent's attach method on BelongsToMany relation may lead to such situation.

Answer 2

Looking for images that has all the tags, we will probably need to count our results and group by the image_url after making our necessary joins. This way, we can see how many tags each image matches and just get the images that match all the tags using the count because we know how many tags we have.

$tags = explode(' ', $tag_text);

 $images = DB::table('images')
  ->select(DB::raw('COUNT(*) AS `TheCount`, images.download_url'))
  ->join('image_tag', 'images.id', '=', 'image_tag.image_id')
  ->join('tags', 'tags.id', '=', 'image_tag.tag_id')
  ->whereIn('tags.id', $tags)
  ->groupBy('images.download_url')
  ->having('TheCount', count($tags))
  ->get();

If you need more than just the download_url for each image, you might need to change it up a bit. I'd suggest instead of selecting and grouping by download_url, do it by the image's id and then you can set it up as a subselect and join it back up with the images table on the matching id's if that makes any sense.

Or just use ->lists('images.id') to get the array of id's and make another query on the images table getting all images in that list of id's.