No operator matches the given name and argument types. You might need to add explicit type casts.

[arlharis]

Hi, can anyone help me how to solve this issue?

I'm stuck with this problem for almost a whole day now. My datatable is working properly then I decided to use custom columns. Im using the method filter

I'm getting this error when I type anything in the search box

ActiveRecord::StatementInvalid (PG::UndefinedFunction: ERROR:  operator does not exist: boolean ~~* unknown
LINE 1: ...rchases"."total" AS VARCHAR) ILIKE '%q%') OR paid ILIKE 'q%'...
                                                             ^
HINT:  No operator matches the given name and argument types. You might need to add explicit type casts.
):

Here is my xxxx_datatable.rb

class PurchaseDatatable < AjaxDatatablesRails::ActiveRecord
. 
.
.
 def view_columns
    @view_columns ||= {
      id: { source: 'Purchase.id' },
      order_no: { source: 'Purchase.order_no' },
      purchase_items: { source: 'Purchase.purchase_items', searchable: false, orderable: false },
      status: { source: 'Purchase.status' },
      total: { source: 'Purchase.total' },
      paid: { source: 'Purchase.paid', cond: filter_custom_column_condition },
      charges: { source: 'Charge.bill_id' },
      coupon_code: { source: 'Purchase.coupon_code' },
      created_at: { source: 'Purchase.created_at' }
    }
  end

  def data
    records.map do |record|
      {
        id: record.id,
        order_no: record.order_no.upcase,
        purchase_items: record.purchase_items.count,
        status: record.status.humanize,
        total: as_currency(record.total),
        paid: record.paid ? 'Paid' : 'Pending',
        charges: record.charges.last.try(:bill_id),
        coupon_code: record.coupon_code,
        created_at: record.created_at.strftime('%e-%b-%Y'),
        show: link_to('', admin_purchase_url(record), class: 'btn btn-alive rounded-0 fas fa-arrow-circle-right'),
        DT_RowId: record.id
      }
    end
  end

  def get_raw_records
    Purchase.joins(:charges).all
  end

  def filter_custom_column_condition
     ->(column, value) { ::Arel::Nodes::SqlLiteral.new(column.field.to_s).matches("#{ column.search.value }%") }
  end

  def as_currency(value)
    number_to_currency(value, unit: '')
  end
end

[matissg]

By which column you're trying to filter data and what's that column data type?

[arlharis]

By which column you're trying to filter data and what's that column data type?

under paid column and the column data type is boolean

By default, the data will return to true and false, so im use paid: record.paid ? 'Paid' : 'Pending', under mapping section to change it. But when i try to search paid or pending in search bar. Nil result. So i try to use the filter method.

[matissg]

Take a look on my example, where I have User with activated as boolean:

  def view_columns
    @view_columns ||= {
      activated: { source: "User.activated", cond: :like }
    }
  end

  def data
    records.map do |user|
      {
        id: user.id,
        activated: user.activated
      }
    end
  end

  def get_raw_records
    User.all
  end

For filtering I'm passing in "true" or "false", however for labeling those choices I can name whatever - "Activated", "Not Activated".

[arlharis]

How u do the custom labelling ( Activated and Not Activated ) ? Did u define it .js file? Im not clear, sorry.

I already tried your example by using cond: like .But still no match records found result when i search the data. also tried to remove the ? 'Paid' : 'Pending' in the record.map section. Result still same.

[matissg]

I use YADCF plugin where you define your labeling in JS file. Just checked my example and I can confirm it works. You can use same approach for this as well: menu_role: { source: "User.menu_role", cond: :like } and then pass in seller, buyer or whatever you have.

[arlharis]

Can u attach your JS file for my reference? Thank you in advance.

[matissg]

I can place here snippet, which is something like this:

    var adminUsersTable = $('#users-table').DataTable();
    yadcf.init(adminUsersTable, [
      {
        column_number: 4,
        data: [
          {
            value: "seller",
            label: I18n.t("roles.seller")
          },
          {
            value: "buyer",
            label: I18n.t("roles.buyer")
          }
        ],
        filter_type: "select",
        style_class: "form-control",
        reset_button_style_class: "btn btn-outline btn-danger",
        filter_reset_button_text: "Clean",
        filter_default_label: "Select"
      },
      {
        column_number: 5,
        data: [
          {
            value: "true",
            label: "Approved"
          },
          {
            value: "false",
            label: "Not approved"
          }
        ],
        filter_type: "select",
        style_class: "form-control",
        reset_button_style_class: "btn btn-outline btn-danger",
        filter_reset_button_text: "Clean",
        filter_default_label: "Select"
      },
    ]);

