Rails: Has many through associations -- find with AND condition, not OR condition

Question

I have the following query method in my ActiveRecord model:

def self.tagged_with( string )
    array = string.split(',').map{ |s| s.lstrip }
    select('distinct photos.*').joins(:tags).where('tags.name' => array )
end

So, this finds all records that have tags taken from a comma separated list and converted into an array.

Currently this matches records with ANY matching tags -- how can I make it work where it matches ALL tags.

IE: if currently if I input: "blue, red" then I get all records tagged with blue OR red.

I want to match all records tagged with blue AND red.

Suggestions?

-- EDIT --

My models are like so:

class Photo < ActiveRecord::Base
  ...
  has_many :taggings, :dependent => :destroy
  has_many :tags, :through => :taggings
  ...
  def self.tagged_with( string )
    array = string.split(',').map{ |s| s.lstrip }
    select('distinct photos.*').joins(:tags).where('tags.name' => array )
  end
  ...
end

class Tag < ActiveRecord::Base
  has_many :taggings, :dependent => :destroy
  has_many :photos, :through => :taggings
end

class Tagging < ActiveRecord::Base
  belongs_to :photo
  belongs_to :tag
end

A tag has two attributes: ID and Name (string).

Passing an array as a parameter implicitly uses a SQL IN condition, explaining why your results have records with any matching tags. Can you provide more detail on the structure of the `photos` and `tags` tables? I don't think you can achieve this with a simple join and where condition, but I may just not understand the structure fully. — MikeH, Apr 29 '11 at 21:38
What you want is a join for each condition - see http://stackoverflow.com/questions/5376869/rails-join-with-multiple-conditions/5377304#5377304 it covers HABTM which is not too dissimilar to hm:t — Omar Qureshi, May 02 '11 at 21:32

Zabba · Accepted Answer · 2011-05-02T21:26:36.437

5

This should work:

def self.tagged_with( string )
  array = string.split(',').map{ |s| s.lstrip }
  select('distinct photos.*').
    joins(:tags).
    where('tags.name' => array).
    group("photos.id").
    having("count(*) = #{array.size}")
end

Above will match photos that have tags red and blue at least. So that means if a photo has red, blue and green tags, that photo would match too.

edited May 02 '11 at 21:26

answered May 02 '11 at 21:06

Zabba

64,285
47
179
207

That worked, thank you! To be sure I understand -- is `group("photos.id")` making sure there aren't duplicates? And, is `having("count(*) = @{array.size}")` making sure that the photo has at least as many matching tags as were in the array? – Andrew May 02 '11 at 22:23
`group("photos.id")` is just grouping the rows based on that field. Then the `having` clause is ensuring that we select only the groups where the rows are equal to x. Since the `group` will return 1 row per photo id, it is already "distinct", so no need to use `distinct photos.*` really. Try this out in `rails c`: `Photo.select("photos.id,tags.name,count(*)").joins(:tags).where('tags.name' => array).group("photos.id")` - You should see all the rows with counts. You might want to install [hirb](http://tagaholic.me/2009/03/13/hirb-irb-on-the-good-stuff.html) too. – Zabba May 02 '11 at 22:35
Ok, this is great on SQLite but I've run into the common "PG Error: column ____ must appear in group by clause or aggregate function." I tested `select('distinct photos.*').joins(:tags).where('tags.name' => array).group("photos." + self.column_names.join(', photos.')).having("count(*) = #{array.size}")`, and it works, but it seems hackish. Is there a better way to avoid the group by problem? Apparently `having` doesn't work without `group by`. – Andrew May 02 '11 at 22:39
In my original answer, try removing the `select` clause and in the `having` replace `count(*)` with `count(photos.id)`..whats the result? – Zabba May 02 '11 at 22:41
It reports that for any column name that I draw information from in the view. The problem is, I want this `tagged_with` function to return the entire Photo record to me so I can use it to show all the information about photos matching the queried tags if I so desire. – Andrew May 02 '11 at 22:52
Can you post the SQL generated by PG? (i.e. the SQL generated from my answer) And just to be sure, is this code giving the same error (and what is the SQL generated for this code): `Photo.joins(:tags).where('tags.name' => array).group("photos.id").having("count(*) = #{array.size}")`. Also see [this answer](http://stackoverflow.com/questions/1769361/postgresql-group-by-different-from-mysql/1777448#1777448) – Zabba May 02 '11 at 23:08
To be honest I'm not sure how to get the SQL generated by PG... I use Postgre in production (on Heroku) but have had real problems getting the version Heroku uses (8.2) setup in development, so I've just stuck with SQLite for development. The Heroku logs don't include a record of SQL queries the way the development log does. To answer your other question both the code in the previous comment and your suggestion to remove the select clause and replace count(*) with count(photos.id) generated the same error message. – Andrew May 02 '11 at 23:35
Did you try the `DISTINCT ON`? What is the error message (if any) from that? – Zabba May 02 '11 at 23:51
Also, you'll get answers from more people if you posted this new problem as a separate question. – Zabba May 02 '11 at 23:58
Thanks for the suggestions, I'll go ahead and open a new question when I get a chance. I appreciate the help - in fact, would you consider emailing me (my email address is in my profile) -- I'd like to ask you a question off-site. Thanks! – Andrew May 03 '11 at 04:04
Hey thanks for all the help - sorry I've been so busy I didn't accept this answer right away. – Andrew May 05 '11 at 12:23

score 1 · Answer 2 · answered Apr 29 '11 at 18:25

1

You could change your select statement to the following:

select('distinct photos.*').joins(:tags).where('tags.name = ?',  array.join(' OR '))

Which will properly create the OR string in the where clause.

ian.

answered Apr 29 '11 at 18:25

ipd

5,674
3
34
49

In testing this doesn't seem to work. When I search with more than one tag I get no results, even if there are photos that have both tags I was searching for... – Andrew Apr 29 '11 at 21:37
In the rails console, add .to_sql to the end of select() call to see what SQL is being generated, this will help you debug. – ipd Apr 29 '11 at 21:53

score 1 · Answer 3 · edited May 23 '17 at 12:03

1

LOL the solution for this is not a simple task--I thought through it from a SQL standpoint and it was UGLY. I figured somebody else has to have tried this so I did some searching and found this post that should help you:

HABTM finds with "AND" joins, NOT "OR"

edited May 23 '17 at 12:03

Community

1
1

answered Apr 30 '11 at 01:24

MikeH

796
7
18

Mike, I'm going to work on that answer a bit but I'm pretty much a SQL novice and I don't really get how to apply it to my situation. If you care to elaborate on what the query string to do this would look like in Rails 3 syntax that would be awesome. Thanks! – Andrew May 02 '11 at 03:37

Rails: Has many through associations -- find with AND condition, not OR condition

3 Answers3

Linked