Rails - Jul 2009 - Complex Finds -- find_by_sql?

I have been working with an ever scope creeping application which
requires very deeply nested database relations find/select statements.
I''ve taken in more than a few cases (where optimization is needed) to
using find_by_sql("select * ... join, count, where , etc") when
sticking to builtin rails finder options become inefficient or unable
to process the queries. I am aware that sometimes I use find_by_sql
just because I don''t have the time to try to figure out the rails way
and writing queries is just simpler and quicker. Every time I do I am
am impressed at the query optimization, but I always have a feeling of
guilt over it that I just can''t shake!

Ideally I would like to know if these sql queries are going to scale
to other dbs query languages. Do other people find themselves doing
the same thing? I think it would be great if there were a sql tester
that would tell you that syntax or operational order could be
ambiguous depending on what db software is being implemented. Anyone
know of such a thing, or perhaps have a good set of guidelines that
keeps queries compatible?

-Josh

On Wed, Jul 29, 2009 at 4:29 PM,
Josh<joshbwhite-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>
wrote:
>
> I have been working with an ever scope creeping application which
> requires very deeply nested database relations find/select statements.
> I''ve taken in more than a few cases (where optimization is needed)
to
> using find_by_sql("select * ... join, count, where , etc") when
> sticking to builtin rails finder options become inefficient or unable
> to process the queries. I am aware that sometimes I use find_by_sql
> just because I don''t have the time to try to figure out the rails
way
> and writing queries is just simpler and quicker. Every time I do I am
> am impressed at the query optimization, but I always have a feeling of
> guilt over it that I just can''t shake!
>
> Ideally I would like to know if these sql queries are going to scale
> to other dbs query languages. Do other people find themselves doing
> the same thing? I think it would be great if there were a sql tester
> that would tell you that syntax or operational order could be
> ambiguous depending on what db software is being implemented. Anyone
> know of such a thing, or perhaps have a good set of guidelines that
> keeps queries compatible?

If you''re having to write SQL your models are likely not associated
correctly.

Make sure to use the :select option in your find calls, otherwise you
get the default SELECT *.

Your raw SQL may work on another db if it''s simple, but not having any
SQL will of course be the completely portable option.


-- 
Greg Donald
http://destiney.com/