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. -- 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-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.
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. On 20 April 2010 15:19, Patrick Clas <lists-fsXkhYbjdPsEEoCn2XhGlw@public.gmane.org> 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. > -- > 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-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org > To unsubscribe from this group, send email to > rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org<rubyonrails-talk%2Bunsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org> > . > For more options, visit this group at > http://groups.google.com/group/rubyonrails-talk?hl=en. > >-- -- Nandri(Thanks in Tamil), Amala Singh -- 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-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.
On Apr 20, 3:19 pm, Patrick Clas <li...-fsXkhYbjdPsEEoCn2XhGlw@public.gmane.org> 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... >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> 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. > -- > Posted viahttp://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-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org > To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe@googlegroups.com. > For more options, visit this group athttp://groups.google.com/group/rubyonrails-talk?hl=en.-- 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-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe@googlegroups.com. For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.
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. > > On 20 April 2010 15:19, Patrick Clas <lists-fsXkhYbjdPsEEoCn2XhGlw@public.gmane.org> wrote: > >> 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-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org >> To unsubscribe from this group, send email to >> rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org<rubyonrails-talk%2Bunsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org> >> . >> For more options, visit this group at >> http://groups.google.com/group/rubyonrails-talk?hl=en. >> >> > > > -- > -- > Nandri(Thanks in Tamil), > Amala SinghPerhaps 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. -- 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-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.
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, -- Marnen Laibow-Koser http://www.marnen.org marnen-sbuyVjPbboAdnm+yROfE0A@public.gmane.org -- 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-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.
I thought this was possible using the update_all method of ActiveRecord...no? If not, then I will just write native sql commands instead. -- 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-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.
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-sbuyVjPbboAdnm+yROfE0A@public.gmane.org -- 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-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.
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-sbuyVjPbboAdnm+yROfE0A@public.gmane.orgWell 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. -- 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-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.
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-sbuyVjPbboAdnm+yROfE0A@public.gmane.org -- 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-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.
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-sbuyVjPbboAdnm+yROfE0A@public.gmane.orgAny 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 -- 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-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.
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 = 100Are 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, -- Marnen Laibow-Koser http://www.marnen.org marnen-sbuyVjPbboAdnm+yROfE0A@public.gmane.org -- 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-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.
> 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. -- 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-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.