Custom search box

[chabgood]

Is there an easy way to implement search box limited by column? So it will search by a certain column?

[antillas21]

As in limiting to only one column?

You can do so defining columns properties in your JS code, example: Suppose I have a table with 4 columns, and want only one of them to be searchable, say column number 3.

    $('#users-table').dataTable({
      responsive: true,
      pageLength: 25,
      processing: true,
      serverSide: true,
      ajax: $('#users-table').data('source'),
      columns: [
        { sortable: true, searchable: false },
        { sortable: true, searchable: false },
        { sortable: true, searchable: true },
        { sortable: false, searchable: false }
      ],
      order: [1, 'desc']
    });

[chabgood]

I was referring to having another search box labeled PO where it will only search on that column. I am tasked to have several boxes that will search on specific columns for each search box.

On Wed, Dec 17, 2014 at 3:33 PM, Antonio Antillon notifications@github.com wrote:

As in limiting to only one column?

You can do so defining columns properties in your JS code, example: Suppose I have a table with 4 columns, and want only one of them to be searchable, say column number 3.

$('#users-table').dataTable({
  responsive: true,
  pageLength: 25,
  processing: true,
  serverSide: true,
  ajax: $('#users-table').data('source'),
  columns: [
    { sortable: true, searchable: false },
    { sortable: true, searchable: false },
    { sortable: true, searchable: true },
    { sortable: false, searchable: false }
  ],
  order: [1, 'desc']
});

— Reply to this email directly or view it on GitHub https://github.com/antillas21/ajax-datatables-rails/issues/65#issuecomment-67417079 .

"In matters of style, swim with the current; in matters of principle, stand like a rock." Thomas Jefferson

[antillas21]

Oooh I see... yes, there is a way to do what you need.

Please refer to the jQuery.dataTables plugin documentation here. You'll notice this feature is supported by this gem since this commit.

Happy hacking.

[chabgood]

Thanks!!

On Wednesday, December 17, 2014, Antonio Antillon notifications@github.com wrote:

Oooh I see... yes, there is a way to do what you need.

Please refer to the jQuery.dataTables plugin documentation here http://datatables.net/examples/api/multi_filter.html. You'll notice this feature is supported by this gem since this commit https://github.com/antillas21/ajax-datatables-rails/commit/bd669f8931cd7e63b129fb2066dcfc3e0b9ef7d1 .

Happy hacking.

— Reply to this email directly or view it on GitHub https://github.com/antillas21/ajax-datatables-rails/issues/65#issuecomment-67431591 .

"In matters of style, swim with the current; in matters of principle, stand like a rock." Thomas Jefferson

[chabgood]

Howdy, I see the parameters coming across but it is not searching any fields:

value is: saaaaaaaaaaaaaa

