Model association help...

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

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’]

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

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.

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

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

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

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