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? -- Posted via http://www.ruby-forum.com/.
On 11/28/05, List Recv <listrecv-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> 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?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/Associations/ClassMethods.html -- rick http://techno-weenie.net
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...
--
Posted via http://www.ruby-forum.com/.
Maybe use :select ? something like this i think :select => "table.*, table.afield + table.afield2 AS calculated_result" List Recv wrote:>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? > > >
> 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.I''d recommend a pragmatic approach... Do the right OO-thing, that *might* be very slow. If that shows up as a performance problem, and your profiling shows that it indeed is this calculated field that causes the bad performance, replace that specific problem with some finetuned SQL. I don''t think this is an inherent problem with Active Record. If you had perfect technology, how would this problem be solved? Best regards, torben
torben.wolm wrote:> > I don''t think this is an inherent problem with Active Record. If you had > perfect technology, how would this problem be solved? >I am NOT trying to start flames, but Hibernate provides a very natural syntax for this, where you can define how properties should be queried - raw SQL, with a forumla, with a Java method, etc. You can modify this on a per (sub)class basis. I know of no smooth way of doing this in ActiveRecord. Sure, you can override find_by_[name], but what about find_by_[name]_and_[somethingelse]? Or what about :conditions => {...}. Both of those cases will drop to the standard find. So, overriding the find method in AR is asking for trouble, at least if you have more than one developer - someone''s bound to forget that it is overridden, and use one of the standard finds... Really, there are two issues here: 1) Being able to override a find method, so that it will be called when Rails generates a find_by_, or replaces in to :conditions hash 2) Being able to do this on a per subclass basis. 1 is not so difficult a patch, but 2 would involve a huge rewrite of how AR handles subclasses. (Currently, the SQL is totally unaware of subclasses - they only come alive once the row has already been fetched). -- Posted via http://www.ruby-forum.com/.
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 -- Posted via http://www.ruby-forum.com/. --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
> 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.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. --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---