Cant call .all on dataset

Question

I am running into problems when changing a query from plain sql (note: I am not using AR but sequel)

So this works:

Post.fetch(
    "SELECT
      COUNT(*) as count, HOUR(created_at) as date, class_type
    FROM
      tours t
    WHERE
      t.created_at Between ? and ?
    GROUP BY hour(t.created_at), flavor", @range_begins, @range_ends
    ).all

And it returns the expected array. But this doesnt:

Post.select("COUNT(*) as count, HOUR(created_at) as date, class_type")
    .where(created_at: @basic_range)
    .group_by("HOUR(created_at), flavor")
    .all

But in the console (without the .all at the end) the query generated from that looks good (formatted for better reading):

<Sequel::Mysql2::Dataset:
"SELECT
  'COUNT(*) as count, HOUR(created_at) as date, class_type'
FROM
  `posts`
WHERE
  ((`created_at` >= '2014-05-01 00:00:00') AND (`created_at` <= '2014-05-01 23:59:59')) 
GROUP BY
  'HOUR(created_at), flavor'">

Adding the ".all" at the end returns an array looking like this:

[#<Tour @values={:"COUNT(*) as count, HOUR(created_at) as date, class_type"=>"COUNT(*) as count, HOUR(created_at) as date, class_type"}>]

what am I missing? Thanks in advance for the help

score 4 · Accepted Answer · answered May 01 '14 at 07:40

4

When you are writing query with where function, you dont need to write .all in the end, it will by default give all the results matching the query parameters. You just need to do :

Post.select("COUNT(*) as count, HOUR(created_at) as date, class_type")
    .where(created_at: @basic_range)
    .group_by("HOUR(created_at), flavor")

answered May 01 '14 at 07:40

Saurabh

71,488
40
181
244

I thought you precisely should have attributes from `group_by` and `having` in `select` to work. – zishe May 01 '14 at 09:16
1

@zishe: It's implementation dependent. For example, Oracle requires that the columns named in the 'group by' clause appear in the select statement. Others, including MySql, for example, do not. – Saurabh May 01 '14 at 09:51
Hey Saurabh, I tried it without the .all and it doesnt return an array with the data, just the dataset: = '2014-05-01 00:00:00') AND (`created_at` <= '2014-05-01 23:59:59')) GROUP BY 'HOUR(created_at), flavor'"> – tomr May 01 '14 at 13:15

Cant call .all on dataset

1 Answers1