Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Arel merge, rails' hidden gem

benhoskin.gs

Post reply on HN
Print view View original

11–17 of 17 posts

Re: Arel merge, rails' hidden gem

#11

Post by xentronium »

zzzeek wrote: post #3

I've never been a fan of Rails' hieroglyphics. "The query is the one you’d hope for" - why do we need to "hope", when the ORM could just allow you to use relational concepts directly ? Here is SQLAlchemy's much less exciting version of what I see here for "merge", just use a hybrid (sorry, we have more verbose config, due to explicit is better than implicit): from sqlalchemy import Column, Integer, String, ForeignKey…

> when the ORM could just allow you to use relational concepts directly

There is a database toolkit in ruby-land that allows you do just that! It's called Sequel.

I've built an example, which I deliberately kept as bare-bones and explicit as possible: https://gist.github.com/1aa977a63b7d6a727497

You can also build something similar on top of ARel which backs ActiveRecord.

Re: Arel merge, rails' hidden gem

#13

Post by yxhuvud »

I know of merge, but usually I just don't need it. article.users.editorial would usually be enough for my needs.

I'd have great usage of union (which doesn't exist, or doesn't exist in the version of rails I'm stuck in) though.

Re: Arel merge, rails' hidden gem

#14

Post by omarqureshi »

davidw wrote: post #5

Here's one that's been bugging me - anyone want to take a crack at it? @dev_configs = DeviceConfig. joins("join (select device_id, max(updated_at) as max_updated_at from device_configs group by device_id) dc2 on dc2.device_id = device_configs.device_id and dc2.max_updated_at = device_configs.updated_at"). includes("device").order("devices.updated_at desc") Each device has many device configurations, and we want to di…

This is something you should be able to do with Arel (with join_sources) or through the use of to_sql

as a note - you shouldn't use includes and joins, includes hammers joins.

Simplest tidy up though is a scope for the join, something like

    DeviceConfig.joins("#{DeviceConfig.last_updated.to_sql} dc2 
                          on dc2.device_id = device_configs.device_id 
                          and dc2.max_updated_at = device_configs.updated_at).
                 order(...)
Alternatively maybe something like

    dc_t = Arel::Table::new :device_configs
    dc2_t = Arel::Table::new :device_configs

    inner = dc_t.group.(dc_t[:device_id]).
                 project(dc_t[:device_id].as("device_id"),
    dc_t[:updated_at].maximum.as("maximum_updated_at"))

    dc2_t.join(inner.join_sources).
          on(dc2_t[:device_id].eq(inner[:device_id])).
          on(dc2_t[:updated_at].eq(inner[:max_updated_at])).
          order(...)
im not entirely sure of the on syntax there, the API doesnt look clear.

Atleast, those are the two solutions I have though personally - i think it reads better as straight SQL with select_values. That way, you know what is happening.

Re: Arel merge, rails' hidden gem

#15

Post by ef4 »

I've been using Arel directly (with ActiveRecord) for a project. It's 80% of what I want, but then it inexplicably sucks at the last 20%.

The whole point of a relational algebra is that it's closed under all the relevant operations. But Arel's implementation mostly ignores this fact, and you get back different types of objects with incompatible APIs depending on what operations you use and even what order you apply them in.

Concrete example one: "foo.union(bar).union(baz)" explodes because the union operation is not composable.

Concrete example two: you can compose joins from the right but not the left. So "(foo.join(bar)).join(baz)" works but baz.join(foo.join(bar))" explodes -- but not until later when you try to dump it to sql, at which point you get an obscure exception.

When you look under the hood, you see that it's having a hard time with this stuff because it doesn't really implement relational algebra. It's mostly just an abstract syntax tree for SQL.

Re: Arel merge, rails' hidden gem

#16

Post by sirn »

xentronium wrote: post #11
zzzeek wrote: post #3

I've never been a fan of Rails' hieroglyphics. "The query is the one you’d hope for" - why do we need to "hope", when the ORM could just allow you to use relational concepts directly ? Here is SQLAlchemy's much less exciting version of what I see here for "merge", just use a hybrid (sorry, we have more verbose config, due to explicit is better than implicit): from sqlalchemy import Column, Integer, String, ForeignKey…

> when the ORM could just allow you to use relational concepts directly There is a database toolkit in ruby-land that allows you do just that! It's called Sequel. I've built an example, which I deliberately kept as bare-bones and explicit as possible: https://gist.github.com/1aa977a63b7d6a727497 You can also build something similar on top of ARel which backs ActiveRecord.

Actually Sequel (and ARel) is more like SQLAlchemy's SQL Expression Language[1] than the SQLAlchemy ORM itself.

[1]: http://docs.sqlalchemy.org/en/latest/core/tutorial.html

Re: Arel merge, rails' hidden gem

#17

Post by dkubb »

ef4 wrote: post #15

I've been using Arel directly (with ActiveRecord) for a project. It's 80% of what I want, but then it inexplicably sucks at the last 20%. The whole point of a relational algebra is that it's closed under all the relevant operations. But Arel's implementation mostly ignores this fact, and you get back different types of objects with incompatible APIs depending on what operations you use and even what order you apply t…

The current version of ARel is not a relational algebra library, it's an SQL compiler according to Aaron Patterson [1]. Many of the concepts and names for internals are based on SQL not RA. There's nothing wrong with that, it's a pragmatic choice for Rails given that ActiveRecord is an RDBMS only abstraction.

I've been working on a relational algebra library for ruby (tentatively) called veritas [2] where the sets are closed under all operations.

Given that it's a higher level abstraction than ARel the trade off is that there's not a 1:1 mapping between it's pure RA ops and SQL operations. I err on the side of producing SQL that will returns the correct results, which means some of the queries are a bit verbose. I consider that only a temporary problem though; I believe I can get to the point where most common queries are identical to what you'd write by hand. I'm focused on correctness before performance.

I've also written an optimizer [3] that takes the RA AST and rewrites it to be smaller and more efficient. It handles lots of the low hanging fruit, but there's still room for improvement. The advantage to this approach is that it simplifies the AST for all targets, not just SQL. There's even room to do per-target optimizations, which is nice because there is often multiple ways to form a query, and some approaches may be more efficient than others on different backends.

[1] https://github.com/sconover/knit-js#footnotes [2] https://github.com/dkubb/veritas [3] https://github.com/dkubb/veritas-optimizer

Post reply on HN