Ransack: Searching full_name with ransack (first_name & last_name)

[yigitbacakoglu]

How can i concat first_name and last_name to search with full name using ransack? @radar

Thanks

[jwg2s]

I'm also looking into doing this but have hit a bit of a brick wall. Does ransack support virtual attributes like this? Any help would be appreciated.

[ernie]

It does. See https://github.com/ernie/ransack/blob/master/spec/support/schema.rb#L21-L23

[jwg2s]

That works for ransack purposes, but when the rest of the page loads, I get this error:

Cannot visit Arel::Nodes::InfixOperation

Might be missing something easy here.

[ernie]

Without seeing your code, it's hard to say -- but you shouldn't be getting a cannot visit error for that node type. Unless, maybe, you're using an older version of ARel?

[jwg2s]

In my model (user model):

ransacker :full_name do |parent|
      Arel::Nodes::InfixOperation.new('||',
                                      Arel::Nodes::InfixOperation.new('||', parent.table[:first_name], ' '),
                                      parent.table[:last_name])
    end

In my view:

<div class="control-group">
              <%= f.label :full_name_cont, 'Full Name' %>
              <%= f.text_field :full_name_cont, data: { autocomplete_source: Platform::User.order(:last_name).map(&:full_name)}, class: "span12 search-field search-query", placeholder: 'Full Name' %>
            </div>

I'm not seeing any red flags here but I could be missing something or would more code be helpful?

[ernie]

Can you drop the autocomplete stuff, just to narrow down the test case?

[radar]

Which version of Ransack?

Please provide some steps or an example app to reproduce this issue.

[yigitbacakoglu]

In my model,

ransacker :lines do |parent|
      Arel::Nodes::InfixOperation.new('||', parent.table[:line1], parent.table[:line2])
    end

and,

search = Address.search({"lines_cont" => "foo"})

when i type search.result.first, i receive Cannot visit Arel::Nodes::InfixOperation answer.

ransack version 0.7.0

(mysql database)

[yigitbacakoglu]

After restarting server, I realized that, if search cannot find correct result, it returns some wrong records.
For example, I search for lines_cont => 'foo' and there is no such record, search.result returns me a record which has 'bar' string in it.
Also if I type search.result.count during debugging, it raises Cannot visit Arel::Nodes::InfixOperation and I cannot make that lines search anymore until i restart server.

[gudata]

with mysql it should be

  ransacker :full_name, :formatter => proc {|v| UnicodeUtils.downcase(v) } do |parent|
    Arel::Nodes::NamedFunction.new('LOWER',
      [Arel::Nodes::NamedFunction.new('concat_ws', [' ', parent.table[:first_name], parent.table[:middle_name], parent.table[:last_name]])]
    )
  end

[tiekuhn]

Hi,

I have a similar issue and came up with quite the same solution

ransacker :full_name do |parent|
    Arel::Nodes::NamedFunction.new('concat_ws', [' ', parent.table[:first_name], parent.table[:last_name]])
  end

while this code returns the correct results it has one major flaw --> the sql statement it produces is painfully slow as it is not using the index properly

example

searching for first name and lastname "separately" using AND as combinator --> returns in <1 sec
searching for the same records using the above ransacker code --> returns in ~ 53 sec

I already tried setting a combined index on the two columns --> wont help as we are using string concatenation.

Are you having any Idea how to

• either speed up the query?
• split the incoming "full_name" parameter into first_name and last_name and searching for these individually (combined with AND)?

[gudata]

I guess that MySQL have to execute the concat_ws on every record so it couldn't be faster.

[audionerd]

I was able to use Arel::Nodes::SqlLiteral.new with ransacker to sort on a "virtual attribute" (is that what they’re called in this context?), something like this (pseudo-code):

class Entry < ActiveRecord::Base

  [..]

  ransacker :rank do |parent|
    Arel::Nodes::SqlLiteral.new('rank')
  end

  def self.ranked
    select("DENSE_RANK() OVER (ORDER BY a DESC, b DESC, c DESC) AS rank")
  end

  [..]

end

Which allowed me to sort by the virtual attribute rank, e.g.:

q = { sorts: 'rank desc' }
assert_equal [3, 2, 1], Entry.ranked.search(q).result.map(&:rank)

Not ideal that I'm using .map(&:rank) – I tried .pluck(:rank)but then the SELECT which calculates rank was being overridden.
And I suppose in this particular example, rank could just be cached to the db in a real column, so the virtual attribute would not be required.

So, I'd be happy to know of ways to improve this, but it does at least tie into ransack sort/filter nicely now.

[jonatack]

If you need performance, I'd suggest using a dedicated, indexed search field as I describe in comments here and here toward the end of each conversation. Cheers :)

[codebycliff]

If anyone gets the following error:

Unsupported argument type: String. Construct an Arel node instead.

The solution is to build a quoted node:

  ransacker :full_name do |parent|
    Arel::Nodes::NamedFunction.new('CONCAT_WS', [
      Arel::Nodes.build_quoted(' '), parent.table[:first_name], parent.table[:last_name]
    ])
  end