Q: col custom search + formatting

[Mr-Tao]

I’ve found this brilliant gem a few days ago.

I have a cell with a JSON serialized array. I need to list these items in a DataTable cell and I also need to search through them. As easy as it is to implement each of these requirements, I have spent the better part of the day trying to figure out how to achieve both.

• For the display part I did col :participants, partial: 'participants/table_item', partial_as: 'event' and put

  <ul>
    <% JSON::parse(event.participants).each do |p| %>
      <li><%= p %></li>
    <% end %>
  </ul>

  in the partial.
• For the search part I’ve set up full text search in PostgreSQL so that Event.search_participants() does the searching and I can write:

  col :participants do |event|
    event.participants
  end.search do |collection, term, column, sql_column|
    collection = Event.search_participants(term)
  end

I would like to put these together, but I cannot as You cannot use partial: … with the block syntax
So I tried

col :participants do |event|
  tag.ul do
    JSON.parse(event.participants).each do |participant|
      tag.li participant
    end.search do |collection, term, column, sql_column|
      collection = Decision.search_participants(term)
    end
  end
end

but this render just an empty <ul></ul>. What am I doing wrong here?

Funnily I've skimmed through some old documentation too and it seems like overriding search_column method would suffice in the past.

[Mr-Tao]

Funy like you find a workaround just after writing the post…

col :participants do |decision|
  tag.ul ActionController::Base.helpers.
      sanitize(JSON::parse(decision.participants).map { |i| tag.li i }.join )
end.search do |collection, term, column, sql_column|
  collection = Decision.search_participants(term)
end

There, however is another issue:

col :category, visible: true, search: { as: :select, 
                                        collection: EventCategory.all, 
                                        multiple: true }

This does great job rendering multiple-choice selection, but items in the table are rendered as links to edit action. Passing as: string to col seems to work at first sight, but it breaks search since queries are formed like SELECT … WHERE "event"."decision_category_id" ILIKE '%93% instead … IN (93).

[matt-riemer]

Haha ooh @Mr-Tao

Software is fun right? It's like..."this should be so easy", "we're so close" but no...

The end of your first issue there...where you remove the partial and move to the block...is so close to correct, but your end was a bit weird. Try like this:

col :participants do |event|
  tag.ul do
    JSON.parse(event.participants).map { |participant| tag.li participant }.join().html_safe
  end
end.search do |collection, term, column, sql_column|
  collection = Decision.search_participants(term)
end

col returns an object which you can then chain search and sort onto (but otherwise is just a Hash)

[matt-riemer]

Is your category a belongs_to object? Or just a string?

That query makes me think datatables is detecting it like a string ILIKE %93% but the decision_category_id makes me think it's a belongs_to? Not sure what's up there.

Something like this should work tho:

col(:category, search: { as: :select, collection: EventCategory.all, multiple: true }) do |obj|
  obj.category.to_s
end

or

col(:category_id, as: :integer, search: { as: :select, collection: EventCategory.all, multiple: true }) do |obj|
  obj.category.to_s
end

Or maybe the join syntax could work

datatable do
  col 'category.name'
end

collection do
   Event.joins(:category).all
end

[Mr-Tao]

Thanks, @matt-riemer, category indeed is a belongs_to object and obj.category.to_s did the job! Event.joins instigated the db to usurp all the memory instead :).

While scopes are working great, I am unable to get filters to perform.

I've set test filter like this:

  filters do
    byebug
    filter :test_filter, "default-query"
  end

it looks like nothing I write into the filter field propagates and default gets always picked instead. After submitting any filter value I get:

    5:         def filter(name = nil, value = :_no_value, as: nil, label: nil, parse: nil, required: false, **input_html)
=>  6:           return datatable.filter if (name == nil && value == :_no_value) # This lets block methods call 'filter' and get the values
    7:
    8:           raise 'expected second argument to be a value' if value == :_no_value
    9:           raise 'parse must be a Proc' if parse.present? && !parse.kind_of?(Proc)
   10:
