Sort / Search column syntax deprecation considerations

[aldefouw]

Hello -

First off - huge thanks to you for maintaining this gem, Antonio. This gem has made life a lot easier for a project the team I'm a part of is working on.

What I'd like to know is what the plan is for for the sort and search column syntax moving forward. I know that the table_name.column_name is already deprecated - but are you planning to remove it entirely in future versions?

I for one would not be in favor of fully removing it and I will likely have to fork updates if that happens.

I knew going into the newer version of this gem (0.3.0) that this functionality was deprecated, but we continue to use the table_name.column_name functionality because ModelName.attribute will not work for us in one of our specific situations.

To be more specific, in our case - we don't use table_name.column name - we actually use just column_name because we're calling an alias.

Our query being made contains a database column alias we formed in the SELECT clause that we use to sort the columns in our DataTable.

We know that a column alias is "ugly" code and it's stepping outside of the model - which isn't a good practice typically. However, in this case, we are trying to sort on a piece of data that is more complex than a single column can typically contain - so it requires a nested sub-query inside an SQL column alias.

I know that this is a bit of an edge case, but I'm wondering if you might consider a configuration option where it would allow us to run in a legacy mode that would prevent these deprecation warnings from happening.

Or perhaps you might consider allowing the legacy column types (including aliases) to be in the column as an option, if configured ...

These are just thoughts. If there is a more elegant way to handle this edge case, I'm all ears.

We know that the functionality is deprecated, but in the case of needing to reference an alias to sort a column properly, there currently really isn't any other choice for us that I'm aware of.

Any insights or thoughts on this area would be appreciated. I've walked through part of this gem's code a few times to see what's happening, but I'm sure you have additional insights beyond what I can glean by reading through.

Thank you!

[antillas21]

Hi @aldefouw,

Glad to know the gem is of help to you and your team :)

About the functionality you mention... here's what's been done on that front:

• We've been working on improving the overall API for the gem in this branch
• I decided not to remove the functionality, but to "improve" it by allowing you to use the "old" table_name.column_name syntax or the new ModelName.column_name syntax, as I realized (as you mention) that it was not a good idea to drop it, given that there are some cases when writing strictly ActiveRecord code only, you end up with alias columns in your resulting query (when calling .to_sql on it). This decision was a result from working on this issue.

So, you've got nothing to worry about losing this functionality :)

Now, about what are the things coming to a newer release? well:

• Collapsed searchable_columns, sortable_columns into one single view_columns method, as we don't need to keep them separated, given we can realize which columns are sortable and searchable based on the params hash coming in from the view.
• You can now use a richer API when declaring the columns in your datatable, you can see an example here where you can notice the following: view_columns should return a Hash of Hashes. These nested hashes represent our columns; for each column you should specify at least its source (:source key), and if you want to control how to perform its comparison when it comes to filtering, you pass in a :cond key (soon to be renamed :condition) with an Arel symbol representing the comparison to perform at the db level (eq, gt, gteq, lt, lteq)

This last feature has just been merged, and it represents a major surgery in the gem internals, which lead to breaking the build entirely. The gem works, but all tests need to be re-written, I have to find the time. You are welcome to try this branch in a sample pet app, to get a hold of the new syntax.

Before I finish, I'd like to comment on the

I will likely have to fork updates

part of your message.

I will be glad and humbled if you'd be interested in getting involved in planning, developing, features addressing edge scenarios :)
I don't have a complete picture on scenarios that developers run into when trying to solve a particular feature in their apps by using this gem, and all the help I can get (by means of reporting, or better yet, code contributions) is highly appreciated.

So, my question to you is, would you be interested in helping shape this gem's API and functionality?

[aldefouw]

Hi Antonio -

Thanks for your response. I apologize for the delay in getting back to you. It's been a busy week here since Monday was a federal holiday, and we're trying to fit a week's worth of work into 4 days.

I appreciate your explanation and reassurance that the alias functionality will not be removed in the future. That makes us breathe a bit easier.

