Eric Czarny
2005-Oct-27 16:25 UTC
Re: has_many problems, can''t gather items in a todo list (urgent, working on a school project)
>> I am trying to create a todo list application for class, but I am >> having trouble listing each item in a list. Currently I find the >> current list being viewed by id, then I attempt to gather each >> each item in the list with: @items = @list.find_all_in_items(). >> When I try to load the page though I get the following exception: >> >> Unknown column ''items.list_id'' in ''where clause'': SELECT * FROM >> items WHERE items.list_id = 1 ORDER BY created_at DESC >> >> I have NO idea why Rails is looking for an the list_id in the >> items table. I made extra sure my models were correct, and each >> relationship looks fine. Any ideas? > > What do your relations look like? > Also, what do the has_many and belongs_to relationships look like?Sorry for the delay, I believe I submitted this before, but Mail screwed me up by entering the wrong subject... I have list.rb and item.rb. The first has a has_many relationship with items. The second belongs to list. The sources are below: class Item < ActiveRecord::Base belongs_to :list belongs_to :author end class List < ActiveRecord::Base has_many :items, :dependent => true, :order => ''created_at DESC'' belongs_to :author validates_presence_of :title validates_uniqueness_of :title end Here is the SQL schema: CREATE TABLE `authors` ( `id` INT NOT NULL auto_increment, `name` VARCHAR(32) DEFAULT NULL, `username` VARCHAR(32) DEFAULT NULL, `password` VARCHAR(32) DEFAULT NULL, `address` VARCHAR(64) DEFAULT NULL, `created_at` TIMESTAMP NOT NULL, `updated_at` TIMESTAMP NOT NULL, PRIMARY KEY (`id`) ) ENGINE = MyISAM; CREATE TABLE `authors_lists` ( `author_id` INT NOT NULL, `list_id` INT NOT NULL ) ENGINE = MyISAM; CREATE TABLE `lists` ( `id` INT NOT NULL auto_increment, `title` TEXT, `description` TEXT, `created_at` TIMESTAMP NOT NULL, `updated_at` TIMESTAMP NOT NULL, PRIMARY KEY (`id`) ) ENGINE = MyISAM; CREATE TABLE `items` ( `id` INT NOT NULL auto_increment, `name` VARCHAR(16) DEFAULT NULL, `completed` BOOLEAN DEFAULT ''0'', `created_at` TIMESTAMP NOT NULL, `updated_at` TIMESTAMP NOT NULL, PRIMARY KEY (`id`) ) ENGINE = MyISAM; CREATE TABLE `sessions` ( `id` INT NOT NULL auto_increment, `sessid` CHAR(32) NOT NULL, `data` TEXT, `created_at` TIMESTAMP NOT NULL, `updated_at` TIMESTAMP NOT NULL, PRIMARY KEY (`id`), INDEX session_index (sessid) ) ENGINE = MyISAM; Anything look weird? Thanks for the help! Cheers, Eric Czarny
Michael Smedberg
2005-Oct-27 16:55 UTC
Re: Re: has_many problems, can''t gather items in a todo list (urgent, working on a school project)
If I understand correctly, I think you DO want to have a column called "list_id" in your items table. By saying class List < ActiveRecord::Base has_many :items # etc. you''re saying that a list is associated with a bunch of items, and there has to be some record of which items belong to which list. The convention with "has_many" is that there''s a foreign key in the remote table. So there should be a column in the items table that says which list each item belongs to. That''s why you''re getting the SQL error- Rails is trying to figure out which items belong to a particular list, and is doing so by looking for a "list_id" column in the items table. I''m not sure if this is what you want- if you WANT each item to be associated with a particular list, then you should add a list_id column to the items table. If not, then you probably want to remove the has_many relationship. On 10/27/05, Eric Czarny <eczarny-NMPvQhWyYO43uJ6cTUYseUEOCMrvLtNR@public.gmane.org> wrote:> > >> I am trying to create a todo list application for class, but I am > >> having trouble listing each item in a list. Currently I find the > >> current list being viewed by id, then I attempt to gather each > >> each item in the list with: @items = @list.find_all_in_items(). > >> When I try to load the page though I get the following exception: > >> > >> Unknown column ''items.list_id'' in ''where clause'': SELECT * FROM > >> items WHERE items.list_id = 1 ORDER BY created_at DESC > >> > >> I have NO idea why Rails is looking for an the list_id in the > >> items table. I made extra sure my models were correct, and each > >> relationship looks fine. Any ideas? > > > > What do your relations look like? > > Also, what do the has_many and belongs_to relationships look like? > > Sorry for the delay, I believe I submitted this before, but Mail > screwed me up by entering the wrong subject... > > I have list.rb and item.rb. The first has a has_many relationship > with items. The second belongs to list. The sources are below: > > class Item < ActiveRecord::Base > belongs_to :list > belongs_to :author > > end > > class List < ActiveRecord::Base > has_many :items, :dependent => true, :order => ''created_at DESC'' > belongs_to :author > > validates_presence_of :title > validates_uniqueness_of :title > > end > > Here is the SQL schema: > > CREATE TABLE `authors` ( > `id` INT NOT NULL auto_increment, > `name` VARCHAR(32) DEFAULT NULL, > `username` VARCHAR(32) DEFAULT NULL, > `password` VARCHAR(32) DEFAULT NULL, > `address` VARCHAR(64) DEFAULT NULL, > `created_at` TIMESTAMP NOT NULL, > `updated_at` TIMESTAMP NOT NULL, > PRIMARY KEY (`id`) > ) ENGINE = MyISAM; > > CREATE TABLE `authors_lists` ( > `author_id` INT NOT NULL, > `list_id` INT NOT NULL > ) ENGINE = MyISAM; > > CREATE TABLE `lists` ( > `id` INT NOT NULL auto_increment, > `title` TEXT, > `description` TEXT, > `created_at` TIMESTAMP NOT NULL, > `updated_at` TIMESTAMP NOT NULL, > PRIMARY KEY (`id`) > ) ENGINE = MyISAM; > > CREATE TABLE `items` ( > `id` INT NOT NULL auto_increment, > `name` VARCHAR(16) DEFAULT NULL, > `completed` BOOLEAN DEFAULT ''0'', > `created_at` TIMESTAMP NOT NULL, > `updated_at` TIMESTAMP NOT NULL, > PRIMARY KEY (`id`) > ) ENGINE = MyISAM; > > CREATE TABLE `sessions` ( > `id` INT NOT NULL auto_increment, > `sessid` CHAR(32) NOT NULL, > `data` TEXT, > `created_at` TIMESTAMP NOT NULL, > `updated_at` TIMESTAMP NOT NULL, > PRIMARY KEY (`id`), > INDEX session_index (sessid) > ) ENGINE = MyISAM; > > Anything look weird? Thanks for the help! > > Cheers, > Eric Czarny > _______________________________________________ > Rails mailing list > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > http://lists.rubyonrails.org/mailman/listinfo/rails >_______________________________________________ Rails mailing list Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org http://lists.rubyonrails.org/mailman/listinfo/rails
Chris Hall
2005-Oct-27 16:55 UTC
Re: Re: has_many problems, can''t gather items in a todo list (urgent, working on a school project)
your relationships are all out of sorts. your model relationships don''t match up to your db schema. examples: Item belongs_to Author and List, yet in your db schema, you don''t have an author_id or list_id column column in your items table List belongs_to Author, yet your db schema reflects a HABTM relationship between the two. please describe in words what you want your model relationships to reflect and we can try and help further. going by your db schema, your models would look as such, but I doubt this is what you want. you probably want a combination of the two. model Author < ActiveRecord::Base has_and_belongs_to_many :lists end model List < ActiveRecord::Base has_and_belongs_to_many :authors end model Item < ActiveRecord::Base # no associations end Chris On 10/27/05, Eric Czarny <eczarny-NMPvQhWyYO43uJ6cTUYseUEOCMrvLtNR@public.gmane.org> wrote:> > >> I am trying to create a todo list application for class, but I am > >> having trouble listing each item in a list. Currently I find the > >> current list being viewed by id, then I attempt to gather each > >> each item in the list with: @items = @list.find_all_in_items(). > >> When I try to load the page though I get the following exception: > >> > >> Unknown column ''items.list_id'' in ''where clause'': SELECT * FROM > >> items WHERE items.list_id = 1 ORDER BY created_at DESC > >> > >> I have NO idea why Rails is looking for an the list_id in the > >> items table. I made extra sure my models were correct, and each > >> relationship looks fine. Any ideas? > > > > What do your relations look like? > > Also, what do the has_many and belongs_to relationships look like? > > Sorry for the delay, I believe I submitted this before, but Mail > screwed me up by entering the wrong subject... > > I have list.rb and item.rb. The first has a has_many relationship > with items. The second belongs to list. The sources are below: > > class Item < ActiveRecord::Base > belongs_to :list > belongs_to :author > > end > > class List < ActiveRecord::Base > has_many :items, :dependent => true, :order => ''created_at DESC'' > belongs_to :author > > validates_presence_of :title > validates_uniqueness_of :title > > end > > Here is the SQL schema: > > CREATE TABLE `authors` ( > `id` INT NOT NULL auto_increment, > `name` VARCHAR(32) DEFAULT NULL, > `username` VARCHAR(32) DEFAULT NULL, > `password` VARCHAR(32) DEFAULT NULL, > `address` VARCHAR(64) DEFAULT NULL, > `created_at` TIMESTAMP NOT NULL, > `updated_at` TIMESTAMP NOT NULL, > PRIMARY KEY (`id`) > ) ENGINE = MyISAM; > > CREATE TABLE `authors_lists` ( > `author_id` INT NOT NULL, > `list_id` INT NOT NULL > ) ENGINE = MyISAM; > > CREATE TABLE `lists` ( > `id` INT NOT NULL auto_increment, > `title` TEXT, > `description` TEXT, > `created_at` TIMESTAMP NOT NULL, > `updated_at` TIMESTAMP NOT NULL, > PRIMARY KEY (`id`) > ) ENGINE = MyISAM; > > CREATE TABLE `items` ( > `id` INT NOT NULL auto_increment, > `name` VARCHAR(16) DEFAULT NULL, > `completed` BOOLEAN DEFAULT ''0'', > `created_at` TIMESTAMP NOT NULL, > `updated_at` TIMESTAMP NOT NULL, > PRIMARY KEY (`id`) > ) ENGINE = MyISAM; > > CREATE TABLE `sessions` ( > `id` INT NOT NULL auto_increment, > `sessid` CHAR(32) NOT NULL, > `data` TEXT, > `created_at` TIMESTAMP NOT NULL, > `updated_at` TIMESTAMP NOT NULL, > PRIMARY KEY (`id`), > INDEX session_index (sessid) > ) ENGINE = MyISAM; > > Anything look weird? Thanks for the help! > > Cheers, > Eric Czarny > _______________________________________________ > Rails mailing list > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > http://lists.rubyonrails.org/mailman/listinfo/rails >_______________________________________________ Rails mailing list Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org http://lists.rubyonrails.org/mailman/listinfo/rails