Can I count based on virtual attribute?

Question

I have the following error:

no such column: company_name: SELECT count("contact_emails".id) AS count_id FROM "contact_emails" 

The model ContactEmail does NOT have a column company_name. I created it as a virtual attribute.

It's not possible to do a select based on that information? How would I do it, then?

ContactEmail belongs_to Contact which belongs_to Company.

Answer 1

Virtual attributes added to the class definition but not present in the database can't be used for a database select since the virtual attribute is not in the database.

You can use the database to select a superset of the desired rows, then do a second select at the Rails level which will used the virtual attribute.

Eg

# model Citizen class file
# model fields:
#   id
#   name
#   age
#   city

def can_vote?  # a virtual attribute
   age >= 18 
end

def self.find_voters_by_city(city)  # class level finder
   # returns array of voters in a city
   citizens = Citizen.find_by_city(city) # First select done in database
   citizens.select{|citizen| citizen.can_vote?} # Second select is done at Rails
                                                # level using the Array#select
                                                # method
end

Note that while the above works fine, you should be very careful with regard to performance issues. Selecting at the Rails level is much slower than selecting in the dbms. Also, you're transferring much more data over the Rails/DBMS connection than would otherwise be needed.

If you're going to be selecting something on a regular basis, then it is usually better to push the virtual attribute into the database--make it a real attribute in the database.

If your table can't be changed, you can also create a second table, with a has_one relationship. The second table would hold the additional attributes.

Added: Selecting in the database using two tables

### model Contact
#   id
#   name
#   city
has_one :company
def company_name; company.name; end # a virtual attribute

### model Company
#   id
#   contact_id
#   name
#   employee_count
belongs_to :contact

### Selecting on city (from Contact) and employee_count (from Company)
city = xyz # the city we are searching for
employee_count = 123 # minimum company size we want
contacts = Contact.find(:all, 
      :conditions => ["contacts.city = ? and companies.employee_count >= ?",
         city, employee_count],
      :include => :company)

# above statement does the join and select in the database
# There are also techniques involving named scopes for the above
# And in Rails 3, it will be different too.