Processing by SytelineOrdersController#managebuyers as JSON Parameters: {"draw"=>"49", "columns"=>{"0"=>{"data"=>"0", "name"=>"", "searchable"=>"false", "orderable"=>"false", "search"=>{"value"=>"", "regex"=>"false"}}, "1"=>{"data"=>"1", "name"=>"", "searchable"=>"true", "orderable"=>"true", "search"=>{"value"=>"", "regex"=>"false"}}, "2"=>{"data"=>"2", "name"=>"", "searchable"=>"true", "orderable"=>"true", "search"=>{"value"=>"saaaaaaaaaaaaaa", "regex"=>"false"}}, "3"=>{"data"=>"3", "name"=>"", "searchable"=>"true", "orderable"=>"true", "search"=>{"value"=>"", "regex"=>"false"}}, "4"=>{"data"=>"4", "name"=>"", "searchable"=>"true", "orderable"=>"true", "search"=>{"value"=>"", "regex"=>"false"}}, "5"=>{"data"=>"5", "name"=>"", "searchable"=>"true", "orderable"=>"true", "search"=>{"value"=>"", "regex"=>"false"}}, "6"=>{"data"=>"6", "name"=>"", "searchable"=>"true", "orderable"=>"true", "search"=>{"value"=>"", "regex"=>"false"}}, "7"=>{"data"=>"7", "name"=>"", "searchable"=>"true", "orderable"=>"true", "search"=>{"value"=>"", "regex"=>"false"}}, "8"=>{"data"=>"8", "name"=>"", "searchable"=>"true", "orderable"=>"true", "search"=>{"value"=>"", "regex"=>"false"}}, "9"=>{"data"=>"9", "name"=>"", "searchable"=>"true", "orderable"=>"true", "search"=>{"value"=>"", "regex"=>"false"}}, "10"=>{"data"=>"10", "name"=>"", "searchable"=>"true", "orderable"=>"true", "search"=>{"value"=>"", "regex"=>"false"}}, "11"=>{"data"=>"11", "name"=>"", "searchable"=>"true", "orderable"=>"true", "search"=>{"value"=>"", "regex"=>"false"}}, "12"=>{"data"=>"12", "name"=>"", "searchable"=>"true", "orderable"=>"true", "search"=>{"value"=>"", "regex"=>"false"}}, "13"=>{"data"=>"13", "name"=>"", "searchable"=>"false", "orderable"=>"false", "search"=>{"value"=>"", "regex"=>"false"}}}, "order"=>{"0"=>{"column"=>"3", "dir"=>"desc"}}, "start"=>"0", "length"=>"10", "search"=>{"value"=>"", "regex"=>"false"}, ""=>"1419034663798"} User Load (0.1ms) SELECT users.* FROM users WHERE users.id = 16 LIMIT 1 SytelineVendor Load (0.0ms) SELECT syteline_vendors.* FROM syteline_vendors WHERE syteline_vendors.email = 'chabgood@gmail.com' LIMIT 1 (0.1ms) SELECT COUNT(_) FROM syteline_orders WHERE syteline_orders.user_id = 16 (0.5ms) SELECT COUNT(_) FROM syteline_orders WHERE syteline_orders.user_id = 16 AND ((((((((((((syteline_orders.po_line LIKE 0 OR syteline_orders.qty_ordered LIKE 0) OR syteline_orders.qty_received LIKE 0) OR syteline_orders.u_m LIKE '%%') OR syteline_orders.po_id LIKE 0) OR syteline_orders.vendor_name LIKE '%%') OR syteline_orders.item_name LIKE '%%') OR syteline_orders.item_description LIKE '%%') OR syteline_orders.buyer LIKE '%%') OR syteline_orders.unit_mat_cost LIKE 0.0) OR syteline_orders.due_date LIKE '%%') OR syteline_orders.status LIKE '%%')) AND (syteline_orders.qty_received LIKE 0) SytelineOrder Load (0.7ms) SELECT syteline_orders.* FROM syteline_orders WHERE syteline_orders.user_id = 16 AND ((((((((((((syteline_orders.po_line LIKE 0 OR syteline_orders.qty_ordered LIKE 0) OR syteline_orders.qty_received LIKE 0) OR syteline_orders.u_m LIKE '%%') OR syteline_orders.po_id LIKE 0) OR syteline_orders.vendor_name LIKE '%%') OR syteline_orders.item_name LIKE '%%') OR syteline_orders.item_description LIKE '%%') OR syteline_orders.buyer LIKE '%%') OR syteline_orders.unit_mat_cost LIKE 0.0) OR syteline_orders.due_date LIKE '%%') OR syteline_orders.status LIKE '%%')) AND (syteline_orders.qty_received LIKE 0) ORDER BY syteline_orders.u_m DESC LIMIT 10 OFFSET 0 CACHE (0.0ms) SELECT COUNT(*) FROM syteline_orders WHERE syteline_orders.user_id = 16 AND ((((((((((((syteline_orders.po_line LIKE 0 OR syteline_orders.qty_ordered LIKE 0) OR syteline_orders.qty_received LIKE 0) OR syteline_orders.u_m LIKE '%%') OR syteline_orders.po_id LIKE 0) OR syteline_orders.vendor_name LIKE '%%') OR syteline_orders.item_name LIKE '%%') OR syteline_orders.item_description LIKE '%%') OR syteline_orders.buyer LIKE '%%') OR syteline_orders.unit_mat_cost LIKE 0.0) OR syteline_orders.due_date LIKE '%%') OR syteline_orders.status LIKE '%%')) AND (syteline_orders.qty_received LIKE 0) Comple

