Model association help...

rubyonrails-talk
1

I'm having trouble with three models that need to play nice together:

- entry <- stores diary entries - entry_category <- links a diary entry to a category (entry_id, category_id) - category <- list of category names and attributes

I want to be able to :include => :categories when I perform a find, but I'm not sure if I have the model associations wrong or if my find syntax is wrong or both? An entry should only be able to be a part of one category.

Here are the models:

class EntryCategory < ActiveRecord::Base   belongs_to :entry   belongs_to :category end

class Category < ActiveRecord::Base   has_many :entry_categories   has_many :entries, :through => :entry_categories end

class Entry < ActiveRecord::Base   belongs_to :user   has_one :entry_category   has_one :category, :through => :entry_category end

I want to sum the price of all entries and include the category name. I can get all the entries grouped properly like so....

@entries = Entry.sum(:price, :conditions => ['user_id = 1'], :include => :entry_category, :group => 'entry_categories.category_id')

...how do I also get the category.name? If I try and include categories, the query doesn't work which makes me think i have a problem with my model associations.

Any help would be greatly appreciated.

Thanks, -Alex

2

An entry can only belong to a category right? If this is the case you don’t need this many-to-many association. You just have to:

class Category < ActiveRecord::Base

has_many :entries end

class Entry < ActiveRecord::Base

belongs_to :user belongs_to :category

end

This will let you do:

my_entry.category.name my_category.entry

If you want to keep working with that model then you have an error in your query. Using :include in finders will modify the structure of your SQL query. So you have to refer to table names in conditions like this:

:conditions => [‘users.user_id = 1’]

3

Hi Andres, thanks for the reply. I tried your suggested setup, but it didn't work. I then tried a simpler query with my existing model setup (which you see above) which did work...

@first = Entry.find(:first, :include => :category) @first.category.name

Both those work which is great. However, if i try my orignial query...

@entries = Entry.sum(:price, :include => :category, :conditions => ['user_id = 1'], :group => 'categories.id')

I get this...

ActiveRecord::StatementInvalid: Mysql::Error: Unknown column 'categories.entry_id' in 'on clause': SELECT sum(`entries`.price) AS sum_price, categories.id AS categories_id FROM `entries` LEFT OUTER JOIN `categories` ON categories.entry_id = entries.id WHERE (user_id = 1) GROUP BY categories.id

Categories doesn't have an entry_id, it doesn't seem to be picking up entry_category as the join table, though based on its naming, it isn't a traditional join table otherwise it would be called category_entry.

Any thoughts? Thanks again for your reply.

-A

4

I think it has to be with the type of relationships you set up. As I told you, you just need a one-to-many relationship reading your code. I tried your find with a many-to-many relationship and it worked for me.

5

> Categories doesn't have an entry_id, it doesn't seem to be picking up > entry_category as the join table, though based on its naming, it isn't > a traditional join table otherwise it would be called > category_entry.

With the setup that Andres suggested you need a entry_id in the categories table.

6

Right Heimdull, thanks. So my model setup is correct for what I'm trying to achieve you think? Any idea why this works:

@first = Entry.find(:first, :include => :category) @first.category.name

...and this does not?

@entries = Entry.sum(:price, :include => :category, :conditions => ['user_id = 1'], :group => 'categories.id')

It doesn't seem like my model setup is using the catogory_entries table successfully as a join table. Thoughts?

-A

7

Right Heimdull, thanks. So my model setup is correct for what I'm trying to achieve you think? Any idea why this works:

@first = Entry.find(:first, :include => :category) @first.category.name

...and this does not?

@entries = Entry.sum(:price, :include => :category, :conditions => ['user_id = 1'], :group => 'categories.id')

Because they are going down 2 different code paths. One is loading the
entry and then the category. The is trying to generate the appropriate
join statement. It just so happens that the latter is bust in rails
2.1 for has_one :through associations (the former is also broken but
not in a noticeable way)

Fred

8

Right Heimdull, thanks. So my model setup is correct for what I'm trying to achieve you think? Any idea why this works:

@first = Entry.find(:first, :include => :category) @first.category.name

...and this does not?

@entries = Entry.sum(:price, :include => :category, :conditions => ['user_id = 1'], :group => 'categories.id')

Because they are going down 2 different code paths. One is loading
the entry and then the category. The is trying to generate the
appropriate join statement. It just so happens that the latter is
bust in rails 2.1 for has_one :through associations (the former is
also broken but not in a noticeable way)

Forgot to add, 2.1.1 should fix this

Fred