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 --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
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 <ressister-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> > 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 > > >-- ANDRES RODRIGUEZ E Electronic Engineer Software Developer IT Consultant US Phone: (305) 853-8356 Primary Mobile: +57-300-2006186 Secondary Mobile: +57-314-7939859 --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
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 On Sep 23, 4:11 pm, "Andres Rodriguez" <andres...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> 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 <ressis...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote: > > > 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 > > -- > ANDRES RODRIGUEZ E > Electronic Engineer > Software Developer > IT Consultant > > US Phone: (305) 853-8356 > Primary Mobile: +57-300-2006186 > Secondary Mobile: +57-314-7939859--~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe@googlegroups.com For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
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 <ressister-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> > 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 > > On Sep 23, 4:11 pm, "Andres Rodriguez" <andres...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote: > > 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 <ressis...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote: > > > > > 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 > > > > -- > > ANDRES RODRIGUEZ E > > Electronic Engineer > > 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 --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
> > > 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. --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
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 On Sep 23, 7:10 pm, heimdull <fre...-RCI/mp9mI1I6GGFevw1D/A@public.gmane.org> wrote:> > > 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.--~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe@googlegroups.com For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
On 24 Sep 2008, at 00:44, ressister wrote:> > 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> > > It doesn''t seem like my model setup is using the catogory_entries > table successfully as a join table. Thoughts? > > -A > > On Sep 23, 7:10 pm, heimdull <fre...-RCI/mp9mI1I6GGFevw1D/A@public.gmane.org> wrote: >>>> 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. > >--~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
On 24 Sep 2008, at 10:46, Frederick Cheung wrote:> > On 24 Sep 2008, at 00:44, ressister wrote: > >> >> 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> Fred >> >> >> It doesn''t seem like my model setup is using the catogory_entries >> table successfully as a join table. Thoughts? >> >> -A >> >> On Sep 23, 7:10 pm, heimdull <fre...-RCI/mp9mI1I6GGFevw1D/A@public.gmane.org> wrote: >>>>> 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. >> >> >--~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---