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

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.