CODEWITHSUNDEEP
sqlruby-on-rails
Dercni
Dercni

Reputation: 1224

ActiveRecord complex SQL statement

Can AR manage complex statements such as the following?

SELECT COUNT(P2.emp) AS indentation, P1.emp
FROM Personnel AS P1, Personnel AS P2
WHERE P1.lft BETWEEN P2.lft AND P2.rgt
GROUP BY P1.emp
ORDER BY P1.lft;

From: http://www.ibase.ru/devinfo/DBMSTrees/sqltrees.html

My variation of the SQL is:

SELECT COUNT(R2.name) AS indentation, R1.name FROM Regions AS R1, Regions AS R2 WHERE R1.lft BETWEEN R2.lft AND R2.rgt GROUP BY R1.name ORDER BY R1.lft

I have located the following but have no idea how to link it all together.

.count(R2.name)
.where(R1.lft: R2.lft..R2.rgt)
.group("R1.name")
.order("R1.lft")

Upvotes: 1

Views: 174

Answers (1)

Jared Beck
Jared Beck

Reputation: 17538

Yes. All of the SQL keywords you use, including the implied join should be documented in the Active Record Query Interface Guide. Let me know if you find any that aren't.

For the BETWEEN you can use ruby's Range object, but you may want to use explicit comparison operators. It's a matter of personal style, I think.

Once you've learned how to construct these queries by hand, check out gems like awesome_nested_set which can construct them for you.

Upvotes: 1

Related Questions