(byebug) name
:test_filter
(byebug) value
"default-query"

[matt-riemer]

Hmm, each filter just ends up as a form field in the filters section above the datatable.

If you just did filter :start_date, Time.zone.now, required: true does a date selector get displayed? This should work just fine with your string too.

[Mr-Tao]

Filter fields are showing up as a plain text field. Is the date selector supposed to be a fancy picker?

The issue is that after submitting the form only default values are passed to query in the collection block.

[matt-riemer]

yea that date selector is supposed to be a fancy picker (this is from the effective_bootstrap gem, which I don't think you have the javascript/stylesheets included).

But it does look like your string filter is being displayed.

Weird it wouldn't submit with whatever value you've typed into that search box!

Is there some kind of console error or otherwise going on here? What values do you see via the rails console when you click Apply?

[Mr-Tao]

Oic, I do not have effective_form_inputs because of effective_form_inputs and effective_bootstrap cannot be run alongside eachother. Sorry.
In the Rails console there are filter’s default values (default-query below):

console log here ☜

Finished "/cable/" [WebSocket] for 127.0.0.1 at 2018-05-15 21:53:34 +0200
ProgressNotificationChannel stopped streaming from progress_notification_channel
Started GET "/cable" for 127.0.0.1 at 2018-05-15 21:53:35 +0200
Started GET "/cable/" [WebSocket] for 127.0.0.1 at 2018-05-15 21:53:35 +0200
Successfully upgraded to WebSocket (REQUEST_METHOD: GET, HTTP_CONNECTION: Upgrade, HTTP_UPGRADE: websocket)
ProgressNotificationChannel is transmitting the subscription confirmation
ProgressNotificationChannel is streaming from progress_notification_channel
Started GET "/cable" for 127.0.0.1 at 2018-05-15 21:53:40 +0200
   (1.5ms)  SELECT "schema_migrations"."version" FROM "schema_migrations" ORDER BY "schema_migrations"."version" ASC
Started GET "/cable/" [WebSocket] for 127.0.0.1 at 2018-05-15 21:53:41 +0200
Successfully upgraded to WebSocket (REQUEST_METHOD: GET, HTTP_CONNECTION: Upgrade, HTTP_UPGRADE: websocket)
ProgressNotificationChannel is transmitting the subscription confirmation
ProgressNotificationChannel is streaming from progress_notification_channel
ProgressNotificationChannel is transmitting the subscription confirmation
ProgressNotificationChannel is streaming from progress_notification_channel
Started POST "/datatables/events_datatable-897909376254.json" for 127.0.0.1 at 2018-05-15 21:53:43 +0200
Processing by Effective::DatatablesController#show as JSON
  Parameters: {"draw"=>"2", "columns"=>{"0"=>{"data"=>"0", "name"=>"instance", "searchable"=>"true", "orderable"=>"true", "search"=>{"value"=>"", "regex"=>"false"}, "visible"=>"false"}, "1"=>{"data"=>"1", "name"=>"matter", "searchable"=>"true", "orderable"=>"true", "search"=>{"value"=>"", "regex"=>"false"}, "visible"=>"true"}, "2"=>{"data"=>"2", "name"=>"participants", "searchable"=>"true", "orderable"=>"true", "search"=>{"value"=>"", "regex"=>"false"}, "visible"=>"true"}, "3"=>{"data"=>"3", "name"=>"category", "searchable"=>"true", "orderable"=>"true", "search"=>{"value"=>"", "regex"=>"false"}, "visible"=>"true"}, "4"=>{"data"=>"4", "name"=>"effective_date", "searchable"=>"true", "orderable"=>"true", "search"=>{"value"=>"", "regex"=>"false"}, "visible"=>"false"}, "5"=>{"data"=>"5", "name"=>"year", "searchable"=>"true", "orderable"=>"true", "search"=>{"value"=>"", "regex"=>"false"}, "visible"=>"false"}}, "order"=>{"0"=>{"column"=>"4", "dir"=>"desc"}}, "start"=>"0", "length"=>"25", "search"=>{"value"=>"", "regex"=>"false"}, "cookie"=>"897909376254", "scope"=>"", "id"=>"events_datatable-897909376254"}
  Decision Load (9.1ms)  SELECT DISTINCT "events"."instance" FROM "events"
"default-query"
  DecisionCategory Load (5.1ms)  SELECT "event_categories".* FROM "event_categories" ORDER BY "event_categories"."name" ASC
   (0.8ms)  SELECT MAX("event_categories"."id") FROM "event_categories"
  CACHE DecisionCategory Load (0.0ms)  SELECT "event_categories".* FROM "event_categories" ORDER BY "event_categories"."name" ASC
   (32.5ms)  SELECT COUNT(*) FROM "events" WHERE (matter ilike '%default-query%')
  CACHE  (0.0ms)  SELECT COUNT(*) FROM "events" WHERE (matter ilike '%default-query%')
COLUMN TOOL: order_column effective_date desc "events"."effective_date"
  Decision Load (28.8ms)  SELECT  "events".* FROM "events" WHERE (matter ilike '%default-query%') ORDER BY "events"."effective_date" DESC NULLS LAST LIMIT 25 OFFSET 0
  Rendered collection of /usr/local/lib/ruby/gems/2.5.0/gems/effective_datatables-4.0.4/app/views/effective/datatables/_resource_column.html.haml [0 times] (0.0ms)
Completed 200 OK in 139ms (Views: 0.5ms | ActiveRecord: 76.3ms)

[matt-riemer]

Yea, datatables 3.0 vs 4.0

Stupid Twitter Bootstrap3 vs Bootstrap4 :)

