Adding support to ActiveRecord / Arel for the ONLY keyword in Postgres

In Postgres, the keyword ONLY can be used after FROM to specify that results should only be taken from the named table, and not any descendant tables that may exist, as in the case of table inheritance. ONLY can also be used in a join statement in the same manner, i.e. “INNER JOIN ONLY posts ON …”.

It seems that the Postgres Arel Visitor class inherits the behavior of constructing the SELECT statement from Arel::Visitors::ToSql, which only prepends FROM to the collector. Arel::Nodes::SelectCore doesn’t seem to have an attribute that seems relevant to prefixing the keyword either.

As an ActiveRecord user, I would like to build an Arel-compatible query where I can opt into prefixing the ONLY keyword when selecting and joining certain tables. I don’t have a strong opinion on the API for this, but a naive example could be like:

Post.table_name # => "posts"

Post.from_only(true).where(status: 'draft').to_sql
# => SELECT posts.* FROM ONLY posts WHERE status = 'draft'

User.joins(:posts).merge(Post.from_only(true)).to_sql
# => SELECT users.* FROM users INNER JOIN ONLY posts ON posts.user_id = users.id

Does this sound feasible? I would be willing to help with creating a PR for the feature, but I would need guidance when it comes to the Arel implementation. Curious to hear if anyone has thoughts about this. Thanks!

3 Likes

As an additional use case: Partitioned tables, at least with native declarative partitioning in PostgreSQL, have a hierarchical relationship as well with parent and child tables. Adding indexes to only the parent can be done using the “ONLY” keyword, which initially adds the index as INVALID.

CREATE INDEX ON ONLY the partitioned table; PostgreSQL: Documentation: 16: 5.11. Table Partitioning

1 Like