Prasanna kumar Nagasamudram
2006-Nov-02 10:42 UTC
Multiple items in the where clause while updating...
Hi The following is a database table named friends. +-----+------+------+-------+ | sid | id | fid | ftype | +-----+------+------+-------+ | 30 | 1 | 2 | F | | 31 | 1 | 3 | R | | 32 | 3 | 2 | F | | 33 | 3 | 4 | F | +-----+------+------+-------+ I want to update the ftype field based on id and fid. I want to achive the following. Update friends set ftype=''F'' where id=1 and fid=3 Im using the following code... ==========f = Friend.find(:first, :conditions => "id = 1 and fid = 3") f.ftype=''F'' f.save ========== This errors out because the sql satement that gets generated will be UPDATE friends SET `sid` = 31, `ftype` = ''F'', `fid` = 3 WHERE id = 1 As you can see the where clause always takes just the id attribute and ignores others which will try to update muliple rows. Is there a way to handle this situation? Thanks Prasanna -- Posted via http://www.ruby-forum.com/. --~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---
Frederick Cheung
2006-Nov-02 10:49 UTC
Re: Multiple items in the where clause while updating...
Rails (or rather activerecord) assumes by default that tables have a primary key called id, whereas your id is obviously not that. it looks like sid is your primary key, so you''d need to stick set_primary_key "sid" in the declaration of your class Fred -- Posted via http://www.ruby-forum.com/. --~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---
Prasanna kumar Nagasamudram
2006-Nov-02 10:57 UTC
Re: Multiple items in the where clause while updating...
Gr8!!!!!!!!! that works, I spent the whole day trying various combinations. Thanks a lot Fred. Thanks Prasanna -- Posted via http://www.ruby-forum.com/. --~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---
Prasanna kumar Nagasamudram
2006-Nov-03 06:48 UTC
Re: Multiple items in the where clause while updating...
I have a similar problem...
I have the following table name friends
+-----+------+------+-------+
| sid | id | fid | ftype |
+-----+------+------+-------+
| 3 | 1 | 5 | N |
+-----+------+------+-------+
| 4 | 1 | 2 | N |
+-----+------+------+-------+
| 5 | 2 | 3 | N |
+-----+------+------+-------+
where sid is the primary key and I have achieved thsi by adding
set_primary_key "sid" to the class declaration.
Now I''m trying to add a new row to the above table with the following
code.
f = Friend.new
f.id=1
f.fid=4
f.ftype=''N''
f.save
The row added wil be of the form
+-----+------+------+-------+
| sid | id | fid | ftype |
+-----+------+------+-------+
| 1 | NULL | 4 | N |
+-----+------+------+-------+
This is because
INSERT INTO friends (`ftype`, `fid`, `sid`, `id`)
VALUES(''N'',4, 1, NULL)
The sid field should be automatically generated since it is a
auto_increment field.
it seems that the statement
f.id=1
is not taking effect.
How do I handle this situation.
Thanks
Prasanna
--
Posted via http://www.ruby-forum.com/.
--~--~---------~--~----~------------~-------~--~----~
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
-~----------~----~----~----~------~----~------~--~---
Prasanna kumar Nagasamudram wrote:> I have a similar problem... > f = Friend.new > f.id=1 > f.fid=4 > f.ftype=''N'' > f.save > > The row added wil be of the form > > +-----+------+------+-------+ > | sid | id | fid | ftype | > +-----+------+------+-------+ > | 1 | NULL | 4 | N | > +-----+------+------+-------+I believe ActiveRecord will always assume that your primary key is referenced by "id". Even if the column name is different, the accessor method is still id. If you are writing an application from scratch in rails, you should have no reason to step outside the rails conventions. So your primary key should be named "id". Although you can make it work, you will have headaches if you continue down this path. That said, you might try it this way: f = Friend.new f[:id] = 1 f.fid = 4 f.ftype = ''N'' f.save using object[:column_name] maps directly to the named attribute rather than being proxied through a method generated by active record. -- Posted via http://www.ruby-forum.com/. --~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---
Prasanna kumar Nagasamudram
2006-Nov-03 07:29 UTC
Re: Multiple items in the where clause while updating...
Thanks Alex.... I trying to follow the rails convention of having id as the primary key. But my requirement is to have a field named id which should not be unique I have a table named users as follows +----+----------+-----------------------------+----------------+ | id | login | name | location | +----+----------+-----------------------------+----------------+ | 1 | praskuma | Prasanna Kumar | Bangalore | | 2 | nagasapk | Nagasamudram Prasanna Kumar | Bangalore | | 3 | mannesp | Siva | Andhra Pradesh | | 4 | puranikp | Prashant Puranik | Karnataka | | 5 | ankamrb | Ankam Ramesh Babu | Badrachalam | +----+----------+-----------------------------+----------------+ and friends table as follows +-----+------+------+-------+ | sid | id | fid | ftype | +-----+------+------+-------+ | 1 | 1 | 4 | N | +-----+------+------+-------+ | 2 | 1 | 3 | N | +-----+------+------+-------+ | 3 | 2 | 1 | N | +-----+------+------+-------+ | 4 | 2 | 3 | N | +-----+------+------+-------+ id and fid above are foriegn keys pointing to the id field of the users table. and id field in the friends table can have repeaded values, so i cannot have id as the primary key in the friends table. I just kept sid field as a primary key just for future use. If you can think of an alternative way (reorganizing tables), please let me know. Thanks a lot for solving my earlier issue Prasanna -- Posted via http://www.ruby-forum.com/. --~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---
Rob Biedenharn
2006-Nov-04 12:01 UTC
Re: Multiple items in the where clause while updating...
On Nov 3, 2006, at 1:48 AM, Prasanna kumar Nagasamudram wrote:> > I have a similar problem... > > I have the following table name friends > ... > where sid is the primary key and I have achieved thsi by adding > set_primary_key "sid" to the class declaration. > > ... > it seems that the statement > f.id=1 > is not taking effect. > > How do I handle this situation. > > Thanks > PrasannaYour problem is that f.id=1 is "set the primary key to 1". See this doc: http://api.rubyonrails.org/classes/ActiveRecord/ Base.html#M000901 I don''t know how you "fix" this in your situation, but knowing the cause ought to help. -Rob Rob Biedenharn http://agileconsultingllc.com Rob-xa9cJyRlE0mWcWVYNo9pwxS2lgjeYSpx@public.gmane.org --~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---