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

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

Gr8!!! that works, I spent the whole day trying various
combinations.
Thanks a lot Fred.

Thanks
Prasanna

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

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.

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

On Nov 3, 2006, at 1:48 AM, Prasanna kumar Nagasamudram wrote:

    f.id=1

is not taking effect.

How do I handle this situation.

Thanks
Prasanna

Your 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 B. http://agileconsultingllc.com
[email protected]

This forum is not affiliated to the Ruby language, Ruby on Rails framework, nor any Ruby applications discussed here.

| Privacy Policy | Terms of Service | Remote Ruby Jobs