Need Help With Find(:all) and conditions

rubyonrails-talk
1

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:

2

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

3

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

4

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

5

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

6

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:

7

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

8

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