leikind / wice_grid

A Rails grid plugin to create grids with sorting, pagination, and (automatically generated) filters
MIT License
535 stars 215 forks source link

How to use filter by full datetime (with time part)? #344

Open havran opened 7 years ago

havran commented 7 years ago

Hi. We use wicegrid in our site and we have configured filter on created_at field. But for some reason in SQL query there are only date part from filter => ( message_posts_view.created_at >= '2017-02-12' and message_posts_view.created_at <= '2017-02-14' ) .

We use latest wicegrid version - 3.6.0.pre4

Filter is filter_type: :rails_datetime_helper (detached).

Parameters sent to rails seems correct:

 Parameters: {"grid"=>{"f"=>{"created_at"=>{"fr"=>{"year"=>"2017", "month"=>"2", "day"=>"12", "hour"=>"00", "minute"=>"00"}, "to"=>{"year"=>"2017", "month"=>"2", "day"=>"13", "hour"=>"23", "minute"=>"59"}}}}}

Sql query looks like this:

  SQL (8.9ms)  SELECT  "message_posts_view"."id" AS t0_r0, "message_posts_view"."title" AS t0_r1, "message_posts_view"."content" AS t0_r2, "message_posts_view"."source_source_id" AS t0_r3, "message_posts_view"."source_source_title" AS t0_r4, "message_posts_view"."source_section_id" AS t0_r5, "message_posts_view"."source_mutation_id" AS t0_r6, "message_posts_view"."source_attachment_id" AS t0_r7, "message_posts_view"."issued_on" AS t0_r8, "message_posts_view"."issued_at" AS t0_r9, "message_posts_view"."web_issued_at_update" AS t0_r10, "message_posts_view"."web_link_url" AS t0_r11, "message_posts_view"."print_number" AS t0_r12, "message_posts_view"."print_year" AS t0_r13, "message_posts_view"."print_pages" AS t0_r14, "message_posts_view"."broadcast_position" AS t0_r15, "message_posts_view"."authors" AS t0_r16, "message_posts_view"."moderators" AS t0_r17, "message_posts_view"."redactors" AS t0_r18, "message_posts_view"."source_source_code_name" AS t0_r19, "message_posts_view"."source_source_language" AS t0_r20, "message_posts_view"."source_source_country" AS t0_r21, "message_posts_view"."source_source_settings" AS t0_r22, "message_posts_view"."source_type_id" AS t0_r23, "message_posts_view"."source_type_title" AS t0_r24, "message_posts_view"."source_type_code_name" AS t0_r25, "message_posts_view"."source_type_kind" AS t0_r26, "message_posts_view"."source_scope_id" AS t0_r27, "message_posts_view"."source_scope_title" AS t0_r28, "message_posts_view"."source_scope_code_name" AS t0_r29, "message_posts_view"."source_periodicity_id" AS t0_r30, "message_posts_view"."source_periodicity_title" AS t0_r31, "message_posts_view"."source_periodicity_code_name" AS t0_r32, "message_posts_view"."source_areas_ids" AS t0_r33, "message_posts_view"."source_section_title" AS t0_r34, "message_posts_view"."source_mutation_title" AS t0_r35, "message_posts_view"."source_attachment_title" AS t0_r36, "message_posts_view"."private" AS t0_r37, "message_posts_view"."customer_ids" AS t0_r38, "message_posts_view"."rating_grp" AS t0_r39, "message_posts_view"."rating_ave" AS t0_r40, "message_posts_view"."state" AS t0_r41, "message_posts_view"."system_state" AS t0_r42, "message_posts_view"."access_user_id" AS t0_r43, "message_posts_view"."language" AS t0_r44, "message_posts_view"."exported" AS t0_r45, "message_posts_view"."exported_at" AS t0_r46, "message_posts_view"."exported_so" AS t0_r47, "message_posts_view"."exported_at_so" AS t0_r48, "message_posts_view"."created_at" AS t0_r49, "message_posts_view"."updated_at" AS t0_r50, "message_posts_view"."size" AS t0_r51, "access_users"."id" AS t1_r0, "access_users"."username" AS t1_r1, "access_users"."email" AS t1_r2, "access_users"."full_name" AS t1_r3, "access_users"."encrypted_password" AS t1_r4, "access_users"."reset_password_token" AS t1_r5, "access_users"."reset_password_sent_at" AS t1_r6, "access_users"."remember_created_at" AS t1_r7, "access_users"."sign_in_count" AS t1_r8, "access_users"."current_sign_in_at" AS t1_r9, "access_users"."last_sign_in_at" AS t1_r10, "access_users"."current_sign_in_ip" AS t1_r11, "access_users"."last_sign_in_ip" AS t1_r12, "access_users"."confirmation_token" AS t1_r13, "access_users"."confirmed_at" AS t1_r14, "access_users"."confirmation_sent_at" AS t1_r15, "access_users"."unconfirmed_email" AS t1_r16, "access_users"."failed_attempts" AS t1_r17, "access_users"."unlock_token" AS t1_r18, "access_users"."locked_at" AS t1_r19, "access_users"."created_at" AS t1_r20, "access_users"."updated_at" AS t1_r21, "access_users"."destroyed_at" AS t1_r22, "access_users"."authentication_token" AS t1_r23, "access_users"."disabled" AS t1_r24, "access_users"."settings" AS t1_r25 
FROM "message_posts_view" 
LEFT OUTER JOIN "access_users" ON "access_users"."id" = "message_posts_view"."access_user_id" AND "access_users"."destroyed_at" IS NULL 
WHERE "message_posts_view"."state" = $1 
  AND "message_posts_view"."system_state" = $2 
  AND ( message_posts_view.created_at >= '2017-02-12'  and  message_posts_view.created_at <= '2017-02-14' )  
ORDER BY "message_posts_view"."created_at" desc 
LIMIT 40 
OFFSET 0  [["state", "completed"], ["system_state", "indexed"]]

Filter looks like this:

Imgur

Code for column:

    g.column name: t('created_at'),
             attribute: 'created_at',
             class: 'shrink',
             filter_type: :rails_datetime_helper,
             detach_with_id: :created_at_filter do |post_view|
      post_view.created_at.strftime('%d.%m.%Y %H:%M')
    end
havran commented 7 years ago

Seems like in demo app this dont work too http://wicegrid.herokuapp.com/dates?grid%5Bf%5D%5Bupdated_at%5D%5Bfr%5D%5Byear%5D=2015&grid%5Bf%5D%5Bupdated_at%5D%5Bfr%5D%5Bmonth%5D=8&grid%5Bf%5D%5Bupdated_at%5D%5Bfr%5D%5Bday%5D=17&grid%5Bf%5D%5Bupdated_at%5D%5Bfr%5D%5Bhour%5D=20&grid%5Bf%5D%5Bupdated_at%5D%5Bfr%5D%5Bminute%5D=00&grid%5Bf%5D%5Bupdated_at%5D%5Bto%5D%5Byear%5D=2015&grid%5Bf%5D%5Bupdated_at%5D%5Bto%5D%5Bmonth%5D=8&grid%5Bf%5D%5Bupdated_at%5D%5Bto%5D%5Bday%5D=17&grid%5Bf%5D%5Bupdated_at%5D%5Bto%5D%5Bhour%5D=21&grid%5Bf%5D%5Bupdated_at%5D%5Bto%5D%5Bminute%5D=00