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?


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.