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’]
On Tue, Sep 23, 2008 at 2:00 PM, ressister [email protected] wrote:
syntax is wrong or both? An entry should only be able to be a part of
has_many :entry_categories
I can get all the entries grouped properly like so…
Thanks,
-Alex
–
ANDRES RODRIGUEZ E
Electronic Engineer
Software Developer
IT Consultant
US Phone: (305) 853-8356
Primary Mobile: +57-300-2006186
Secondary Mobile: +57-314-7939859
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
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
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.
On Tue, Sep 23, 2008 at 4:05 PM, ressister [email protected] wrote:
An entry can only belong to a category right? If this is the case you
end
category_id)
belongs_to :entry
has_one :entry_category
categories, the query doesn’t work which makes me think i have a
Software Developer
IT Consultant
US Phone: (305) 853-8356
Primary Mobile: +57-300-2006186
Secondary Mobile: +57-314-7939859
–
ANDRES RODRIGUEZ E
Electronic Engineer
Software Developer
IT Consultant
US Phone: (305) 853-8356
Primary Mobile: +57-300-2006186
Secondary Mobile: +57-314-7939859
On 24 Sep 2008, at 10:46, Frederick C. wrote:
…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
On 24 Sep 2008, at 00:44, ressister wrote:
[‘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