Topic: Grouping / counting data from mysql

Hi,

I'll be honest im not sure if this should be done in the model or in the controller, ether way im struggling to find what i need with google.


DB Model

mysql> desc statuses;
+------------+----------+------+-----+---------+----------------+
| Field      | Type     | Null | Key | Default | Extra          |
+------------+----------+------+-----+---------+----------------+
| id         | int(11)  | NO   | PRI | NULL    | auto_increment |
| feed_id    | int(11)  | YES  |     | NULL    |                |
| page_count | int(11)  | YES  |     | NULL    |                |
| message    | text     | YES  |     | NULL    |                |
| created_at | datetime | YES  |     | NULL    |                |
| updated_at | datetime | YES  |     | NULL    |                |
| code       | int(11)  | YES  |     | NULL    |                |
+------------+----------+------+-----+---------+----------------+
7 rows in set (0.00 sec)

Example data needed

+---------------------+------+
| created_at          | code |
+---------------------+------+
| 2012-04-09 09:00:06 |    1 |
| 2012-04-09 09:00:07 |  100 |
| 2012-04-09 10:00:10 |    200 |
| 2012-04-09 10:00:10 |  100 |
| 2012-04-09 16:00:11 |    1 |
| 2012-04-09 16:00:11 |  200 |
| 2012-04-09 16:10:07 |    1 |
| 2012-04-09 16:10:07 |  100 |
| 2012-04-09 23:10:07 |    1 |
| 2012-04-09 23:10:07 |  100 |
+---------------------+------+

I have tried some crazy sql to try and return the data i'm looking to achieve, however its not quiet right.

  def self.feed_id_and_days(feed_id, days)
    older_than = Time.now.utc - days*60*60*24
   self.find_by_sql("SELECT SUBSTRING_INDEX(created_at, ':',1 ) as created_at, code, count(code) as message FROM `statuses` WHERE (feed_id = '230' and created_at > '2012-05-24 05:49:36') GROUP BY SUBSTRING_INDEX(created_at, ':',1 ),code")
end

this returns a array of objects like so

[#<Status message: 1, created_at: "2012-05-27 17:00:00", code: 0>, #<Status message: 1, created_at: "2012-05-27 17:00:00", code: 100>, #<Status message: 3, created_at: "2012-05-30 20:00:00", code: 0>, #<Status message: 4, created_at: "2012-05-30 20:00:00", code: 100>, #<Status message: 1, created_at: "2012-05-30 20:00:00", code: 200>, #<Status message: 5, created_at: "2012-05-30 21:00:00", code: 0>, #<Status message: 5, created_at: "2012-05-30 21:00:00", code: 100>, #<Status message: 1, created_at: "2012-05-31 15:00:00", code: 0>, #<Status message: 1, created_at: "2012-05-31 15:00:00", code: 100>]

What im after achieving is a array ether from the model or through the controller which returns per day, per hour and then the code grouping so i can create a graph showing how many run's per hour, of which how many succeeded and how many failed etc

So possibly something like the bellow

Created_at, code100, code1, code200
2012-05-30 20:00:00, 10, 1,9
2012-05-30 21:00:00, 12, 9,3

The idea is to take this data and pass it to a graph.

http://www.DumpServer.co.uk Customizable RSS Aggregation

Re: Grouping / counting data from mysql

I didn't understand why you don't use ActiveRecord for it(Status.all(:conditions => ["created_at > ?", Date.today - days_count.days).map{|s| [s.created_at, s.code]}

If you don't have ActiveRecord model for statuses you can use ActiveRecord::Base.connection.select_all("SELECT created_at, code FROM statuses WHERE created_at > DATE('#{(Date.today - days_count.days).to_formatted_s(:db)}')").

Re: Grouping / counting data from mysql

I've been messing with this for a while, i tried the Staus.all method but then could not think how i could get the date out i wanted to make the graphs.

I'll have another play with this and see, thanks for the feedback.

http://www.DumpServer.co.uk Customizable RSS Aggregation

Re: Grouping / counting data from mysql

Thanks, that has helped me get the info into the bellow format, just need to figure out a way to create some nice graphs from it.

[Sun, 03 Jun 2012 00:00:07 UTC +00:00, 100]
[Sun, 03 Jun 2012 00:01:35 UTC +00:00, 1]
[Sun, 03 Jun 2012 00:01:35 UTC +00:00, 100]
[Wed, 06 Jun 2012 00:00:09 UTC +00:00, 1]
[Wed, 06 Jun 2012 00:00:10 UTC +00:00, 100]
[Wed, 06 Jun 2012 00:03:29 UTC +00:00, 200]
[Wed, 06 Jun 2012 00:03:29 UTC +00:00, 100]
[Wed, 06 Jun 2012 00:06:11 UTC +00:00, 1]
[Wed, 06 Jun 2012 00:06:11 UTC +00:00, 100]
[Wed, 06 Jun 2012 00:08:15 UTC +00:00, 1]
[Wed, 06 Jun 2012 00:08:15 UTC +00:00, 100] 

Some how i would like to take the time / date and chop it down to just being a day or a day + hour.

Last edited by dump_weed (2012-06-07 23:30:36)

http://www.DumpServer.co.uk Customizable RSS Aggregation

Re: Grouping / counting data from mysql

Get Facebook fans, buy facebook fans, get facebook likes, buy facebook likes. Delivering likes to fan pages and website and tracking progress at http://www.socialkik.com/