Problem with custom_columns and search

[ejmm320]

I have a problem regarding the searchable functionality of one of my datatables because I added a couple of custom columns in my query (both sum calculations).

First, I had to override the method as_json in the way described here: #159 in order to display the table properly (count and number of rows), but now I'm unable to perform any search.

My first attempt was to override using a custom filter method following this example, I added this code:

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

without luck, both columns are Numeric, I also tried using the cond: :eq as suggested in docs but another error appears.

Checking logs I realized that it looks like the query doesn't consider the custom columns because the name (an alias) is not present. To give an example I put on the search box the number "85" in order to show the generated sql:

SELECT COUNT(*) FROM (SELECT ro_eventos_perdidas.id, ro_eventos_perdidas.fecha_ocurrencia,  
                 ro_eventos_perdidas.descripcion, ro_eventos_perdidas.recuperable, ro_eventos_perdidas.monto_perdida, 
                 coalesce(sum(monto_recuperado),0) as monto_recuperado,
                 coalesce(ro_eventos_perdidas.monto_perdida - coalesce(sum(monto_recuperado),0),0) as monto_pendiente FROM "ro_eventos_perdidas" left outer join ro_recuperaciones_eventos recup on 
                                 recup.ro_evento_perdida_id = ro_eventos_perdidas.id WHERE (ro_eventos_perdidas.monto_perdida > 0 and ro_eventos_perdidas.recuperable = true 
                  and ro_eventos_perdidas.duplicado = false ) AND ((((((CAST("ro_eventos_perdidas"."id" AS VARCHAR) ILIKE '%85%' OR CAST("ro_eventos_perdidas"."fecha_ocurrencia" AS VARCHAR) ILIKE '%85%') OR CAST("ro_eventos_perdidas"."descripcion" AS VARCHAR) ILIKE '%85%') OR CAST("ro_eventos_perdidas"."recuperable" AS VARCHAR) ILIKE '%85%') OR CAST("ro_eventos_perdidas"."monto_perdida" AS VARCHAR) ILIKE '%85%') OR 85 ILIKE '85%') OR 85 ILIKE '85%') GROUP BY ro_eventos_perdidas.id) AS foo

as you can see at the end of this query, it shows OR 85 ILIKE '85%' which is wrong, it should be OR CAST("ro_eventos_perdidas"."monto_recuperado" AS VARCHAR) ILIKE '%85%')... which is one of my custom columns.

I'm not sure if I have to add something else or maybe something is missing, so please point me in the right direction.

Regards.

Stack:

Postgresql 9.4
Rails 4.2.7
jquery-datatables-rails 3.3.0
ajax-datatables-rails 0.4.0

[ajahongir]

hey, try this one
def filter_custom_column_condition
->(column) { ::Arel::Nodes::SqlLiteral.new(column.field.to_s).eq(column.search.value) }
end

[ejmm320]

@ajahongir Thanks for the quick support, but still not working, now I get a:
NoMethodError - undefined method 'field' for "8":String

[ajahongir]

yep, now Proc is recieving just value - not column object and I wonder how it works now 😄

def filter_custom_column_condition
->(value) { value}
end

[ajahongir]

@n-rodriguez can you help to figureout please?

[ejmm320]

@ajahongir, @n-rodriguez still doesn't work... Now I get a RuntimeError - unsupported: String Thanks in advance.

[n-rodriguez]

Hi @ejmm320! Actually it looks like a bug from ajax-datatables-rails.
It was introduced by 92c2254#diff-a7f0f3ee893084549cc5255576b14fe3L38.
At the time passing a value to the filter method without using it was a bit weird to me.
Now I better understand why we pass self to the Proc. This part is not covered by a test, do you have a use case to write one?
It's now fixed : 07795fd
For now you can test it by changing your Gemfile :

gem 'ajax-datatables-rails`, git: 'https://github.com/jbox-web/ajax-datatables-rails.git'

[ejmm320]

Hi @n-rodriguez !, thanks for the quickfix, however I'm still having problems with the filter_custom_column_condition method, and I think is related to the usage of aliased columns.

ro_eventos_perdidas (which is the name of the table that I'm working with) doesn't have a monto_pendiente and monto recuperado columns , they are just calculated for this query that I need to show in the datatable).

