Database query question

I'm new to rails and I'm also new to database access so hopefully this
will be easy to answer, and hopefully I can explain it well enough to be
understood.

I've got ActiveRecord objects that all relate to eachother. User and
Game have a many to many relationship connected via GameCollection.
GameCollection also has various attributes such as percentComplete.
Every user has a certain number of points. What I am trying to do is
increment the points for each user that has a game with a certain id and
percentComplete = 100. I'm looking to do this by calling
GameCollection.update or User.update. I'd be curious how I might be
able to do it either way. I just can't seem to figure out the correct
syntax...

Any ideas? Is this doable? I've previously been doing it manually
using ruby code, but that is really slow and I want to take advantage of
the power of mysql updates.

If you cannot make this perform quicker in ruby language, I am afraid
that ROR will not be helpful in that way.

ROR is basically a framework for quick development and maintenance.
To improve the performance you need to either do it via backend or tweaking
the ROR in a complex way to perform better. The requirement you are
asking can be done in fewer lines of code in ROR. Check Active
Record tutorials and examples in the search engines.

I'm new to rails and I'm also new to database access so hopefully this
will be easy to answer, and hopefully I can explain it well enough to be
understood.

I've got ActiveRecord objects that all relate to eachother. User and
Game have a many to many relationship connected via GameCollection.
GameCollection also has various attributes such as percentComplete.
Every user has a certain number of points. What I am trying to do is
increment the points for each user that has a game with a certain id and
percentComplete = 100. I'm looking to do this by calling
GameCollection.update or User.update. I'd be curious how I might be
able to do it either way. I just can't seem to figure out the correct
syntax...

For something like this, if you want to do it in a single query then
you'll probably just have to write some sql to do it, perhaps
something like

update users
inner join game_collections on games_collections.user_id = users.id
set points = points + 2
where game_id = 23 and percentComplete = 100

if a user had multiple rows in game_collections then this might do
funny stuff. Make sure you think about the edge cases and test for
them!

Fred

Amala Singh wrote:

If you cannot make this perform quicker in ruby language, I am afraid
that ROR will not be helpful in that way.

ROR is basically a framework for quick development and maintenance.
To improve the performance you need to either do it via backend or
tweaking
the ROR in a complex way to perform better. The requirement you are
asking can be done in fewer lines of code in ROR. Check Active
Record tutorials and examples in the search engines.

GameCollection.update or User.update. I'd be curious how I might be
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@googlegroups.com.
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe@googlegroups.com<rubyonrails-talk%2Bunsubscribe@googlegroups.com>
.
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.

--
--
Nandri(Thanks in Tamil),
Amala Singh

Perhaps I'm not explaining myself properly. I want to take advantage of
mysql queries to perform this faster instead of doing all the checks in
the controller itself. That make more sense? I'm looking for the
actual syntax of the ActiveRecord.update call and I've been unable to
find any examples that do something similar.

Patrick Clas wrote:

I'm new to rails and I'm also new to database access so hopefully this
will be easy to answer, and hopefully I can explain it well enough to be
understood.

I've got ActiveRecord objects that all relate to eachother. User and
Game have a many to many relationship connected via GameCollection.
GameCollection also has various attributes such as percentComplete.
Every user has a certain number of points. What I am trying to do is
increment the points for each user that has a game with a certain id and
percentComplete = 100. I'm looking to do this by calling
GameCollection.update or User.update. I'd be curious how I might be
able to do it either way. I just can't seem to figure out the correct
syntax...

Any ideas? Is this doable? I've previously been doing it manually
using ruby code, but that is really slow and I want to take advantage of
the power of mysql updates.

Are you trying to call a SQL UPDATE statement that updates multiple
records? If so, then that's not possible with straight ActiveRecord
AFAIK. You have three options, roughly in descending order of
preferability:

* Use the ar-extensions plugin, which does bulk updates.

* Write the SQL yourself (probably not recommended, though you should at
least understand the syntax)

