> I haven't found a good active record example that covers all bases for generating SQL.
You won't either I'm afraid. That is just the thing about SQL as such as it is in regards to the environment you have to work within.
My only suggestion is that you use your implementation of the Active Record to cover common ground such as CRUD operations, and separate out the rest in so far that you follow the Criteria formula?
You construct your SQL elsewhere (which isn't too bad an idea, considering), and once ready, pass that into your Active Record if required, to work with that given row of data in question.
Essentially then, you set up your Active Record with a row of data which does nothing until you've passed in your Criteria object. One thing I don't like about the Active Record is in regards to relationships between data.
The restrictiveness of the Active Record suggests the only option is to go down the RoR route in how it manages the problem but that again doesn't solve all edge cases as you note.
2. Is there any other design pattern I should look into that will speed up my development when querying the database?
in my works and OS project (see signature) i prefer to use my implementation of The Data Mapper Pattern, which includes the best practicies both from phpDoctrine and propel. I think that DM is more flexible and 'cleaner' than AR