Ro::RoEventoPerdida.joins("left outer join ro_recuperaciones_eventos recup on 
                                 recup.ro_evento_perdida_id = ro_eventos_perdidas.id")
        .select('ro_eventos_perdidas.id, ro_eventos_perdidas.fecha_ocurrencia,  
                 ro_eventos_perdidas.descripcion, ro_eventos_perdidas.recuperable, ro_eventos_perdidas.monto_perdida, 
                 coalesce(sum(monto_recuperado),0) as monto_recuperado,
                 coalesce(ro_eventos_perdidas.monto_perdida - coalesce(sum(monto_recuperado),0),0) as monto_pendiente')
          .where("ro_eventos_perdidas.monto_perdida > 0 and ro_eventos_perdidas.recuperable = true 
                  and ro_eventos_perdidas.duplicado = false")
            .group("ro_eventos_perdidas.id")

And I don't know how to change the method in order to fix the search using this aliased fields, I'm able to get the value from the ActiveRecord::Relation using for example: query_result.first['alias_name']. So, maybe this is somehow related to #165 because what I need is to create the correct Arel sentence in order to get the search working.

Please could you give me a hint to fix this? Thanks again for your help!

[ajahongir]

@ejmm320 can you put show your datatable?
howerver, after fixing custom column seraching - now it should work with old version

[ejmm320]

@ajahongir see this: https://gist.github.com/ejmm320/30817ab917f30c68147e126784b97d67, also I tried this way and it doesn't work:

def filter_custom_column_condition
  ->(column) { ::Arel::Nodes::SqlLiteral.new(column.table[column.field]).eq(column.search.value) }
end

[ajahongir]

@ejmm320 looks good, so whats is your latest error after those fixes?
I gues you are having problem with using an alias in where clause?

[ejmm320]

@ajahongir yes the problem was with the where clause and the aliased columns.

The only way that I've found for now was forcing the filter method with raw SQL like this:

def filter_custom_column_condition
  ->(column) { ::Arel::Nodes::SqlLiteral.new("CAST('#{column.field}' AS VARCHAR)").matches("#{ column.search.value }%") }
end

Thanks for pointing me in the right direction, also thanks to @n-rodriguez for the fix related to this issue described before.

[scottysmalls]

@ejmm320 Do you still use the raw SQL solution? I have the same issue and this approach results in the search filtering on the name of my column instead of it's value.

def filter_custom_column_condition
   ->(column, value) {::Arel::Nodes::SqlLiteral.new("CAST('purchase_value' AS VARCHAR)").matches("%#{column.search.value}%") }
end

I wasn't sure it was actually looking at the column, so I just put some gibberish in as the column name expecting it to error when casting it but nothing:

def filter_custom_column_condition
   ->(column, value) {::Arel::Nodes::SqlLiteral.new("CAST('asdfasdfasdfads' AS VARCHAR)").matches("%#{column.search.value}%") }
end

I thought maybe it was just comparing the actual name of the column, 'purchase_value', so i did a search filtering on that string and all rows came up as a result so I think that is the case. I tried switching quotes and apostrophes within the new statement the original issue of UndefinedColumn error appears again.

def filter_custom_column_condition
   ->(column, value) {::Arel::Nodes::SqlLiteral.new('CAST("purchase_value" AS VARCHAR)').matches("%#{ value }%") }
end

@ajahongir, if I understand it correctly there are two queries made during the filter, a count and grabbing the data. It looks like the count query is not including the custom column for some reason. These are my resulting queries for the first code snippet. When I try the third, it errors out in the first query when it hits ...OR CAST("purchase_value'" AS VARCHAR)...

SELECT DISTINCT COUNT(DISTINCT "purchases"."id") AS count_id, purchases.id AS 
purchases_id, vendors.id AS vendors_id FROM "purchases" INNER JOIN "vendors" ON 
"vendors"."id" = "purchases"."vendor_id" AND "vendors"."business_id" = $1 LEFT JOIN 
purchase_line_items ON purchase_line_items.purchase_id = purchases.id WHERE 
"purchases"."business_id" = $2 AND (((CAST("purchases"."reference_number" AS VARCHAR) ILIKE
 '%11%' OR CAST("purchases"."purchase_date" AS VARCHAR) ILIKE '%11%') OR 
CAST("vendors"."name" AS VARCHAR) ILIKE '%11%') OR CAST('purchase_value' AS VARCHAR)
 ILIKE '%11%') GROUP BY purchases.id, vendors.id  [["business_id", 1], ["business_id", 1]]


SELECT  DISTINCT vendors.*, purchases.*, sum(purchase_line_items.price_cents * 
purchase_line_items.quantity*.01) as purchase_value FROM "purchases" INNER JOIN "vendors" ON
 "vendors"."id" = "purchases"."vendor_id" AND "vendors"."business_id" = $1 LEFT JOIN 
purchase_line_items ON purchase_line_items.purchase_id = purchases.id WHERE "purchases"."business_id" = $2 AND (((CAST("purchases"."reference_number" AS VARCHAR) ILIKE
 '%11%' OR CAST("purchases"."purchase_date" AS VARCHAR) ILIKE '%11%') OR 
CAST("vendors"."name" AS VARCHAR) ILIKE '%11%') OR CAST('purchase_value' AS VARCHAR) 
ILIKE '%11%') GROUP BY purchases.id, vendors.id  ORDER BY purchases.reference_number ASC 
LIMIT 10 OFFSET 0  [["business_id", 1], ["business_id", 1]]

[n-rodriguez]

Hi there! Any news?

[reiallenramos]

I'm having a similar problem. The error is

ActiveRecord::StatementInvalid (PG::UndefinedColumn: ERROR:  column "custom_column" does not exist
LINE 4: ...ST("users"."id_number" AS VARCHAR) ILIKE '%g%' OR custom_col...

users_datatable.rb

 def view_columns
    @view_columns ||= {
      id_number:            { source: 'User.id_number' },
      custom_column:    { source: "custom_column", cond: filter_full_name_condition }
    }
  end

  private

  def data
    records.map do |record|
      {
        id_number:          record.id_number,
        custom_column:  record[:custom_column]
      }
    end
  end

  def get_raw_records
    User.select("users.*, concat(users.first_name, ' ', users.last_name) AS custom_column")
  end

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

[reiallenramos]

Casting worked for me: I had to change the proc to:
->(column, value) { ::Arel::Nodes::SqlLiteral.new("CAST('#{column.field}' AS VARCHAR)").matches("#{ column.search.value }%") }

[bschrag]

->(column, value) { ::Arel::Nodes::SqlLiteral.new("CAST('#{column.field}' AS VARCHAR)").matches("#{ column.search.value }%") }

While this solution doesn't raise an error, it doesn't seem to work as intended. This is comparing the string value of column.field to the column.search.value. So if the field is "custom_column" this is comparing the string "custom_column" to the search value, not the value contained in the custom_column field of the query.