With regard to your offer to be involved in the direction that the gem is headed, thank you very much for the offer. That is very kind.

I think there's a strong possibility that we could continue to help report issues we run into.

That said, we have dozens of projects that we work on for the department we are part of, and we only use DataTables in a few of them - many applications we work on are legacy PHP applications. So I'm not sure how frequent our feedback would be for you; but, we're happy to contribute when we can!

With regard to the updates to the branch on v0-4-0, I'm curious how those changes would impact our current code.

It looks like there may be significant changes to our code because of how we're using it. In particular, the changes to the sorting and searching methods - where they are consolidated into view_columns may be problematic. That's probably just for how we are using it though ...

Below is a README that I wrote up for adding new datatables to our application.

We dynamically set column names and decide whether we are going to sort or search on a column within the hash for that column that we've defined.

It's more complicated than most people have, but it enables us to keep our JavaScript code very tidy. We have somewhere in the neighborhood of 50 separate DataTables but it only requires one call in Javascript because we're using Ruby to generate all of the Javascript we need for the different column parameters and such.

Below is a README that I created for the project so other developers can add to our project. Unfortunately, I can't point you to a repository that includes our project because it isn't open source, but this should give you an idea of how our unique situation is structured.

Perhaps some of these aspects could be handled more gracefully on the Gem level, but I wasn't really sure how to approach it on that level. Perhaps this will give you ideas if nothing else.

Readme for our specific project integration

The instructions below will cover how to

• Create a new Specific Filter
• Create a new Base Filter

The general purpose of this document is to cover the basics of implementing a DataTable in our project. There isn't much code involved, but there are some important aspects to understand before diving in.

---

Basic Facts

Most of the code needed to create or modify DataTables Filters is contained within the /app/datatables folder.

Model methods will need to be created in the respective model file (e.g. Award models in /app/models/award.rb).

Helper methods should be placed in the respective helper file (e.g. Award helpers in /app/datatables/filters/award/award_helpers.rb).

Methods that are common to all datatables are located in /app/datatables/common_datatables.rb.

---

Create a Specific Filter

These instructions will demonstrate how to add a Specific Filter to Award. Although these instructions focus on 'Award', for Notes or Project, it is the same process - just use 'notes' or 'project' instead of 'award'.

Note: This example is under the assumption we are creating an Award filter called FooBar.

What are the requirements for a Specific Filter to work?

Specific Filters have three requirements to make them work:

1. A Query Method in the respective model (e.g. Award) that selects the data you want
2. A dynamically created Route that has the same Query Method name as its Route name
3. A Class definition for the Specific Filter that defines the Base Filter we are inheriting functionality from

What exactly is a Specific Filter?

A Specific Filter is essentially the specific implementation of a DataTable (which is defined within a Base Filter). Specific Filters are accessible through a route that you can type into your web browser.

Each section (Award, Project, Notes) uses a standard view implementation, and each Specific Filter utilizes this view implementation without any extra code necessary. (For instance, you can see the Award view implementation used for all Specific Filters in /app/views/awards/index.html.erb.)

Naming conventions help everything come together.

Implementation of FooBar Specific Filter using DefaultAwardFilter Base Filter

Requirement	Name	File Required	Code Type	New File?	Notes
Query	foo_bar	/app/models/Award.rb	Method	No	The method contains a Query to the database
Route	foo_bar	/config/initializers/constants.rb	Array Element	No	Code is placed in AWARDS_FILTER array
Specific Filter	AwardFooBarFilter	/app/datatables/filters/award/specific_filters/award_foo_bar_filter.rb	Class Definition	Yes	Inherits from DefaultAwardFilter
Base Filter	DefaultAwardFilter *	/app/datatables/filters/award/base_filters/foo_norf_filter.rb	Class Definition + Methods	Yes	No inheritance

* Base Filter does not have to be DefaultAwardFilter as you will see in the next section ().

As you can see, the Query Method name for a given filter needs to match the Route name. ALWAYS. If they do not, your implementation will not work.

