Topic: Convert MySQl query into Rails

Hello, I am trying to convert a MySQL query into a rails query. Right now, it only returns the player's name, but I also need an integer value for how many times he died.

SELECT player,SUM(amount) FROM Stats_death ORDER BY amount desc LIMIT 1

Any help is appreciated.

Re: Convert MySQl query into Rails

There's not enough information to answer your question.
What tables do you have and what are there relationships? How have you represented those relationships in your models?
You would typically write these as two separate scopes and chain them together when you need them using .order, .sum and the various finders.

What you want and what you need are too often not the same thing!
When your head is hurting from trying to solve a problem, stop standing on it. When you are the right way up you will see the problem differently and you just might find the solution.
(Quote by me 15th July 2009)

Re: Convert MySQl query into Rails

This is being pulled from one table. They also are not generated by rails, so I specified the table names. Does it matter in which order I chain them?

Re: Convert MySQl query into Rails

O.K., The fact that you have an amount column that you are both summing and ordering by is somewhat confusing. It implies that there would be more than one stats_death record for a player and that each record may contain an amount > 1

Given the above your SQL statement is ambiguous as to it's intention is possibly an indication of why you, and I am struggling to get you to the right solution.

Think about it, and see if you can put into English the actual result you are looking for rather than trying to figure out an ambiguous SQL statement conversion.

Order the data first, or use the group_by function, whichever best suits your needs.

Normally in a relational database you would have a players table linked to a stats_death table in a one to many relationship. If you had this then the approach would be slightly different, you would find the correct player record then sum the amount column for all the stats_death records fro that player which would look something like this
Player.find(some_id).stat_deaths.sum(:amount)

What you want and what you need are too often not the same thing!
When your head is hurting from trying to solve a problem, stop standing on it. When you are the right way up you will see the problem differently and you just might find the solution.
(Quote by me 15th July 2009)

Re: Convert MySQl query into Rails

I see what you are saying, and most of the tables I have worked with are like this. The table I am trying to pull data from has the following columns.

counter(int)|player(varchar)|cause(varchar)|amount(int)|entity(int).

The cause of death is the name of the mob, the entity is a boolean variable, 1 or 0, to signify that the cause of death was by a mob. I am trying to find the player who has died the most amount of times. It is as you said, and I am sorry for not including this, a person can be listed more than once. It seems they are listed once for each type of entity. For example

The top 2 rows of the db

1  specialrose  Skeleton  3  1
2  specialrose  Creeper  5  1

The code I listed works when I execute it in PHPMyAdmin, but it does not return the proper result set when I execute it in rails using Model.find_by_sql

Re: Convert MySQl query into Rails

Now things are clearer.
The SQL you have doesn't really work, it just looks like it works as it doesn't take care of a player appearing more than once and you are wanting to find the player that has died the most amount of times regardless of any other factors.

Take the following data set as an example

1  specialrose  Skeleton  3  1
2  specialrose  Creeper  5  1
3  b                     Skeleton  3  1
4  b                     Creeper  2  1
5  b                     Bee  2  1
6  b                     Dragon  2  1

In the above example specialrose has died 8 times an b has died 9 times. Your SQL would pull out special rose as being the person that has died the most which is clearly not the case (I think!!!! smile)

you actually need a bit of complex logic involving grouping and summing to get the data you need which could be quite computationally expensive.
Possibly this might work for you

StatsDeath.group(['player', 'amount']).order('sum(amount DESC'))

I think (untested) the above should work for you and you should just be able to tag on a .first if you just want the players name.

Make that into a method on the StatsDeath model (def most_died_player or something like) which will make it simpler to test and easier to refactor if you need.

I URGE you to write a unit test for this using similar data to the above.

What you want and what you need are too often not the same thing!
When your head is hurting from trying to solve a problem, stop standing on it. When you are the right way up you will see the problem differently and you just might find the solution.
(Quote by me 15th July 2009)

Re: Convert MySQl query into Rails

I actually posted the wrong SQL. I am converting this from PHP, so I got them confused. This returns the correct data set on my test db.

SELECT player,SUM(amount) FROM Stats_death GROUP BY player ORDER BY SUM(amount) desc LIMIT 1

I was able to get what you posted to work, but it is returning an ActiveRecord::Relation. It was before returning the classname followed by the data. I used to get the following back

#<Statskill player: "specialrose">]

now I am getting this

ActiveRecord::Relation:0x007fd5be189200&gt

Here is how I modified your code to work.

Statsdeath.select("player SUM(amount)").group('player').order("SUM(amount) DESC")

Putting a .first on the end results in an error, and using .limit(1) returns the relation, so nothing changed.

Re: Convert MySQl query into Rails

ActiveRecord::Relation (AKA ARel) is the desired result, it's all about lazy loading which enhances the ability seamlessly to do things like fragment caching, tagging an enumerable, such as .first, on to the end of an ARel result should then cause the actual query to be run.
The idea is that ruby is preparing the query to be run before it is actually needed. Using the ARel should then cause the SQL to be run.

wherever possible It is desirable to use ruby methods rather than direct SQL statements as Ruby has the arrogance (rightly so) to believe it will do a better job of :-

1) Optimising the query to be run as efficiently as possible.

2) Making sure that the SQL that is actually run is as close as possible to the correct syntax for the database used according the the adapter specified in the database.yml for the current environent (Development, Test, Production)

The idea is that this enables the developer to concentrate more on getting the desired results and less on worrying about the intended database that is going to be used.
It doesn't always work that way, but 99.9% of the time ruby is very very good at achieving both those objectives.

So given my solution that returns an ActiveRecord::Relation do you get the desired result when you tag on a .first? If not what is the error?

What you want and what you need are too often not the same thing!
When your head is hurting from trying to solve a problem, stop standing on it. When you are the right way up you will see the problem differently and you just might find the solution.
(Quote by me 15th July 2009)

Re: Convert MySQl query into Rails

I put it both in the controller and the view, both times it gave me the same error.

Controller

@mostKilledplayer = Statsdeath.select("player SUM(amount)").group('player').order("SUM(amount) DESC").first

Error: 
Mysql2::Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SUM(amount) FROM `Stats_death`  GROUP BY player ORDER BY SUM(amount) DESC LIMIT ' at line 1: SELECT  player SUM(amount) FROM `Stats_death`  GROUP BY player ORDER BY SUM(amount) DESC LIMIT 1

View

Most Killed Player: <%=@mostKilledplayer.first%>

Error: Mysql2::Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SUM(amount) FROM `Stats_death`  GROUP BY player ORDER BY SUM(amount) DESC LIMIT ' at line 1: SELECT  player SUM(amount) FROM `Stats_death`  GROUP BY player ORDER BY SUM(amount) DESC LIMIT 1

I didn't have them like that at the same time. I first changed the controller, then when that didn't work I put it in the view. They both produce the same error. It is occurring on the line that I changed, so it's nothing wrong with the other code.

Re: Convert MySQl query into Rails

I was able to get it to work. It is however printing out some weird data.

Here is what I did to get it to work.

Statsdeath.select("player SUM(amount)").group(:player).order("SUM(amount) DESC").limit(1).sum(:amount)

It is however returning a Hash that is hard to work with. Instead of the keys being player and amount, they are the player's name and the amount.

{"foo"=>73}

Is this the best way to associate a player with their amount of deaths?

Last edited by Wnt2bsleepin (2013-01-08 13:40:33)