README: show 'data' method for "Associated and nested models"

[listx]

It's unclear from reading the README how to define the data method when dealing with associated/nested models. The get_raw_records example is shown, but not for data. I mean, how would the records.map function work in the context of associated/different models?

[antillas21]

Yeah, maybe you're right... the overall idea is this:

Suppose you have a Client model, who has an associated ShippingAddress model. This ShippingAddress has a method named label_friendly which outputs an address as a one line string, example: 123 Some St., Smallville, KS, 12345... pretty much this:

# app/models/client.rb
class Client < ActiveRecord::Base
  has_one :shipping_address
  has_one :billing_address
end

# app/models/address.rb
class Address < ActiveRecord::Base
  belongs_to :client

  def label_friendly
    [address_line1, address_line2, city, state, zip].compact.join(",")
  end
end

# app/models/shipping_address.rb
class ShippingAddress < Address
end

# app/models/billing_address.rb
class BillingAddress < Address
end

So, suppose you need to output a dataTables enhanced table in your view that displays a client name, client status, client shipping address, client billing addres (as a one liner each), and the usual show, edit, delete actions. You would do something like this:

# app/datatables/client_datatable.rb
class ClientDatatable < AjaxDatatablesRails::Base
  # other methods and config not shown for brevity

  def get_raw_records
    # Rails 4+
    Client.includes(:shipping_address, :billing_address).all
    # Rails 3
    # Client.includes(:shipping_address, :billing_address).scoped
  end

  def data
    records.map do |record|
      [
        record.name, # client name
        record.status, # client status
        record.billing_address.label_friendly, # billing address data
        record.shipping_address.label_friendly, # shipping address data
        link_to("show", client_path(record)), # show link
        link_to("edit", edit_client_path(record)), # edit link
        link_to("delete", client_path(record), method: :delete, data: { confirm: "Are you sure?" }) # delete link
      ]
    end
  end
end

Notice the following, in the get_raw_records method, we are defining that our point of entry to the query is the Client model... so, the end product of the method are instances of the Client model, and are assigned to an internal variable: records.

From there, it's a simple matter of calling the corresponding methods on those instances to return the data we need.... remember, as we are using ActiveRecord to query the database, and the end product are always instances of a model that inherits from ActiveRecord (enclosed in an ActiveRecord::Relation) and not raw records from the database, then all methods (and relations) are available to be called inside the datatable class... the only thing you should put special attention to, is to add the proper joins or includes in your query inside the get_raw_records method, to create an optimized SQL query and avoid an N+1 scenario, specially because you'll be calling methods on associated models.

The same applies for nested models... just put special attention on the models relationships and the corresponding code inside the joins or includes calls, as explained here: https://github.com/antillas21/ajax-datatables-rails#associated-and-nested-models , once you have the resulting object inside the records variable, just call the corresponding methods to access the data you need.

It is worth mentioning that having delegate methods in ActiveRecord models, help greatly to clean method chaining.

Hope this helps.

[listx]

Thank you, that does help. To summarize, it's necessary to define the right methods in the respective model classes so that we can call those methods from the record.whatever syntax in #data.

But I have a related question --- in the README, all of the examples have the same order of values in #sortable_columns, #searchable_columns, and #data. Does the ordering matter?

I.e., is

@sortable_columns ||= ['foo.bar', 'foo.quux']

different from

@sortable_columns ||= ['foo.bar', 'foo.quux']

?

I only ask because the README is silent about this, while following a convention about keeping everything in the same order across sortable_columns, searchable_columns, and data.

[listx]

I forgot to ask a more general question --- how does this gem know which columns in def sortable_columns to associate with which records in def data? Is it based on the ordering of the definitions?

[antillas21]

Yes, ordering is important... remember that the end product, is a jQuery dataTables enhanced <table> in an html view... this table, has a specific order in its columns, right?

So, you start from there... from the order you will display columns in this table, and map that to the corresponding methods in the datatable class: sortable_columns, searchable_columns, data. Example:

From the example above in my previous comment... suppose you need to display a table like this:

Client Name	Client Status	Billing Address	Shipping Address
John Doe	active	123 Some St., Smallville, KS, 12345	456 Some St., Smallville, KS, 12345	show	edit	delete
Jane Doe	active	123 Some St., Smallville, KS, 12345	456 Some St., Smallville, KS, 12345	show	edit	delete

Then, mapping columns in our table to our datatable class:

# app/datatables/clients_datatable.rb
class ClientsDatatable < AjaxDatatablesRails::Base
  def sortable_columns
    @sortable_columns ||= [
      'Client.name', 'Client.status', 'BillingAddress.address_line1',
      'ShippingAddress.address_line1'
    ]
  end

  def searchable_columns
    @searchable_columns ||= [
      'Client.name', 'Client.status', 'BillingAddress.address_line1',
      'ShippingAddress.address_line1'
    ]
  end

  def data
    records.map do |record|
      [
        record.name, # client name
        record.status, # client status
        record.billing_address.label_friendly, # billing address data
        record.shipping_address.label_friendly, # shipping address data
        link_to("show", client_path(record)), # show link
        link_to("edit", edit_client_path(record)), # edit link
        link_to("delete", client_path(record), method: :delete, data: { confirm: "Are you sure?" }) # delete link
      ]
    end
  end

  def get_raw_records
    # will need to use joins here to allow ActiveRecord to search in fields
    # from related database tables
    Client.joins(:billing_address, :shipping_address).all
  end