Additionally, the Specific Filter filename (i.e. award_foo_bar_filter) will ALWAYS be "sandwiched" between "award" and "filter" in the Class name and the filename.

These requirements will make more sense as you step through the tutorial.

#1 - Create a method in /app/models/award.rb called foo_bar

def self.foo_bar(inputs)
    entire(inputs)
end

You need to fill the method with whatever Award data you want in the DataTable. Since we are going to inherit from DefaultAwardFilter, the entire method is a good choice.

#2 - Create a route for this filter in the following file: /config/initializers/constants.rb

Routes are dynamically created for Awards (using the values in AWARD_FILTERS). The same is true for Project or Notes - they just use PROJECT_FILTERS and NOTE_FILTERS, respectively.

#3 - Add foo_bar to the AWARD_FILTERS array in /config/initializers/constants.rb

This text addition will enable a route located at /awards/foo_bar

AWARD_FILTERS = %w(
    foo_bar
    budget_balance_overdrawn
    budget_balance_overdrawn_cl
    budget_balance_overdrawn_grant
    ...
)

NOTE : Don't copy and paste the above code. It will break other routes. Add foo_bar to the top of the existing array list.

#4 - Create a file called award_foo_bar_filter.rb in this folder: /app/datatables/filters/award/specific_filters/

This file serves one purpose: To tell Ruby what Base Filter your Specific Filter inherits from.
Thus, your Specific Filter file will ALWAYS contain a class definition, and no methods.

Your filter will currently inherit from the DefaultAwardFilter Base Filter.

Since DefaultAwardFilter is already in place by default, all we need to do is reference it in our file. (The next section covers how to ... )

#4 - Add your class definition to award_foo_bar_filter.rb

Use the following code (and save) in the file:

class AwardFooBarFilter < DefaultAwardFilter
end

#5 - Test your FooBar filter

At this point, you should have a new Specific Filter implementation of the DefaultAwardFilter (Base Filter) .

http://localhost:3000/awards/foo_bar

Since DefaultAwardFilter is also used in other Specific Filter implementations, you should see the same content as http://localhost:3000/awards.

If you do not, you may want to review the steps above.

IMPORTANT NOTE: All Specific Filters MUST inherit from a Base Filter.

---

Create a Base Filter

This tutorial assumes that you've already completed the Create a Specific Filter tutorial. This tutorial is dependent upon those files being in place. Please complete before proceeding.

Why use Base and Specific Filter hierarchies?

The idea behind the Base and Specific Filter hierarchy is that Specific Filters inherit functionality from common Base Filters located in each section (Award, Project, Notes).

The reason that Specific Filters inherit from Base Filters is simple: Base Filters follow the DRY principle by allowing us to define the columns of a DataTable one time for use in several different Specific Filters.

This promotes re-use and keeps code to a minimum.

Implementation of FooBar Specific Filter using FooNorf Base Filter

Requirement	Name	File Required	Code Type	New File?	Notes
Query	foo_bar	/app/models/Award.rb	Method	No	The method contains a Query to the database
Route	foo_bar	/config/initializers/constants.rb	Array Element	No	Code is placed in AWARDS_FILTER array
Specific Filter	AwardFooBarFilter	/app/datatables/filters/award/specific_filters/award_foo_bar_filter.rb	Class Definition	Yes	Inherits from FooNorfFilter
Base Filter	FooNorfFilter *	/app/datatables/filters/award/base_filters/foo_norf_filter.rb	Class Definition + Methods	Yes	Inherits from DefaultAwardFilter

The only real difference we are going to see in this tutorial versus the first tutorial is that the Base Filter will be different.

The steps below cover how to create the new Base Filter called FooNorf.

#1 - Add the FooNorf Base Filter

a. Go to /app/datatables/filters/award/base_filters.
b. Add a file called foo_norf_filter.rb.
c. Add the following code (and save):

class FooNorfFilter < DefaultAwardFilter

     def define_columns
       [
         :col_1,
         :col_2,
         :col_3
       ]
     end

end

