Forum: Ruby on Rails Querying calculated fields of ActiveRecord

Announcement (2017-05-07): www.ruby-forum.com is now read-only since I unfortunately do not have the time to support and maintain the forum any more. Please see rubyonrails.org/community and ruby-lang.org/en/community for other Rails- und Ruby-related community platforms.
Ccfe7d097475a32dc3ff78d6fc42c852?d=identicon&s=25 List Recv (lstrecv)
on 2005-11-28 17:06
Querying in O/R Mappers

I'm confused about performing queries with O/R Mappers, when properties
don't map directly to database fields.

Cases like:
	* calculated fields ( eg def get_age(as_of = now) ; as_of - birthday ;
end  -> method returns result of calculation, not simple accessor)
	* different subclasses implement field differently (some may store it
directly, some may calculate it)
	* Methods like def zip_code ; address.zip_code; end - which access the
fields of a member object (demeter friendly)

Let's say I need to get all people whose age as of today is >18, or all
people whose zipcode is 01234.

I can think of two ways:
	A) Retrieve all the objects, and run the method on each one.
	B) Write special SQL to do the query.

The obvious downside to A is performance.

The obvious downside to B is that it ruins all the benefits of OO
encapsulation, demeter, inheritance, etc.  I need to incorporate all of
that knoweledge into the finder methods.  When different subclasses
implement the field differently, this gets into some pretty messy SQL as
well.

How do the ActiveRecord pro's do it?
821395fe70906c8290df7f18ac4ac6cf?d=identicon&s=25 technoweenie (Guest)
on 2005-11-28 17:26
(Received via mailing list)
On 11/28/05, List Recv <listrecv@gmail.com> wrote:
>         * Methods like def zip_code ; address.zip_code; end - which access the
>
> The obvious downside to B is that it ruins all the benefits of OO
> encapsulation, demeter, inheritance, etc.  I need to incorporate all of
> that knoweledge into the finder methods.  When different subclasses
> implement the field differently, this gets into some pretty messy SQL as
> well.
>
> How do the ActiveRecord pro's do it?

ActiveRecord lets you very easily drop down to SQL to fine tune your
queries:

# dynamic finders for every table attribute
Developer.find_by_zip_code('01234')
Developer.find_all_by_zip_code('01234')

Developer.find(:all, :conditions => ['lines_of_code > ? and zip_code =
?, 50, '01234'])

# automatically filters to current project
@project.developers.find(:all, :conditions => ['lines_of_code > ? and
zip_code = ?, 50, '01234'])

class Project < AR::Base
  has_many :developers
  has_many :active_developers,
    :class_name => 'Developer',
    :conditions => 'lines_of_code > 50'
...

@project.active_developers

As always, consult the documentation:

http://rails.rubyonrails.com/classes/ActiveRecord/Base.html
http://rails.rubyonrails.com/classes/ActiveRecord/...

--
rick
http://techno-weenie.net
Ccfe7d097475a32dc3ff78d6fc42c852?d=identicon&s=25 List Recv (lstrecv)
on 2005-11-28 17:33
Right, but, if you read the original post, the question is in cases when
the fields don't map directly to SQL fields, but are instead calculated.
This occurs either do to encapsulation, polymorphism, or
inheritance/subclassing.

An example:
* A price method, which might be calculated (not stored!), and be
calculated differently for various subclasses

Again, the two methods I suggested each have drawbacks.  Loading each
object and checking the method (A):

  Products.find_all.select { |p| p.price < 30 }

works great in terms of OO, but is very slow.

Writing custom SQL (B) improves performance, but convolutes the whole OO
thing.  You can no longer encapsulate price() in to each subclass, but
need to define it all in some whopping SQL statement, which would
incorporate each subclasses' price() method in SQL, and invoke the
correct one based on the type field...
Ac2be938319175ab6e69e24b2b4b5a95?d=identicon&s=25 morg (Guest)
on 2005-11-28 17:43
(Received via mailing list)
Maybe use :select ?

something like this i think
:select => "table.*, table.afield + table.afield2 AS calculated_result"
0e71feafb02b7edd6c8999e9d4628724?d=identicon&s=25 Chris Smouse (jcsmouse)
on 2007-02-09 23:54
I realize this thread is quite old, but I recently solved a similar
problem, and ran into similar difficulties.

In my situation, I wanted to return, along with other attributes, the
distance between an address and another fixed coordinate on Earth.
Performance is high on my list, so I wrote a pl/pgsql function to avoid
excessive marshalling (and for convenience), but when I started to port
my PHP application to RoR, I ran into the problem you describe.

The best solution I could come up with that balances performance, ease
of use and the OO features/limitations of AR was to, in summary:

1.  Create a database view which selects all of the columns from the
table in which you're interested, including your calculated column.  Be
sure to alias your calculated column (geo_distance in my case).
2.  Generate an AR class which binds to the view (either naturally by
naming it appropriately, or by calling 'set_table_name "my_view"' in
code.
3.  Code custom find_* methods as needed.  This will give you free
access to your calculated columns.

Obviously, you can't update a database view, so don't even try calling
.save(!) on any MyView objects.  I solved my need to update the table
involved in the DB view by generating a second AR class bound to the
table itself.

Happy tRails!

Chris
2f9a03aa0fcfe945229cb6126eda2cb2?d=identicon&s=25 Philip Hallstrom (Guest)
on 2007-02-10 00:05
(Received via mailing list)
> of use and the OO features/limitations of AR was to, in summary:
> Obviously, you can't update a database view, so don't even try calling
> .save(!) on any MyView objects.  I solved my need to update the table
> involved in the DB view by generating a second AR class bound to the
> table itself.

Actually you can if my memory serves me right... you just need to write
triggers that fire for insert/update/delete that modify the underlying
tables instead of the view... now how well that works with Rails I don't
know, but my memory (years ago) is that once done it's done...

Might be an option for you... might not.
This topic is locked and can not be replied to.