end

Notice I am cheating a little bit here, as an Address model won't have a label_friendly field in its database table... so I am choosing what could be the closest thing to get sorting and searching functionality, the first line of an address (for the sake of brevity). Of course you can use Arel or .select with raw SQL to concat and alias fields from the database and use the alias name in the sortable_columns, searchable_columns methods.

Now in the view, as I won't be interested in searching and sorting by the columns that contain the show, edit, delete links... I would do the following:

<table id="clients-table" data-source="<%= clients_path(format: :json) %>">
  <thead>
    <tr>
      <th>Client Name</th>
      <th>Client Status</th>
      <th>Billing Address</th>
      <th>Shipping Address</th>
      <th></th>
      <th></th>
      <th></th>
    </tr>
  </thead>
  <tbody></tbody>
</table>

$('#clients-table').dataTable({
      processing: true,
      serverSide: true,
      ajax: $('#clients-table').data('source'),
      columns: [
        { sortable: true, searchable: true },
        { sortable: true, searchable: true },
        { sortable: true, searchable: true },
        { sortable: true, searchable: true },
        { sortable: false, searchable: false },
        { sortable: false, searchable: false },
        { sortable: false, searchable: false }
      ]
    });

[listx]

Thank you for confirming that ordering is important. I do believe this info is absent in the README.

As for the original issue, there's obviously more than enough info regarding def data in the context of complex models. I believe you can close this issue, although to be fair most of the things should be converted and put into the README.

[antillas21]

@listx would you like to contribute an enhancement to the README file to properly explain this?... to be honest, it would be a little hard to do this for me, as I will fall in the typical case of developer short-sight.

What do you say? would you like to help me improve documentation?

[listx]

