ActiveRecord complex SQL statement

Question

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")

.where('R1.lft BETWEEN R2.lft AND R2.rgt') .group("R1.name") .order("R1.lft").select('COUNT(P2.emp) AS indentation, P1.emp') NOT TESTED. But shall work — Yana Agun Siswanto
– Yana Agun Siswanto, Commented Oct 29, 2014 at 3:04
R1 and R2 are aliases for the Resource table. I cannot understand where in your SQL it has any link to the Resource table? — Dercni
– Dercni, Commented Oct 29, 2014 at 9:15
What is your question again ? Which part you dont understand ? — Yana Agun Siswanto
– Yana Agun Siswanto, Commented Oct 30, 2014 at 6:25
What I don't understand is how the SQL above can be achieved in ActiveRecord. I can identify a few of the components as listed above however cannot determine how it can be strung together for use in Rails. i.e. api.rubyonrails.org/classes/ActiveRecord/… — Dercni
– Dercni, Commented Oct 30, 2014 at 9:09

Jared Beck · Accepted Answer · 2014-10-29 01:51:35Z

1

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.

answered Oct 29, 2014 at 1:51

Jared Beck

17.7k11 gold badges76 silver badges106 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

ActiveRecord complex SQL statement

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related