It's possible there's a bug in there with the filter code. I'm not seeing any value being posted from your parse there.

I will look into this in the next day or so and follow up. Cheers :)

[Mr-Tao]

I’ve included effective_bootstrap – pretty fancy now :) Other than that the issue hold.

[matt-riemer]

Hey again @Mr-Tao,

I double checked and filters seem to be working just fine for me.... :)

effective_datatables 4.0.3
effective_resources 0.8.10
effective_bootstrap 0.0.16

I created a datatable with a string filter:

And it was displayed on my table:

when I type in a value and click 'Apply', it posts to my server with the expected params[:filter]

I hope this extra information can help you debug any problem! Let me know

Cheers

[Mr-Tao]

Thanks, @matt-riemer. I’ve recreated your example with effective_(datatables|resources|bootstrap) and it works and the filter key is present in the parameters hash. Now I am trying to figure out why this key is missing when applying filter in my project.

[Mr-Tao]

Hi @matt-riemer, just a quick update. I did rails new and moved models, views and controllers to the new app (Rails 5.1.4 → 5.2). Good news is that filter key is present in the parameters hash now. Bad news is that I still haven’t found the root cause.

I somewhat suspect that all this has to do something with CSRF protection in Rails. I had to swap protect_from_forgery with: :exception to skip_before_action :verify_authenticity_token in the application_controller.rb to render datatable in 5.2 in the first place. Otherwise the response to the Ajax POST was 422 (Unprocessable Entity). ← result of requiring jquery3 after rails-ujs.

Also there is an exception with EffectiveBootstrap in base.js:

base.self-9c90a…1337.js?body=1:57 Uncaught TypeError: Cannot set property 'confirm' of undefined
    at base.self-9c90a…1337.js?body=1:57
    at base.self-9c90a…1337.js?body=1:70

[matt-riemer]

ooh javascript error. that must be a jquery_ujs missing thing...i'll update the JS to check for that, thanks!

[Mr-Tao]

Filtering is working again! After rails app:update from 5.1.4 to 5.2 I’ve manually merged changed files. After including config.load_defaults 5.2 in config/application.rb the filter field appeared in the XHR JSON and filtering began to work.