mercredi 12 août 2015

Grouping Whole records by formatted Date Hash

I am trying to get these querys to return a hash that has id's like this:

{[08/14/2015, [... records ...]],[08/15/2015, [... records ...]]}

With the following code it does this:

@trucks = Truck.where(:company_id => 33)
@trucks_by_date = @trucks.group_by(&:available)

=> {[Fri, 14 Aug 2015 00:00:00 UTC +00:00, [... records ...]], [Sat, 15 Aug 2015 00:00:00 UTC +00:00, [... records ...]]}

I have tried this but it errors:

@trucks.group("DATE(available)")

    => 1.9.3-p551 :074 >     @trucks_by_date = @trucks.group("DATE(available)")
Truck Load (74.2ms)  SELECT "trucks".* FROM "trucks" WHERE "trucks"."company_id" = 33 GROUP BY DATE(available)
ActiveRecord::StatementInvalid: PG::GroupingError: ERROR:  column "trucks.id" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: SELECT "trucks".* FROM "trucks"  WHERE "trucks"."company_id"...
                                                        ^
: SELECT "trucks".* FROM "trucks"  WHERE "trucks"."company_id" = 33 GROUP BY DATE(available)
from /usr/local/rvm/gems/ruby-1.9.3-p551@loadmax/gems/activerecord-3.2.3/lib/active_record/connection_adapters/postgresql_adapter.rb:1139:in `async_exec'
        from /usr/local/rvm/gems/ruby-1.9.3-p551@loadmax/gems/activerecord-3.2.3/lib/active_record/connection_adapters/postgresql_adapter.rb:1139:in `exec_no_cache'
        from /usr/local/rvm/gems/ruby-1.9.3-p551@loadmax/gems/activerecord-3.2.3/lib/active_record/connection_adapters/postgresql_adapter.rb:663:in `block in exec_query'

If i do this: (also wrong format for date)

@trucks_by_date = @trucks.group("DATE(available)").count

=> `{"2015-08-13"=>5, "2015-07-27"=>3, "2015-08-18"=>2, "2015-08-14"=>6, "2015-08-17"=>1, "2015-07-31"=>2, "2015-08-12"=>5, "2015-08-11"=>8, "2015-07-30"=>4, "2015-08-10"=>13, "2015-08-07"=>4, "2015-08-03"=>4, "2015-07-24"=>1, "2015-07-28"=>5}` 

But i don't want counts i want complete records.

REASON: I have a initializer that changes formats on data like so:

Date::DATE_FORMATS[:default] = "%m/%d/%Y"
Time::DATE_FORMATS[:default] = "%m/%d/%Y"

and when i try to iterate thru the hash it changes the formats so it cant find the right position of the hash

Aucun commentaire:

Enregistrer un commentaire