On Wed, Dec 17, 2014 at 6:31 PM, Chris Habgood chabgood@gmail.com wrote:

Thanks!!

On Wednesday, December 17, 2014, Antonio Antillon < notifications@github.com> wrote:

Oooh I see... yes, there is a way to do what you need.

Please refer to the jQuery.dataTables plugin documentation here http://datatables.net/examples/api/multi_filter.html. You'll notice this feature is supported by this gem since this commit https://github.com/antillas21/ajax-datatables-rails/commit/bd669f8931cd7e63b129fb2066dcfc3e0b9ef7d1 .

Happy hacking.

— Reply to this email directly or view it on GitHub https://github.com/antillas21/ajax-datatables-rails/issues/65#issuecomment-67431591 .

"In matters of style, swim with the current; in matters of principle, stand like a rock." Thomas Jefferson

"In matters of style, swim with the current; in matters of principle, stand like a rock." Thomas Jefferson

[antillas21]

Hey @chabgood I was baffled by the possibility that the gem does not work as expected... so I set to replicate your issue with a demo app I use to test the gem... luckily, I found the yadcf plugin that extends jQuery.dataTables and has been of great help to drive out a solution.

Please take a look at this approach:

• besides the regular steps to install this gem and the jQuery dataTables plugin, follow these steps.
• include the css and js files from the yadcf plugin and make sure you load them after jQuery dataTables in your app's asset pipeline.
• define your datatable ruby class as you normally would by following the instructions in this gem's README file.
• the code will change once you get to instantiate a dataTable object in your JS.

The following example assumes we have a Client model, we are displaying 3 columns from this model in a dataTable (id, name, email)

<h1>Listing clients</h1>

<table id="clients-table" data-source="<%= clients_path(format: :json) %>">
  <thead>
    <tr>
      <th>ID</th>
      <th>Name</th>
      <th>Email</th>
    </tr>
  </thead>

  <tbody>
  </tbody>
</table>

$(document).ready(function() {
     $('#clients-table').dataTable({
        "processing": true,
        "serverSide": true,
        "ajax": $('#clients-table').data('source')
    }).yadcf([
        {
          column_number: 0,
          filter_type: 'text'
        },
        {
          column_number: 1,
          filter_type: 'text'
        },
        {
          column_number: 2,
          filter_type: "text"
        }
        ]);
  });

Now, this will display a text input in each header row where you can enter text to individually filter that column.

Look at this example params being passed when I enter text into the inputs on name and email columns:

Processing by ClientsController#index as JSON
  Parameters: {"draw"=>"6", "columns"=>{"0"=>{"data"=>"0", "name"=>"", "searchable"=>"true", "orderable"=>"true", "search"=>{"value"=>"", "regex"=>"false"}}, "1"=>{"data"=>"1", "name"=>"", "searchable"=>"true", "orderable"=>"true", "search"=>{"value"=>"eu", "regex"=>"false"}}, "2"=>{"data"=>"2", "name"=>"", "searchable"=>"true", "orderable"=>"true", "search"=>{"value"=>"ary", "regex"=>"false"}}}, "order"=>{"0"=>{"column"=>"0", "dir"=>"asc"}}, "start"=>"0", "length"=>"10", "search"=>{"value"=>"", "regex"=>"false"}, "_"=>"1419056495488"}
   (0.1ms)  SELECT COUNT(*) FROM "clients"
   (0.2ms)  SELECT COUNT(*) FROM "clients"  WHERE (CAST("clients"."name" AS TEXT) LIKE '%eu%' AND CAST("clients"."email" AS TEXT) LIKE '%ary%')
  Client Load (0.2ms)  SELECT  "clients".* FROM "clients"  WHERE (CAST("clients"."name" AS TEXT) LIKE '%eu%' AND CAST("clients"."email" AS TEXT) LIKE '%ary%')  ORDER BY clients.id ASC LIMIT 10 OFFSET 0
Completed 200 OK in 3ms (Views: 2.3ms | ActiveRecord: 0.5ms)

