I have a table that has no primary key... it is a cross-ref table with some extra data stored in it. I''d prefer not to use a primary key because the size of this table will grow so rapidly that I''m afraid, over time, I will exceed the sequence. When I create a new row in the table, and persist it, it works fine. When I try to update the same row, it throws an error: Unknown column ''id'' in ''where clause'': UPDATE order_details SET `order_id` = 5, `quantity` = 2, `item_id` = 1, `internal_cost_at_sale` = NULL, `customer_price_at_sale` = NULL WHERE id = NULL Of course id is an unknown column... it''s not even supposed to be doing anything with it. Is there some way I can tell this not to do what it''s doing? --~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---
Luke Ivers <technodolt-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> I have a table that has no primary key... it is a cross-ref table with some > extra data stored in it. > > Unknown column ''id'' in ''where clause'': UPDATE order_details SET > `order_id` = 5, `quantity` = 2, `item_id` = 1, `internal_cost_at_sale` > = NULL, `customer_price_at_sale` = NULL WHERE id = NULLWell, you should still have a primary key, to help the database pull your rows out quickly. I''m guessing you''d want PRIMARY KEY (order_id, item_id) but you may want (item_id, order_id) - it depends on which one you''re looking up more often. Either way, it looks like what you want is a WHERE clause like WHERE (order_id, item_id) = (5, 1) You could probably pull this off by defining your own private "update" method in your model... The ActiveRecord default method is: private def update connection.update( "UPDATE #{self.class.table_name} " + "SET #{quoted_comma_pair_list(connection, attributes_with_quotes(false))} " + "WHERE #{self.class.primary_key} = #{quote(id)}", "#{self.class.name} Update" ) return true end ... so just copy and paste that into your model and tweak it for your needs. :-) Cheers, Tyler --~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---
I basically knew I could do that, but sometimes you get burnt out and seeing someone else write something out is just what you need. Thanks :) On 3/14/07, Tyler MacDonald <google.com-oTuY4Vk9bUDG8MNy1oJpyw@public.gmane.org> wrote:> > > Luke Ivers <technodolt-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote: > > I have a table that has no primary key... it is a cross-ref table with > some > > extra data stored in it. > > > > Unknown column ''id'' in ''where clause'': UPDATE order_details SET > > `order_id` = 5, `quantity` = 2, `item_id` = 1, `internal_cost_at_sale` > > = NULL, `customer_price_at_sale` = NULL WHERE id = NULL > > Well, you should still have a primary key, to help the database pull > your > rows out quickly. I''m guessing you''d want > > PRIMARY KEY (order_id, item_id) > > but you may want (item_id, order_id) - it depends on which one you''re > looking up more often. Either way, it looks like what you want is a WHERE > clause like > > WHERE (order_id, item_id) = (5, 1) > > You could probably pull this off by defining your own private "update" > method in your model... The ActiveRecord default method is: > > private > def update > connection.update( > "UPDATE #{self.class.table_name} " + > "SET #{quoted_comma_pair_list(connection, > attributes_with_quotes(false))} " + > "WHERE #{self.class.primary_key} = #{quote(id)}", > "#{self.class.name} Update" > ) > > return true > end > > ... so just copy and paste that into your model and tweak it for your > needs. :-) > > Cheers, > Tyler > > > >--~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---
Luke Ivers wrote the following on 14.03.2007 17:28 :> I have a table that has no primary key... it is a cross-ref table with > some extra data stored in it. > > I''d prefer not to use a primary key because the size of this table > will grow so rapidly that I''m afraid, over time, I will exceed the > sequence.You can use a 64 bit integer type for the id column. Bigint is available for PostgreSQL and MySQL, SQLite adapts itself to the value but you don''t want SQLite for a database which can need more than 32bit for storing ids. Rails by default create simple int columns (INTEGER/INT) but you can create a hand-crafted migration that converts the 32bit column to a 64 bit one. Lionel --~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---