Need Help With Find(:all) and conditions

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. :slight_smile:
(Will happen if only 30 tags in DB and only 25 have associations)

Thanks guys and gals :slight_smile:

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")

Hope this can help.

Fernanda Aispuro

I missed something...
To limit the number of results to 50, just add:
         :limit => 50
after the :order key.

Could you change the habtm to a has_many :through and set up counter caches for the users and tags?

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.

Cheers,
Max

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. :slight_smile:

John Kopanas wrote:
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?

How about:

Tag.find :all, :must_include => :users

Jason Anderson
thenewjhp@gmail.com

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