Please notice the use of AND in all WHERE clauses in the resulting SQL :smile: ... so, the gem works, it just needs the proper request to respond to.

[chabgood]

I see an issue it is off by 1 column I have the first column a a checkbox but the gem is not seeing that. How do I get the 2 below to jive correctly so the plugin picks up what columns are not searchable?

$ -> if $('#po_items_buyers').length $('#po_items_buyers').dataTable processing: true serverSide: true stateSave: true responsive: true jQueryUI: true ajax: $('#po_items_buyers').data('source') pagingType: 'full_numbers' "order": [[ 1, "desc" ]] 'columns': [ { 'sortable': false, 'searchable': false }, { 'sortable': true, 'searchable': true }, { 'sortable': true, 'searchable': true }, { 'sortable': true, 'searchable': true }, { 'sortable': true, 'searchable': true }, { 'sortable': true, 'searchable': true }, { 'sortable': true, 'searchable': true }, { 'sortable': true, 'searchable': true }, { 'sortable': true, 'searchable': true }, { 'sortable': true, 'searchable': true }, { 'sortable': true, 'searchable': true }, { 'sortable': true, 'searchable': true }, { 'sortable': true, 'searchable': true }, { 'sortable': false, 'searchable': false }, ]

myTable = $("#po_items_buyers").DataTable() yadcf.init myTable, [ { column_number: 0 filter_type: "text" } { column_number: 2 filter_type: "text" } ] return

[antillas21]

You can try adding an active record managed column like id, created_at, updated_at, deleted_at (if exists). I regularly do that in this case... and as the JS code is specifying that this first column in neither searchable nor sortable, it will not be taken into account if you add individual search controls to perform individual column filtering.

[chabgood]

sorry this may be stupid question but where do I add the extra column?

On Mon, Dec 22, 2014 at 3:29 PM, Antonio Antillon notifications@github.com wrote:

You can try adding an active record managed column like id, created_at, updated_at, deleted_at (if exists). I regularly do that in this case... and as the JS code is specifying that this first column in neither searchable nor sortable, it will not be taken into account if you add individual search controls to perform individual column filtering.

— Reply to this email directly or view it on GitHub https://github.com/antillas21/ajax-datatables-rails/issues/65#issuecomment-67904684 .

"In matters of style, swim with the current; in matters of principle, stand like a rock." Thomas Jefferson

[antillas21]

add it to the searchable_columns and/or the sorting_columns arrays. As your problem comes from having a first column that is a checkbox and not mapped to a field in your model's table... add this column as the first entry in one or both arrays.

There should be no problem in the overall functionality, as your JS code specifies the first column as not searchable and not sortable. On Dec 22, 2014 10:29 PM, "Chris Habgood" notifications@github.com wrote:

sorry this may be stupid question but where do I add the extra column?

On Mon, Dec 22, 2014 at 3:29 PM, Antonio Antillon < notifications@github.com> wrote:

You can try adding an active record managed column like id, created_at, updated_at, deleted_at (if exists). I regularly do that in this case... and as the JS code is specifying that this first column in neither searchable nor sortable, it will not be taken into account if you add individual search controls to perform individual column filtering.

— Reply to this email directly or view it on GitHub < https://github.com/antillas21/ajax-datatables-rails/issues/65#issuecomment-67904684>

.

"In matters of style, swim with the current; in matters of principle, stand like a rock." Thomas Jefferson

— Reply to this email directly or view it on GitHub https://github.com/antillas21/ajax-datatables-rails/issues/65#issuecomment-67925504 .

[chabgood]

hmm, that did not seem to help. I am pulling my hair out.

[antillas21]

Did you add the column in the same position (inside the array) than what the checkbox occupies in the resulting datatable?

[chabgood]

yes

On Fri, Dec 26, 2014 at 12:51 PM, Antonio Antillon <notifications@github.com

wrote:

Did you add the column in the same position (inside the array) than what the checkbox occupies in the resulting datatable?

— Reply to this email directly or view it on GitHub https://github.com/antillas21/ajax-datatables-rails/issues/65#issuecomment-68157621 .

"In matters of style, swim with the current; in matters of principle, stand like a rock." Thomas Jefferson