What is the best way, using ActiveRecord, to execute the following SQL:
SELECT parent.*
FROM sections AS node, sections AS parent
WHERE node.left BETWEEN parent.left AND parent.right
ORDER BY parent.left DESC
LIMIT 1
I know is possible to use .limit(), .where() and .order() but how do you deal with 'from'? Or is it better just to execute the whole lot as a single statement?
Thanks for any help.
There's nothing wrong with using SQL in your application so long as you can verify it's working correctly and isn't exposing you to injection attacks. Since this statement is executed as-is you're okay in that regard.
ActiveRecord::Base.connection provides a method for executing arbitrary queries and retrieving the results in a variety of formats. select_all or select_rows may be what you're looking for.
The AREL query generator is not always as clever as we'd like, so when in doubt go with the simplest form of expression. In your case it seems to be that chunk of SQL.
To re-write it using AREL you'd have to use the join method to link it back on itself.
Parent.from('sections AS node, sections AS parent')
.where('node.left BETWEEN parent.left AND parent.right')
.order('parent.left DESC')
.limit(1)
And you get the benefit of chaining.
