repository·main·Indexed 23 days ago
https://github.com/georgekaraszi/activerecordextendedA continuation and improvement of the postgres_ext project that extends ActiveRecord with advanced PostgreSQL querying capabilities. It provides support for Common Table Expressions (CTEs) via .with, Window Functions, SQL set operations (UNION, INTERSECT, EXCEPT), and specialized predicate methods for Array, JSONB, HSTORE, and inet types. Additionally, it introduces any_of and none_of for complex OR/NOT logic, and either_join/either_order for conditional associations.
postgres_ext project.The .with/1 method allows you to define complex queries using Postgres WITH statements.
User.with(highly_liked: ProfileL.where("likes > 300"))
.joins("JOIN highly_liked ON highly_liked.user_id = users.id")You can chain multiple .with calls to merge them into a single WITH statement. You can also use these modifiers:
.recursive: Adds the RECURSIVE keyword..materialized: Adds the MATERIALIZED modifier (Postgres 12+)..not_materialized: Adds the NOT MATERIALIZED modifier (Postgres 12+).When using subquery explicit methods (like Unions or JSON methods), CTE clauses are "piped" up to the parent query level. If multiple subqueries use the same CTE name, the implementation favors the parent's CTE or the first one encountered (First come, First served).
User.with(highly_liked: ProfileL.where("likes > 300"), less_liked: ProfileL.where("likes <= 200"))
.joins("JOIN highly_liked ON highly_liked.user_id = users.id")
.joins("JOIN less_liked ON less_liked.user_id = users.id")To use active_record_extended in your Rails application, add the gem to your Gemfile and run bundle install.
gem 'active_record_extended'$ bundleThe foster_select helper automatically converts nested hashes or arrays into dot-notation strings for SQL queries. This is useful for representing table_name.column_name relationships using Ruby structures.
Example Transformation:
hash_to_dot_notation(table_name: :col_name) results in "table_name"."col_name" (or table_name.col_name depending on quoting).. as a separator.The active_record_extended gem extends ActiveRecord::Relation#merge to support merging complex query components. When you merge two relations, the library now automatically handles the combination of:
with clauses, including support for recursive CTEs and materialized/non-materialized keys.This allows you to build complex queries in parts and combine them using standard ActiveRecord merge calls without losing CTE or Window definitions.
To use Postgres Window Functions, you must first define the window and then select using it.
Use .define_window(name) followed by .partition_by(column, order_by: {}).
Use .select_window(function_name, *args, over: window_name, as: alias_name) to apply the function.
Supported Window Functions: Any valid Postgres window function (e.g., row_number, first_value, rank, etc.).
User
.define_window(:number_window).partition_by(:number, order_by: { id: :desc })
.select(:id, :name)
.select_window(:row_number, over: :number_window, as: :row_id)
.select_window(:first_value, :name, over: :number_window, as: :first_value_name)The .select_row_to_json/2 method is designed for sub-queries to transform complex logic into JSON responses. The result must be assigned to an aliased column on the parent level.
Arguments:
from: A subquery (String, Arel, or ActiveRecord::Relation).Options:
as: Alias for the resulting column (default: "results").key: Internal query alias name (useful for mid-level predicate clauses).cast_with: Casting options: :to_jsonb, :array, :array_agg, or :distinct (which applies :array_agg & :to_jsonb).order_by: Ordering operation (ignored if using DISTINCT Aggregated Array).You can pass a block to the method to apply additional scopes to the nested query.
item_query = Variant.select(:name, :id, :category_id, :product_id).where("categories.id = variants.category_id")
product_query = Product.select(:id)
.joins(:items)
.select_row_to_json(item_query, key: :outer_items, as: :items, cast_with: :array) do |item_scope|
item_scope.where("outer_items.product_id = products.id")
end
category_query = Category.select(:name, :id).select_row_to_json(product_query, as: :products, cast_with: :array)These methods facilitate complex matching for Postgres collection types:
contains(attribute: values): Finds records where the column (Array, JSONB, or HSTORE) contains all of the provided values.overlap(attribute: values): Finds records where the Array column contains any of the provided values.Example for Array:
User.where.contains(tags: [1, 4]) # Matches if tags contains both 1 and 4
User.where.overlap(tags: [1, 8]) # Matches if tags contains either 1 or 8alice = User.create!(tags: [1, 4])
bob = User.create!(tags: [3, 1])
randy = User.create!(tags: [4, 1])
User.where.contains(tags: [1, 4]) #=> [alice, randy]
User.where.overlap(tags: [4]) #=> [alice, bob, randy]The any and all methods allow querying Postgres Array columns.
any(attribute: value): Returns records where the array column contains the single provided value.all(attribute: value): Returns records where the array column contains only the single provided value (the array must match the value exactly).Note: Both methods only accept a single value. Passing an array (e.g., [1, 2]) will result in no matches.
alice = User.create!(tags: [1])
bob = User.create!(tags: [1, 2])
randy = User.create!(tags: [3])
User.where.any(tags: 1) #=> [alice, bob]
User.where.all(tags: 1) #=> [alice]Use .json_build_literal/1 or .jsonb_build_literal/1 to create static JSON objects without needing subquery interfacing.
Arguments:
Options:
as: Alias for the resulting column (default: "results").User.json_build_literal(number: 1, last_name: "json", pi: 3.14).take.results
#=> { "number" => 1, "last_name" => "json", "pi" => 3.14 }
User.json_build_literal(:number, 1, :last_name, "json", :pi, 3.14).take.results
#=> { "number" => 1, "last_name" => "json", "pi" => 3.14 }The library provides several ways to combine ActiveRecord relations using SQL set operations:
union(*relations): Combines multiple relations using UNION.union_all(*relations): Combines multiple relations using UNION ALL.union_except(relation1, relation2): Combines relations using EXCEPT.union_intersect(relation1, relation2): Combines relations using INTERSECT.union_as(alias): Changes the default behavior of nesting unions in the FROM clause by providing a specific alias.order_union(*args): Applies an ORDER BY clause to the final combined result set.reorder_union(*args): Clears previous ordering and applies a new ordering scheme to the union.Note on Chaining: There is a known issue when chaining multiple single union clauses. It is recommended to union two relations immediately, then chain subsequent relations.
Person.union(user_1, user_2, users)
Person.union_all(user_1).union_all(user_2)
Person.union_except(users, except_these_users)
Person.union_intersect(likes_100, likes_less_than_150)
Person.select("good_people.id").union(Person.where(id: 1), Person.where(id: 2)).union_as(:good_people)The any_of/1 method simplifies finding records that satisfy multiple OR conditions. none_of/1 is its inverse.
Both methods accept an array of: