The Rhom Ruby API documentation has a brief section near the end on "advanced" Queries. This seems applicable really only to Property Bag models, and promises that "rhom can optimize the query for the property bag table" using "Advanced Queries".
It's important to note the qualifier here - "property bag table". IMO, from my exploration of the source code, if you are NOT using Property Bag models, all the "advanced" queries do is obfuscate your code by building-up queries using complex data structures. Under the hood, (when using a Fixed Schema model) Rhom creates a SQL query and then sends it off to SQLite. This adds some overhead (I would imagine minimal) but more importantly, I find the complex data structures used in find to be quite unreadable!
And, so, as I have been using Fixed Schema, I've been moving most of my code toward using either find_by_sql or at least SQL :conditions specifications to find.
One caution - and a Rhom blind spot - is that find_by_sql doesn't offer binding of variables, and so you will need to sanitize variables and bake their values into the SQL query. (:conditions with a SQL fragment does offer binding). So, this limits the usefulness of some of the techniques I'd like to explore (see below).
(Ah, perhaps there is an out.... it looks like Database.executeSql has bound parameters and will return results. The documentation is a bit wonky, describing args as "array of sql expressions", when I'd imagine it is actually "array of values to bind"...)
I'd like to start a discussion here on the advanced ORM techniques that myself and other developers have used or would like to explore. Most of these involve more direct interaction with SQLite. At one time, (in ancient history) Rhodes did not use SQLite on all platforms, (are there any that still don't use it?) and so strict adherence to only using Rhom was more of an imperative if you wanted to write cross-platform code.
A few random thoughts for starter:
- I work mainly with iOS and Android, working on OSX. I've found the combination of SimPholders2 and DB Browser for SQLite quite handy for inspection, debugging, developing SQL queries, etc. SimPholders (while buggy...) easily finds the iOS Simulator's files on your development system, so that you can open the DB with a GUI tools like DB Browser for SQLite. (You may occasionally have to restart Simpholders2 and/or manually kill it, for which System Monitor is handy...)
- You certainly can do joins between tables, subqueries, and the full range of the available SQL using find_by_sql, and I've either worked on code (or written myself) that does quite a bit of this.
- While I haven't tried it, I'd imagine you can use subqueries in a SQL :conditions. (?)
- Database also has executeSQL and executeBatchSQL which allow you to execute some arbitrary SQL. This could be used to define triggers and views (see below).
I'm curious as to the extent others have exploited these capabilities, and what other advanced database techniques you have used in Rhodes?