If you use Turbolinks, I suggest you wrap this in something like this: if ($('#users-table').is(':visible')) - otherwise I think you'll get error since that var adminUsersTable is not defined for YADCF when you start your app.

[matissg]

Hi,

You need to:

1) require yadcf JS and CSS files 2) do according to my example + check yadcf examples - http://yadcf-showcase.appspot.com/

For select dropdown to pass in your predefined values there has to be "value" and "label" - see my example.

Matiss

On 27.09.2019 4:47, arlharis wrote:

My original JS file is like this,

|jQuery(document).on('turbolinks:load', function() { $('#purchases-datatable').dataTable({ order: [[ 0, 'desc' ]], 'processing': true, 'serverSide': true, 'ajax': $('#purchases-datatable').data('source'), 'pagingType': 'full_numbers', 'columns': [ {'data': 'id'}, {'data': 'order_no'}, {'data': 'purchase_items'}, {'data': 'status'}, {'data': 'total'}, {'data': 'paid'}, {'data': 'charges'}, {'data': 'coupon_code'}, {'data': 'created_at'}, {'data': 'show'} ] }); }); |

and the output will be like this image https://user-images.githubusercontent.com/53414907/65735921-b9d2ca80-e10b-11e9-8554-b310b2a78df2.png

Then i'll change it to this,

|jQuery(document).on('turbolinks:load', function(){ //Old datatable API (lowercase "D") $('#purchase-datatable').dataTable().yadcf([ { column_number : 0, data: ["id"] }, { column_number : 1, data: ["order_no"] }, { column_number : 2, data: ["purchase_items"] }, { column_number : 3, data: ["status"] }, { column_number : 4, data: ["total"] }, { column_number : 5, data: [ { value: "true", label: "Paid" }, { value: "false", label: "Pending" } ] }, { column_number : 6, data: ["charges"] }, { column_number : 7, data: ["coupon_code"] }, { column_number : 8, data: ["craeted_at"] }, { column_number : 9, data: ["show"] }, ]); //New datatable API (capital "D") if ($('#purchase-table').is(':visible')){ var myTable = $('#purchase-datatable').DataTable(); yadcf.init(myTable, [ { column_number : 0, data: ["id"] }, { column_number : 1, data: ["order_no"] }, { column_number : 2, data: ["purchase_items"] }, { column_number : 3, data: ["status"] }, { column_number : 4, data: ["total"] }, { column_number : 5, data: ["paid"] }, { column_number : 6, data: ["charges"] }, { column_number : 7, data: ["coupon_code"] }, { column_number : 8, data: ["craeted_at"] }, { column_number : 9, data: ["show"] }, ]); })}; |

No output appear

Am i doing it wrong?

— You are receiving this because you commented. Reply to this email directly, view it on GitHub https://github.com/jbox-web/ajax-datatables-rails/issues/341?email_source=notifications&email_token=AGRKRH7TGA6MKA2TM7MBWHTQLVQ33A5CNFSM4I2UXNYKYY3PNVWWK3TUL52HS4DFVREXG43VMVBW63LNMVXHJKTDN5WW2ZLOORPWSZGOD7XOFJQ#issuecomment-535749286, or mute the thread https://github.com/notifications/unsubscribe-auth/AGRKRHZRVQNWLEMIVFFTM23QLVQ33ANCNFSM4I2UXNYA.

[arlharis]

Thank you so much for your help, guide and time @matissg . Appreciate it much .

For my problem, I fixed it by use the formatter method to filter the search bar, im sure using the yadcf also can fix it.

In my purchase-datatable.rb

class PurchaseDatatable < AjaxDatatablesRails::ActiveRecord
.
.
.
def view_columns
    @view_columns ||= {
      .
      .
      .
      status: { source: 'Purchase.status', formatter: filter_status_column_condition },
      total: { source: 'Purchase.total' },
      paid: { source: 'Purchase.paid', formatter: filter_paid_column_condition },
      .
      .
      .
    }
  end

and for the Proc ( filter_paid_column_condition & filter_status_column_condition ) under the same file.

def filter_paid_column_condition
    -> (value) { if value.titleize  == 'Pending'
          false
        elsif value.titleize  == 'Paid'
          true
        else
          value
        end }
  end

  def filter_status_column_condition
    -> (value) { if value.titleize  == 'Received'
          0
        elsif value.titleize  == 'Processing'
          1
        elsif value.titleize  == 'Shipped'
          2
        elsif value.titleize  == 'Cancelled'
          3
        else
          value
        end }
  end