Sure, why not? I would like to fill in some holes where I can find them. Currently I am using this gem in a larger project (sadly, it is a private repo https://github.com/LearnToBeFoundation).

I'm currently wrestling with implementing a datatable, but once I'm done with that (later this week at the latest, hopefully), I'll gladly contribute some patches to the README, distilling what I've learned.

[antillas21]

👍 excellent! Let me know if there's anything else I may help you clarify.

Looking forward to your contribution!

P.S. I will close this issue now.

[listx]

Hmm, well, now that #84 is being considered for 0.4.0, I guess any clarifications in the README will have to be specifically labeled as for the current 0.3.x release.

[listx]

I made #85. The changes are minimal, but hopefully it will save other developers some time.

The complex datatable I was working on ended up not being searchable/sortable at all, because the single column called 'Name' depended on two different models. I am not enough of an SQL wizard to combine two different SQL tables' columns into a single "column" that this gem expects.

Anyway, I've also made some edits to the Wiki that repeats some of the stuff in the pull request.

[Turkish]

@antillas21 Regarding ordering, what if i have a column that i don't want to filter positioned between two columns that i want to filter ? For example in the example you gave, what if i don't want BillingAddress to be searchable , but yet remain positioned between Client Status and Shipping Address in the view. Is that possible to do by doing it like this :

$('#clients-table').dataTable({
      processing: true,
      serverSide: true,
      ajax: $('#clients-table').data('source'),
      columns: [
        { sortable: true, searchable: true },
        { sortable: true, searchable: true },
        { sortable: false, searchable: false }, // put  falses for Billing address
        { sortable: true, searchable: true },
        { sortable: false, searchable: false },
        { sortable: false, searchable: false },
        { sortable: false, searchable: false }
      ]
    });

#removed  Billing address from sortables
 def sortable_columns
    @sortable_columns ||= [
      'Client.name', 'Client.status',
      'ShippingAddress.address_line1'
    ]
  end

#removed Billing address from searchables
 def searchable_columns
    @searchable_columns ||= [
      'Client.name', 'Client.status',
      'ShippingAddress.address_line1'
    ]
  end

Does this works ? or is it necessary to have all your searchables first, followed by un-searchables ?

[antillas21]

@YoussefTourki no, unfortunately that won't work. The columns inside sortable_columns and searchable_columns must map exactly to your html view columns.

Also... I've reworked some of the internals of the gem, removing the need to declare to separate methods for columns (sortable and searchable) in favor of declaring just one method view_columns, yet again, the columns declared in this method need to match exactly your html view columns. If you like, you can test drive this in your project by adding to your Gemfile:

gem "ajax-datatables-rails", github: "antillas21/ajax-datatables-rails", branch: "v-0-4-0"

[bbonte]

@antillas21 In your comment on Feb 1 you said the following:

Notice I am cheating a little bit here, as an Address model won't have a label_friendly field in its database table... so I am choosing what could be the closest thing to get sorting and searching functionality, the first line of an address (for the sake of brevity). Of course you can use Arel or .select with raw SQL to concat and alias fields from the database and use the alias name in the sortable_columns, searchable_columns methods.

Based on your example I managed to display the label_friendly full address (with city, state,... included) in my DataTable. In your example the filtering (searching) and sorting happens only for the address_first_line field. But what if I also want to be able to search for a city or state name? You said you can use Arel or .select to concat and alias fields from the database and use the alias name in the sortable_columns and searchable_columns, can you maybe give an example of how to do this?

[ajahongir]

guys, you look at branch https://github.com/antillas21/ajax-datatables-rails/tree/v-0-4-0 - README not fully updated but its under development. also there is sample project https://github.com/ajahongir/ajax-datatables-rails-v-0-4-0-how-to

Now Iam improving searching and filtering on this gem so its importand you opinion. Take a look if you have a chance 😄

[bbonte]

@ajahongir Can you give me an example of how to solve the problem I have? In my specific case I have 2 database fields profile.first_name and profile.last_name I want to show together in 1 column as full_name. I can display them by making a method in the Profile model class to concatenate first_name and last_name, but I can't get searching & sorting to work... I think this can be done by using the right .select statements and using aliases, but I still didn't get it to work...

[bbonte]

I got sorting to work by using the following query:

Order.where({status: 0}).joins(:profile).select("orders.*", "concat(profiles.first_name, ' ', profiles.last_name) as fullname").all

I then use the alias 'fullname' in the data method and the @sortable_columns, @searchable_columns.

@searchable_columns ||= [
        'fullname',
        'Order.value',
        'Order.created_at',
        'Order.updated_at'
    ]

def data
    records.map do |record|
      [
        record.fullname,
        record.value,
        record.created_at.strftime('%d-%m-%Y %T'),
        record.updated_at.strftime('%d-%m-%Y %T')
      ]
    end
  end

Now displaying the concatenated full_name and sorting on it works, but when I start typing in the DataTable's search field I get the following MySQL error:

Completed 500 Internal Server Error in 8ms (ActiveRecord: 1.4ms)

NameError (uninitialized constant Fullname):
  app/controllers/admin/orders_controller.rb:11:in `block (2 levels) in datatable_new'
  app/controllers/admin/orders_controller.rb:9:in `datatable_new'

It's weird that sorting works but searching breaks... I'm using v3.0.1 since I understood v4 is still in development mode.

[antillas21]

Have you tried declaring fullname as Profile.fullname in the searchable_columns method? as we need to have a valid model name Profile in this case (from your query) to which call a method (fullname from your query).

[bbonte]

Yes, I tried that before but then I get the following error in my Server log:

   (1.1ms)  SELECT COUNT(*) FROM `orders` INNER JOIN `profiles` ON `profiles`.`id` = `orders`.`profile_id` AND `profiles`.`deleted_at` IS NULL WHERE `orders`.`deleted_at` IS NULL AND `orders`.`status` = 0 AND (((CAST(`profiles`.`fullname` AS CHAR) LIKE '%c%' OR CAST(`orders`.`value` AS CHAR) LIKE '%c%') OR CAST(`orders`.`created_at` AS CHAR) LIKE '%c%') OR CAST(`orders`.`updated_at` AS CHAR) LIKE '%c%')
Mysql2::Error: Unknown column 'profiles.fullname' in 'where clause': SELECT COUNT(*) FROM `orders` INNER JOIN `profiles` ON `profiles`.`id` = `orders`.`profile_id` AND `profiles`.`deleted_at` IS NULL WHERE `orders`.`deleted_at` IS NULL AND `orders`.`status` = 0 AND (((CAST(`profiles`.`fullname` AS CHAR) LIKE '%c%' OR CAST(`orders`.`value` AS CHAR) LIKE '%c%') OR CAST(`orders`.`created_at` AS CHAR) LIKE '%c%') OR CAST(`orders`.`updated_at` AS CHAR) LIKE '%c%')
Completed 500 Internal Server Error in 11ms (ActiveRecord: 2.0ms)

ActiveRecord::StatementInvalid (Mysql2::Error: Unknown column 'profiles.fullname' in 'where clause': SELECT COUNT(*) FROM `orders` INNER JOIN `profiles` ON `profiles`.`id` = `orders`.`profile_id` AND `profiles`.`deleted_at` IS NULL WHERE `orders`.`deleted_at` IS NULL AND `orders`.`status` = 0 AND (((CAST(`profiles`.`fullname` AS CHAR) LIKE '%c%' OR CAST(`orders`.`value` AS CHAR) LIKE '%c%') OR CAST(`orders`.`created_at` AS CHAR) LIKE '%c%') OR CAST(`orders`.`updated_at` AS CHAR) LIKE '%c%')):
  app/controllers/admin/orders_controller.rb:11:in `block (2 levels) in datatable_new'
  app/controllers/admin/orders_controller.rb:9:in `datatable_new'

Since fullname is just the alias for my concatenated first_name and last_name fields and doesn't exist in the database as a field itself, the generated search query CAST(profiles.fullname AS CHAR) gives this error...

[LucasCioffi]

@Turkish Why did you remove the def sortable_columns and def searchable_columns methods?

Was it because you added lines like { sortable: true, searchable: true }, to the JavaScript file?