开发者

Do ActiveRecord's statements cover the entire scope of what is possible in raw sql?

开发者 https://www.devze.com 2023-04-12 06:02 出处:网络
In other words, can any complex sql statement (non-db specific SQL code) be broken up into constituent ActiveRecord statements? For the sake of the 开发者_运维技巧argument, I am not considering perfor

In other words, can any complex sql statement (non-db specific SQL code) be broken up into constituent ActiveRecord statements? For the sake of the 开发者_运维技巧argument, I am not considering performance or multiple calls to the database (which could of course be avoided with raw SQL).


No. While Active Record does most abstractions fairly well, some calls are database specific and cannot be abstracted like you mentioned. Others just simply cannot be represented. Something like the SQL CASE call is an example of code I couldn't reconstruct with Active Record. On a reasonably large dataset (~30000), looping was not possible as it took upwards of 20 seconds to run compared to the speed of SQL.

SELECT t.price_range AS price_range, count(*) as total FROM
(
    SELECT
    CASE
    WHEN (price >= '0.00' AND price < '25.00') THEN '0-25'
    WHEN (price >= '25.00' AND price < '50.00') THEN '25-50'
    ELSE '50+'
    END AS price_range
    FROM products p
    RIGHT JOIN
    product_categories pc
    ON
    p.id = pc.id
) t group by t.price_range

I would suggest using the docs and some judgment to make an informed decision about when to use SQL.


Re: Do ActiveRecord's statements cover the entire scope of what is possible in raw sql?

Definitely not. Eg Union statements, etc. But there is a good work-around: create your complicated SQL as a SQL View. Then use ActiveRecord to access the View.

Depending on the dbms and the SQL for the View, the AR Model may or may not need to be marked as ReadOnly.

0

精彩评论

暂无评论...
验证码 换一张
取 消

关注公众号