I have a USER model and a TAG model and they have a has_and_belongs_to
relationships between them.
I want to Tag.find(:all) … but I only want to return tags that have
at least one association with at user.
Bonus geek points for the one who could improve on that by returning
the top 50 tags (i.e. the most associations with users) but don’t
return any that do not have at least one association with a user.
(Will happen if only 30 tags in DB and only 25 have associations)
To find the tags associated with a particular user, you could simple
do:
user = User.find(id) #or whatever parameter you’re using to find
it.
tags = user.tags
This way you will only get the tags associated with the user.
To get the top 50, maybe you could have a “tagged” field in your table,
that counts the times the user has been associated with a specific tag
(this is just an idea… I don’t the DB schema). If you have a field
like that, then you could do:
tags = user.tags.find(:all, :order =>“tagged DESC”)
I have a USER model and a TAG model and they have a has_and_belongs_to
relationships between them.
I want to Tag.find(:all) … but I only want to return tags that have
at least one association with at user.
I am assuming you are trying to do something like
Tag.find(:all, :include=>:user]
:include will generate a left outer join, which means tags without
users are returned as well. Try using a :join statement and make it an
INNER JOIN. You’ll want to read up on the naming scheme for table
aliases that AR uses, the Rdoc is under ActiveRecord::Associations.
Thanks everyone for you input. Sometimes SQL is just the nicest
solution. I found an almost perfect solution using Railsisms but it
just did not read as well as SQL. But I did wrap the SQL into a
method that I call from the controller so at least I am just upgrading
the dsl of my app.
I think inner join support should be added to the find method options.
I will gladly add it to the ActiveRecord Extensions if anyone else would
find this useful.
What are your thoughts on the API of the following to do an INNER JOIN?
Tag.find :all, :include=>[ :users_must_exist ]
OR
Tag.find :all, :finclude=>:users
The :finclude would be short for ‘force include’, meaning it would force
that records existed in both tables, hence the INNER JOIN. Thoughts?
Thanks everyone for you input. Sometimes SQL is just the nicest
solution. I found an almost perfect solution using Railsisms but it
just did not read as well as SQL. But I did wrap the SQL into a
method that I call from the controller so at least I am just upgrading
the dsl of my app.
ok i would use an association table like users_tags
so getting tags would be
User_tag.find(:all, :include => {:user, :tag})
getting the top x tags would be easy, just use a DISTINCT and order desc
on
first column