NOTE: In this example, FooNorfFilter inherits from DefaultAwardFilter. If there was a different Base Filter you would prefer to inherit from, you could specify that in place of DefaultAwardFilter in the code.

#2 - Learn about column definitions in Award Headers and Body

a. Open /app/datatables/filters/award/award_headers_and_body.rb.

b. Each column (i.e. col_1, col_2, col_3) that you define in the define_columns method (see the first step) needs instructions for how it will be displayed.

Each column has two components:

• Header
• Body

The column's Header contains an attributes hash that describes the column header.

Below are the valid attributes that the Header hash accepts.

• header - String attribute containing the text that will be displayed on the top of the column.
  sample: "Column_1"
• col - String attribute describing the database column referenced for the purpose of Sorting or Searching.
  sample: "Award.col_1"
• export - Boolean attribute that determines whether the column will be displayed in the PDF or CSV.
  sample: true, false
• sort - Boolean attribute that will enable or disable sorting for this column.
  values: true, false
• search - Boolean attribute that will enable or disable searching for this column.
  values: true, false

#3 - The column's Body contains the content that will be displayed in each row of the DataTable for that column.

#4 - Add the code below to /app/datatables/filters/award/award_headers_and_body.rb.

This code will add Header and Body definitions for the three columns that are specified in your Base Filter definition in the first step:

# === COL 1 === #
def col_1_header
{
     header: "Award Type",
     col: 'Award.award_type',
     export: true,
     sort: true,
     search: true
}
end

def col_1_body(award)
     award.award_type
end

# === COL 2 === #
def col_2_header
{
     header: "Sponsor Name",
     col: 'Award.award_sponsor_name',
     export: true,
     sort: true,
     search: true
}
end

def col_2_body(award)
     award.award_sponsor_name
end

# === COL 3 === #
def col_3_header
{
     header: "Primary PI Name",
     col: 'Award.award_primary_pi_name',
     export: true,
     sort: true,
     search: true
}
end

def col_3_body(award)
     award.award_primary_pi_name
end

#5 - Change the Base Filter that your FooBar Specific Filter inherits from.

• Open /app/datatables/filters/award/specific_filters/award_foo_bar_filter.rb
• Replace DefaultAwardFilter with FooNorfFilter
• This will make your Specific Filter inherit from your newly created FooNorf Base Filter

#6 - Test your FooBar filter.

Visit http://localhost:3000/awards/foo_bar

At this point, you should see columns labeled Award Type, Sponsor Name, and Primary PI Name in your FooBar filter. You should also see rows of data in each column.

Additionally, you should be able to sort and search on any of the 3 fields. Finally, all three columns should be exportable to PDF or CSV.

If you don't see these items, retrace your steps and see where things went wrong.

[ajahongir]

