Rails Virtual Attribute Search or SQL Combined Column Search

Rails virtual attribute search or sql combined column search

You can used a named_scope in your user.rb:

named_scope :find_by_full_name, lambda {|full_name| 
  {:conditions => {:first => full_name.split(' ').first, 
     :last => full_name.split(' ').last}}
}

Then you can do User.find_by_full_name('John Carver')

new stuff in response to changes in requirement

named_scope :find_by_full_name, lambda {|full_name| 
  {:conditions => ["first LIKE '%?%' or last LIKE '%?%'", 
    full_name.split(' ').first, full_name.split(' ').last]}}

How should I access a virtual attribute from an associated model?

pluck grabs column directly from the database. You are getting this error since your full_name method is not a database column.

Change User.pluck(:full_name, :id) to

User.select(:id, :first_name, :last_name).all.map{|u| [u.full_name, u.id] }

create active record query for virtual attribute in Rails 5 or 6

User.select("users.*", "CONCAT(users.first_name, ' ', users.last_name) AS full_name")
    .where(full_name: "John Whoosiwhatsit")

This will work on MySQL, Postgres and SQLite (and probally more). ActiveRecord will map any aliased columns in the resulting rows from the query as attributes in the model.

Some dbs (like Oracle) don't let you use aliases in the WHERE clause so you would have to repeat the concatenation:

# Use an Enterprise DB they said. It will be fun they said.
User.select("users.*", "CONCAT(users.first_name, ' ', users.last_name) AS full_name")
    .where("CONCAT(users.first_name, ' ', users.last_name) = 'John Whoosiwhatsit'")

This has probally worked in almost every version since Rails AFAIK has always allowed you to revert to raw SQL if needed.

ROR Search virtual fields

A virtual field is not on database-level. You can't do database-based search without explaining what that virtual field is to the database.

The definition of your field is essentially a list of columns it consists of. Since you are using PostgreSQL, you could leverage its full-text searching capabilities by using pg_search. It's well able to search by multiple columns at once.

In fact, in the docs there is an example of how to do it that almost exactly matches your case. I literally just copy-pasted it here. Go figure.

# Model
class Person < ActiveRecord::Base
  include PgSearch
  pg_search_scope :search_by_full_name, :against => [:first_name, :last_name]
end

# Example code for the Rails console
person_1 = Person.create!(:first_name => "Grant", :last_name => "Hill")
person_2 = Person.create!(:first_name => "Hugh", :last_name => "Grant")

Person.search_by_full_name("Grant") # => [person_1, person_2]
Person.search_by_full_name("Grant Hill") # => [person_1]

Is that sort of thing worth an extra dependency, is up to you. If you find yourself in situation of constructing many complicated searches, this might help.

To add find_by_ to a model for a virtual attribute

I think you can do that using named_scope

Jim and NAD answers for this similar question Rails virtual attribute search or sql combined column search are probably a good start.

Rails: find_by_sql and virtual column

The count is there, you just can't see it since taskcount is not an attribute Rails creates for that class Task, because it isn't a column that it can see. You have to use the attributes call to find it.
Sample:

class Tag < ActiveRecord::Base
  ...
  def taskcount
    attributes['taskcount']
  end
end

Tag.find_with_count.each do |t|
  puts "#{t.name}: #{t.taskcount}"
end

How can I get a unique :group of a virtual attribute in rails?

Assuming your ContactEmail set is in @contact_emails (untested):

@contact_emails.collect { |contact_email| contact_email.company_name }.uniq

You don't need the virtual attribute for this purpose though. ActiveRecord sets up the relationship automatically based on the foreign key, so you could take the company_name method out of the ContactEmail model and do:

@contact_emails.collect { |contact_email| contact_email.contact.company_name }.uniq

Performance could be a consideration for large sets, so you might need to use a more sophisticated SQL query if that's an issue.

EDIT to answer your 2nd question

If company_name is a column, you can do:

ContactEmail.count(:all, :joins => :contact, :group => 'contact.company_name')

On a virtual attribute I think you'd have to retrieve the whole set and use Ruby (untested):

ContactEmail.find(:all, :joins => :contact, :select => 'contacts.company_name').group_by(&:company_name).inject({}) {|hash,result_set| hash.merge(result_set.first=>result_set.last.count)}

but that's not very kind to the next person assigned to maintain your system -- so you're better off working out the query syntax for the .count version and referring to the column itself.

Single virtual attribute definition for multiple fields

You can always use meta programming to dynamically generate all this repeated code.

module Flexible
  FLEXIBLE_ATTRIBUTES.each do |attr|
    define_method("flexible_#{attr}") do
      self.send(attr)
    end

    define_method("flexible_#{attr}=") do |val|
      self.send("#{attr}=", val.tr('$ ,', '')) unless val.blank?
    end  

  end
end

Now include this module in you class and define the FLEXIBLE_ATTRIBUTES:

class MyModel
  FLEXIBLE_ATTRIBUTES = [:income, :taxes] #......
  include Flexible
end

Now I haven't tested this code but it should do what you're looking for with minimum repetitiveness. It also separates the Flexible method creation logic outside of the model itself. It will also allow you to reuse this module in other classes if needed.

---

Related Topics