* Replace ActiveRecord with another ORM like Sequel, which does this
natively.

Best,

I thought this was possible using the update_all method of
ActiveRecord...no? If not, then I will just write native sql commands
instead.

Patrick Clas wrote:

I thought this was possible using the update_all method of
ActiveRecord...no?

You're quite right. I had forgotten about that method.

If not, then I will just write native sql commands
instead.

No, use something like ar-extensions if update_all won't do the trick.
If you're writing SQL for a task this simple, then something is wrong.

Best,

Marnen Laibow-Koser wrote:

Patrick Clas wrote:

I thought this was possible using the update_all method of
ActiveRecord...no?

You're quite right. I had forgotten about that method.

If not, then I will just write native sql commands
instead.

No, use something like ar-extensions if update_all won't do the trick.
If you're writing SQL for a task this simple, then something is wrong.

Best,
--
Marnen Laibow-Koser
http://www.marnen.org
marnen@marnen.org

Well the task isn't as simple as it seems. I have it working fine now
with SQL, rails just seems to be lacking a bit in this area. Of course
I realize they have to be since they are supporting more than just
mysql.

Patrick Clas wrote:
[...]

Well the task isn't as simple as it seems. I have it working fine now
with SQL, rails just seems to be lacking a bit in this area.

So try ar-extensions or something.

Of course
I realize they have to be since they are supporting more than just
mysql.

Irrelevant. Any SQL database can do bulk updates.

What exactly are you trying to do that you can't do through Rails?

Best,

Marnen Laibow-Koser wrote:

Patrick Clas wrote:
[...]

Well the task isn't as simple as it seems. I have it working fine now
with SQL, rails just seems to be lacking a bit in this area.

So try ar-extensions or something.

Of course
I realize they have to be since they are supporting more than just
mysql.

Irrelevant. Any SQL database can do bulk updates.

What exactly are you trying to do that you can't do through Rails?

Best,
--
Marnen Laibow-Koser
http://www.marnen.org
marnen@marnen.org

Any SQL database can do bulk updates, but mysql makes it easier by
allowing table joins during updates. I suppose I could try to redesign
my statement, but I don't see the need since I own the server I'm
running on and mysql is always going to be there. I guess I'm not a
rails purist so I don't have any strong objection to putting SQL in my
code. Here is the statement I now have in my code:

UPDATE users, game_collections SET users.points = users.points +
#{point_diff},
    users.adjusted_points = users.points *
users.complete/(users.complete + users.incomplete) WHERE
game_collections.user_id = users.id AND game_collections.game_id =
#{game.id} AND game_collections.percentComplete = 100

Patrick Clas wrote:
[...]

Any SQL database can do bulk updates, but mysql makes it easier by
allowing table joins during updates. I suppose I could try to redesign
my statement, but I don't see the need since I own the server I'm
running on and mysql is always going to be there.

That doesn't mean you're always going to be using MySQL (PostgreSQL is a
much better DB, for example, and you may want to switch at some point).
Don't lock yourself in unnecessarily.

I guess I'm not a
rails purist so I don't have any strong objection to putting SQL in my
code.

It has nothing to do with Rails purism. Since Rails will abstract
things for you, let it, and don't tie yourself to one database by
writing SQL! (Or if you must, make sure it's database-neutral SQL.)

Here is the statement I now have in my code:

UPDATE users, game_collections SET users.points = users.points +
#{point_diff},
    users.adjusted_points = users.points *
users.complete/(users.complete + users.incomplete) WHERE
game_collections.user_id = users.id AND game_collections.game_id =
#{game.id} AND game_collections.percentComplete = 100

Are point_diff and game.id coming from any sort of user input or form
submissions? If so, you've just left yourself wide open to SQL
injection attacks.

In any case, I think you *really* want ar-extensions here. Keep your
app as DB-independent as possible.

Best,

Are point_diff and game.id coming from any sort of user input or form
submissions? If so, you've just left yourself wide open to SQL
injection attacks.

Definitely not, thanks for your help.