@aldefouw It seems you concept is quite difficult, I dont know what type of project you have but its to hard define datatable in you senario. can you please look at this sample(https://github.com/ajahongir/ajax-datatables-rails-v-0-4-0-how-to) project and tell is this way acceptable for you?

[aldefouw]

Sure - I will check it out! It might be a few days though - I have a busy week ahead of me.

[n-rodriguez]

@antillas21 I'm testing the new v-0-4-0 branch. It's working quite well except what it seems to be a bug in search. But now I have an objection : I'm not sure that passing the searchable value (yes|no) by view params is a good idea for security reasons.

To be honest, at first I thought the searchable boolean was set in view_columns definition :

def view_columns 
  @view_columns ||= {
    0 => { source: 'User.id', searchable: false },
    1 => { source: 'User.first_name' },
    2 => { source: 'User.last_name' },
    3 => { source: 'User.email' }
  }
end

IMHO it would be more logical to set searchable/orderable settings here.

[ajahongir]

@n-rodriguez Its good argument. I agree with security reasons. May be it will much better to define searchable option as optional?

def view_columns 
  @view_columns ||= [
    { source: 'User.id', searchable: false },
    { source: 'User.first_name' },
    { source: 'User.last_name' },
    { source: 'User.email' },
  ]
end

https://github.com/antillas21/ajax-datatables-rails/blob/v-0-4-0/lib/ajax-datatables-rails/datatable/column.rb#L15

def searchable?
  @view_column[:searchable] || options[:orderable] == 'true'
end

[n-rodriguez]

May be it will much better to define searchable option as optional?

For people who want to manage this param from the view, yes. I'm gonna try it.

[aldefouw]

@antillas21 - Unfortunately, the other projects I am working on are consuming all of my time at work. I want to look at the branch for you - just haven't had the time yet. Getting pulled in multiple directions on many things.

At the worst, we can just lock down our version in our Gemfile and continue using the version we are using now. We have such a specific need in our datatables that it might not be easy to maintain compatibility moving forward.

[n-rodriguez]

@antillas21 I've done some tests and it doesn't work :

def searchable?
  @view_column[:searchable] || options[:orderable] == 'true'
end

==

def searchable?
 false|| true
end

==

always true

Because options[:orderable] == 'true' is always true unless you set it in the view with :

%th.no-sort.no-search User.human_attribute_name('phone_number')

and

  $(element).dataTable
    ajax: $(element).data('source')
    columnDefs: [{ targets: 'no-sort', orderable: false }, { targets: 'no-search', searchable: false }]

which doesn't solve the issue.

[antillas21]

@n-rodriguez if you declare columns attributes in your JS code, you can make columns not orderable, example:

jQuery(document).ready(function() {
  $('#users-table').dataTable({
    processing: true,
    serverSide: true,
    ajax: $('#users-table').data('source'),
    pagingType: "full_numbers",
    columns: [
      { searchable: true, sortable: false },
      { searchable: false, sortable: false },
      { searchable: true, sortable: true }
    ]
  });
});

passing false to the sortable definition, prevents a column from not being used when sorting/ordering records, and the same happens when passing false to searchable, it prevents the column from being used in the filtering queries.

By default, like you said, all columns are both sortable and searchable... but you can override this behavior 😉

[antillas21]

@aldefouw No worries, contributing to open source is always like that, trust me... I have some ideas for this library, just need to find the time between the projects I'm currently working on.

That being said, whenever you have a time and if you're still interested in contributing a solution, I will be grateful and willing to take a look at your proposed changes 😄

[n-rodriguez]

@n-rodriguez if you declare columns attributes in your JS code, you can make columns not orderable, example:

Yep, I've updated my post

[mcnelson]

So if I'm reading this right, support for aliased tables or columns is going to come back? Currently in 0.3.1 they do not work for searchable_columns as it always tries to make a ConstantName out of the alias. Being able to choose between Foo.bar and foos.bar would be fantastic!

[raghuvarmabh]

+1 for mcnelson

[n-rodriguez]

Hi there! Any news?

[SampsonCrowley]

is there an update on this? I need to be able to use custom aliased join tables. not just ActiveRecord Constants in my view columns

[SampsonCrowley]

currently

deposit_date: {source: 'deposit_date'}, (notice no constant or table name)
deposit_date: {source: 'deposits.deposit_date'}, (notice aliased, joined table name)
deposit_date: {source: 'Deposit.deposit_date'}, (notice: Constantized version of aliased table)

ALL throw an error

[n-rodriguez]

@SampsonCrowley
I think deposit_date: {source: 'deposit_date'}, should work : https://github.com/ajahongir/ajax-datatables-rails-v-0-4-0-how-to/blob/master/app/datatables/city_datatable.rb

[n-rodriguez]

I'm closing this issue since most of it is implemented in v0.4 and the rest (filters, etc) seems too complex to be done for now. I'm more in favor of stabilizing the API (see #288) and reducing coupling so the gem could be used elsewhere than adding new features.

@SampsonCrowley : feel free to open a new issue if it's still not working

[SampsonCrowley]

@n-rodriguez it sort of does. I had to add custom cond and formater (i hate that there is only one t there btw, should be formatter) options but it's good enough

[n-rodriguez]

i hate that there is only one t there btw, should be formatter

yeah, it's fixed :) cb8a3da

[n-rodriguez]

but it's good enough

great :)