### Simple Union Example

Source: https://github.com/georgekaraszi/activerecordextended/blob/main/_autodocs/api-reference-union.md

Demonstrates a simple union of three queries. The SQL generated combines the results, removing duplicates.

```ruby
user_1 = Person.where(id: 1)
user_2 = Person.where(id: 2)
users  = Person.where(id: 1..3)

Person.union(user_1, user_2, users)
# => [Person(id: 1), Person(id: 2), Person(id: 3)]

# SQL:
# SELECT "people".* FROM (
#   (SELECT "people".* FROM "people" WHERE "people"."id" = 1)
#   UNION
#   (SELECT "people".* FROM "people" WHERE "people"."id" = 2)
#   UNION
#   (SELECT "people".* FROM "people" WHERE "people"."id" BETWEEN 1 AND 3)
# ) people
```

--------------------------------

### Chained Unions Example

Source: https://github.com/georgekaraszi/activerecordextended/blob/main/_autodocs/api-reference-union.md

Demonstrates chaining multiple `union` calls to achieve the same result as a direct multiple union.

```ruby
user_1 = Person.where(id: 1)
user_2 = Person.where(id: 2)
users  = Person.where(id: 1..3)

Person.union(user_1).union(user_2).union(users)
# => Same result as above
```

--------------------------------

### Window Definition Hash Example

Source: https://github.com/georgekaraszi/activerecordextended/blob/main/_autodocs/types.md

Defines a single window specification for use in SQL `WINDOW` clauses. Includes the window name, partitioning columns, and ordering criteria.

```ruby
{
  window_name: Arel.sql("revenue_window"),
  partition_by: [Arel.sql("department")],
  order_by: Arel.sql("salary DESC")
}
```

--------------------------------

### Install Active Record Extended Gem

Source: https://github.com/georgekaraszi/activerecordextended/blob/main/README.md

Add the gem to your application's Gemfile to begin using its features.

```ruby
gem 'active_record_extended'
```

--------------------------------

### Union Chain Example

Source: https://github.com/georgekaraszi/activerecordextended/blob/main/_autodocs/api-reference-union.md

Demonstrates chaining various union operations including union, except, and intersect, along with ordering. This shows how to build complex queries step-by-step.

```ruby
Person.union.all(q1, q2).except(q3).intersect(q4)
```

--------------------------------

### Get Full Query Plan with Explain

Source: https://github.com/georgekaraszi/activerecordextended/blob/main/_autodocs/integration-patterns.md

Inspect the execution plan of a generated SQL query using the database connection's `explain` method. This is crucial for performance tuning.

```ruby
# Full query plan
puts ActiveRecord::Base.connection.explain(
  User.with(data: Order.select(:id)).to_sql
)
```

--------------------------------

### SelectHelper Hash Syntax Example

Source: https://github.com/georgekaraszi/activerecordextended/blob/main/_autodocs/types.md

Provides a hash format for specifying column selections in `foster_select`. Supports direct columns, nested table.column references, and options for casting, aggregation, and distinctness.

```ruby
{
  alias_name: column_reference,
  alias_name: [column_reference, options_hash],
  alias_name: {
    table: :column,
    cast_with: :aggregate_function,
    order_by: ordering_spec,
    distinct: boolean
  }
}
```

--------------------------------

### Order Union Results

Source: https://github.com/georgekaraszi/activerecordextended/blob/main/_autodocs/api-reference-union.md

Ensure final union results are consistently ordered using `order_union`. This example prioritizes VIP users, then active users, sorting both by name.

```ruby
Person.union(
  Person.where(vip: true).select(:id, :name),
  Person.where(active: true).select(:id, :name)
).order_union(vip: :desc, name: :asc)
# VIPs first, then active users, both sorted by name
```

--------------------------------

### CTE Piping Shadowing Example

Source: https://github.com/georgekaraszi/activerecordextended/blob/main/_autodocs/errors.md

Demonstrates how child CTEs with the same name as parent CTEs are silently ignored, resulting in the parent's CTE being used in the generated SQL.

```ruby
child = Order.with(data: Order.where(id: 1))
parent = User.with(data: User.where(id: 2))

result = parent.union(child)
# Generates SQL with parent's 'data' CTE, not child's
```

--------------------------------

### Optimize Aggregation with Grouping and FosterSelect

Source: https://github.com/georgekaraszi/activerecordextended/blob/main/_autodocs/api-reference-foster-select.md

Use foster_select with grouping for efficient aggregation in a single query, avoiding N+1 problems. This example calculates total spent and order count per user.

```ruby
User.joins(:orders)
    .foster_select(
      user_id: :id,
      total_spent: { orders: :amount, cast_with: :sum },
      order_count: { orders: :id, cast_with: :count }
    )
    .group(:id)
    .having("COUNT(orders.id) > 3")
```

--------------------------------

### Aggregation with Foster Select

Source: https://github.com/georgekaraszi/activerecordextended/blob/main/_autodocs/README.md

Example of using `foster_select` for efficient aggregation without N+1 queries. It allows specifying nested associations and custom casting for aggregated values.

```ruby
User.foster_select(
  id: :id,
  name: :name,
  post_count: { posts: :id, cast_with: :count },
  recent_posts: { posts: :title, cast_with: :array }
)
```

--------------------------------

### Handle Nested Relationships with FosterSelect

Source: https://github.com/georgekaraszi/activerecordextended/blob/main/_autodocs/api-reference-foster-select.md

Perform aggregations on deeply nested relationships using foster_select. This example calculates the average product price within categories.

```ruby
# Category -> Product -> Price
Category.joins(products: :prices)
        .foster_select(
          category_id: :id,
          avg_product_price: { products: { prices: :value }, cast_with: :avg }
        )
```

--------------------------------

### Top N Earners Per Department (Combined Query)

Source: https://github.com/georgekaraszi/activerecordextended/blob/main/_autodocs/api-reference-window.md

Combines window functions with other query methods like `where` to filter results. This example finds the top 3 earners per department among those with a salary over 100000.

```ruby
high_performers = User.where("salary > 100000")

high_performers.define_window(:dept_window)
               .partition_by(:department, order_by: { salary: :desc })
               .select(:id, :name, :salary, :department)
               .select_window(:row_number, over: :dept_window, as: :dept_rank)
               .where("dept_rank <= 3")

# Top 3 earners per department among high performers
```

--------------------------------

### Combine Multiple Data Sources with UNION

Source: https://github.com/georgekaraszi/activerecordextended/blob/main/_autodocs/api-reference-union.md

Combine disparate queries into a single result set using `union`. This example retrieves active users and recent inactive users.

```ruby
# Get active users and also recent inactive users
active = User.where(status: 'active')
recent_inactive = User.where(status: 'inactive')
                      .where("last_seen > ?", 1.week.ago)

User.union(active, recent_inactive)
    .order_union(last_seen: :desc)
```

--------------------------------

### to_nice_union_sql

Source: https://github.com/georgekaraszi/activerecordextended/blob/main/_autodocs/api-reference-union.md

Pretty-prints the union SQL statement, optionally including ANSI color codes. This method requires the 'niceql' gem to be installed.

```APIDOC
## to_nice_union_sql

### Description
Pretty-prints union SQL (requires `niceql` gem).

### Signature
```ruby
to_nice_union_sql(color = true) -> String | nil
```

### Parameters
#### Query Parameters
- **color** (Boolean) - Optional - Default: `true` - Include ANSI color codes

### Return
Returns formatted SQL string, or falls back to `to_union_sql` if niceql not available.

### Example
```ruby
query = Person.union(Person.where(id: 1), Person.where(id: 2))

puts query.to_nice_union_sql
# Output with formatting and colors
```
```

--------------------------------

### CTE Union Operator Grouping Issue

Source: https://github.com/georgekaraszi/activerecordextended/blob/main/_autodocs/errors.md

Shows an example where chaining a different operator after a single union might lead to incorrect SQL grouping.

```ruby
Person.union(query1).union_except(query2)
# May produce incorrect SQL grouping
```

--------------------------------

### Require and Use Extended Query Method

Source: https://github.com/georgekaraszi/activerecordextended/blob/main/_autodocs/overview.md

This snippet shows how to require the gem and immediately use one of its extended query methods on an ActiveRecord model. Ensure the gem is installed and loaded.

```ruby
require 'active_record_extended'  # Auto-loads via Gemfile
User.where.any(tags: 1)  # Now available on all AR models
```

--------------------------------

### Joined Relationships `contains` Example (JSONB)

Source: https://github.com/georgekaraszi/activerecordextended/blob/main/_autodocs/api-reference-predicates.md

Shows how to apply the `contains` predicate on a JSONB column within a joined relationship. This requires joining the relevant tables before applying the condition.

```ruby
Tag.joins(:user).where.contains(user: { data: { nickname: 'chainer' } })
# SELECT "tags".* FROM "tags" 
# INNER JOIN "users" ON "users"."id" = "tags"."user_id" 
# WHERE "users"."data" @> '{"nickname": "chainer"}'
```

--------------------------------

### Use either_joins for Conditional Joins

Source: https://github.com/georgekaraszi/activerecordextended/blob/main/_autodocs/api-reference-conditionals.md

Demonstrates the usage of `either_joins` to retrieve users who have either a `profile_l` or a `profile_r`. Records without either profile are excluded. The example also shows the generated SQL and the `CASE` expression used for conditional joining.

```ruby
class User < ActiveRecord::Base
  has_one :profile_l, class_name: "ProfileL"
  has_one :profile_r, class_name: "ProfileR"
  
  scope :completed_profile, -> { either_joins(:profile_l, :profile_r) }
end

alice = User.create!
bob   = User.create!
randy = User.create!
ProfileL.create!(user_id: alice.id)
ProfileR.create!(user_id: bob.id)

User.either_joins(:profile_l, :profile_r)
# => [alice, bob]  (not randy, who has no profile)

User.completed_profile
# => [alice, bob]

# SQL: SELECT "users".* FROM "users"
#      LEFT OUTER JOIN "profile_ls" ON "profile_ls"."user_id" = "users"."id"
#      LEFT OUTER JOIN "profile_rs" ON "profile_rs"."user_id" = "users"."id"
#      WHERE (CASE WHEN profile_ls.user_id IS NULL THEN profile_rs.user_id 
#                  ELSE profile_ls.user_id END) = users.id
```

--------------------------------

### Window Function with CTE

Source: https://github.com/georgekaraszi/activerecordextended/blob/main/_autodocs/api-reference-cte.md

Combine CTEs with window functions for advanced analytics. This example defines a CTE `high_value` for orders exceeding a certain amount and then uses it in a join with window functions to rank users.

```ruby
User.with(high_value: Order.where("amount > 1000"))
    .joins("JOIN high_value ON high_value.user_id = users.id")
    .select("users.*")
    .define_window(:user_window).partition_by(:id)
    .select_window(:row_number, over: :user_window, as: :rank)
```

--------------------------------

### Aggregate Multiple Functions with FosterSelect

Source: https://github.com/georgekaraszi/activerecordextended/blob/main/_autodocs/api-reference-foster-select.md

Use foster_select to aggregate multiple functions (sum, average, count) on joined tables. This example aggregates sales data per product.

```ruby
Product.joins(:sales)
       .foster_select(
         product_id: :id,
         total_sales: { sales: :amount, cast_with: :sum },
         avg_sale: { sales: :amount, cast_with: :avg },
         sale_count: { sales: :id, cast_with: :count }
       )
       .group(:id)
```

--------------------------------

### Build API Response Shapes with FosterSelect

Source: https://github.com/georgekaraszi/activerecordextended/blob/main/_autodocs/api-reference-foster-select.md

Shape API responses by embedding arrays and counts from joined tables using foster_select. This example shapes author data with their books.

```ruby
Author.joins(:books)
      .foster_select(
        author_id: :id,
        name: :name,
        book_ids: { books: :id, cast_with: :array },
        book_count: { books: :id, cast_with: :count }
      )
      .group(:id)
```

--------------------------------

### Arel Node Construction for PostgreSQL Operators

Source: https://github.com/georgekaraszi/activerecordextended/blob/main/_autodocs/overview.md

Shows examples of custom Arel nodes used to represent PostgreSQL-specific operators. These nodes are built by query methods and later visited by `PostgresqlDecorator` to generate SQL.

```ruby
# Arel::Nodes::Overlaps, Arel::Nodes::Contains, Arel::Nodes::Inet::*
# Built by query methods, then visited by PostgresqlDecorator for SQL
```

--------------------------------

### Recursive CTE for Hierarchical Data Traversal

Source: https://github.com/georgekaraszi/activerecordextended/blob/main/_autodocs/api-reference-cte.md

Use the `recursive` method to add the `RECURSIVE` modifier for self-referential queries, enabling traversal of hierarchical data structures. This example demonstrates fetching all categories starting from those with no parent.

```ruby
initial = Category.where(parent_id: nil)
recursive_query = Category.where("parent_categories.id = categories.parent_id")

Category.with.recursive(
  categories: initial.union(recursive_query)
).from(:categories)
```

```sql
WITH RECURSIVE "categories" AS (
  (SELECT "categories".* FROM "categories" WHERE "categories"."parent_id" IS NULL)
  UNION
  (SELECT "categories".* FROM "categories" 
   WHERE "parent_categories"."id" = "categories"."parent_id")
)
SELECT * FROM categories
```

--------------------------------

### Define Windows with Partitioning

Source: https://github.com/georgekaraszi/activerecordextended/blob/main/README.md

Use `.define_window` to name a window and `.partition_by` to specify the columns for partitioning and ordering. The `order_by` clause is optional.

```ruby
User
.define_window(:number_window).partition_by(:number, order_by: { id: :desc })
.define_window(:name_window).partition_by(:name, order_by: :id)
.define_window(:no_order_name).partition_by(:name)
```

--------------------------------

### SQL Output for Defined Windows

Source: https://github.com/georgekaraszi/activerecordextended/blob/main/README.md

This SQL demonstrates how the defined windows are translated into the `WINDOW` clause in PostgreSQL.

```sql
SELECT *
FROM users
WINDOW number_window AS (PARTITION BY number ORDER BY id DESC),
       name_window   AS (PARTITION BY name ORDER BY id),
       no_order_name AS (PARTITION BY name)
```

--------------------------------

### Conditional Aggregation with FosterSelect and Where

Source: https://github.com/georgekaraszi/activerecordextended/blob/main/_autodocs/api-reference-foster-select.md

Combine foster_select with the `where` clause for filtered aggregation. This example counts comments only for published posts.

```ruby
Post.joins(:comments)
    .where(published: true)
    .foster_select(
      post_id: :id,
      comment_count: { comments: :id, cast_with: :count }
    )
```

--------------------------------

### Conditional Ranking with Window Functions

Source: https://github.com/georgekaraszi/activerecordextended/blob/main/_autodocs/README.md

Shows how to use window functions with `.define_window`, `.partition_by`, and `.select_window` to perform conditional ranking, such as selecting the top N items within categories.

```ruby
Product.define_window(:rank).partition_by(:category, order_by: { price: :desc })
       .select_window(:row_number, over: :rank, as: :rank)
       .where("rank <= 10")  # Top 10 per category
```

--------------------------------

### partition_by

Source: https://github.com/georgekaraszi/activerecordextended/blob/main/_autodocs/api-reference-window.md

Establishes the partitioning and optional ordering for a window definition. It takes one or more partition columns and an optional order_by clause to define the window's scope.

```APIDOC
## `partition_by`

### Description
Establishes the partitioning and optional ordering for a window definition. It takes one or more partition columns and an optional order_by clause to define the window's scope.

### Method Signature
```ruby
partition_by(*partitions, order_by: nil) -> ActiveRecord::Relation
```

### Parameters
#### `*partitions`
- **Type**: Symbol, Column, Array
- **Required**: yes
- **Description**: Columns to partition by.

#### `order_by`
- **Type**: Symbol, Hash
- **Required**: no
- **Default**: nil
- **Description**: Ordering within the partition.

### Return
Returns a new `ActiveRecord::Relation` with the window defined.

### Examples

Simple partition:

```ruby
User.define_window(:user_window)
    .partition_by(:user_id)

# SQL:
# WINDOW user_window AS (PARTITION BY user_id)
```

Partition with ordering:

```ruby
User.define_window(:number_window)
    .partition_by(:number, order_by: { id: :desc })

# SQL:
# WINDOW number_window AS (PARTITION BY number ORDER BY id DESC)
```

Multiple partition columns:

```ruby
User.define_window(:complex_window)
    .partition_by(:category, :region, order_by: { created_at: :asc })

# SQL:
# WINDOW complex_window AS (PARTITION BY category, region ORDER BY created_at ASC)
```

Hash-style ordering:

```ruby
User.define_window(:ordered_window)
    .partition_by(:department, order_by: { salary: :desc, name: :asc })

# SQL:
# WINDOW ordered_window AS (PARTITION BY department ORDER BY salary DESC, name ASC)
```
```

--------------------------------

### Simple Partition by Column

Source: https://github.com/georgekaraszi/activerecordextended/blob/main/_autodocs/api-reference-window.md

Use `partition_by` with a single symbol to partition the window by that column. This is useful for basic window functions where no specific ordering within partitions is required.

```ruby
User.define_window(:user_window)
    .partition_by(:user_id)
```

```sql
WINDOW user_window AS (PARTITION BY user_id)
```

--------------------------------

### Generate Union SQL Statement

Source: https://github.com/georgekaraszi/activerecordextended/blob/main/_autodocs/api-reference-union.md

Use `to_union_sql` to get the raw UNION SQL string without a FROM clause. Returns nil if no unions are defined.

```ruby
query = Person.union(Person.where(id: 1), Person.where(id: 2))

puts query.to_union_sql
# (SELECT "people".* FROM "people" WHERE "people"."id" = 1)
# UNION
# (SELECT "people".* FROM "people" WHERE "people"."id" = 2)
```

--------------------------------

### Perform Set Difference with UNION EXCEPT

Source: https://github.com/georgekaraszi/activerecordextended/blob/main/_autodocs/api-reference-union.md

Find records present in one query but not another using `union_except`. This example selects all users excluding those who are banned.

```ruby
all_users = User.all
banned_users = User.where(status: 'banned')

User.union_except(all_users, banned_users)
# => All users except banned ones
```

--------------------------------

### Define a Basic Window

Source: https://github.com/georgekaraszi/activerecordextended/blob/main/_autodocs/api-reference-window.md

Establishes a named window frame with partitioning and ordering. Use this to create a reusable window definition for subsequent window function calls.

```ruby
User.define_window(:number_window)
    .partition_by(:number, order_by: { id: :desc })
```

--------------------------------

### Array Type `contains` Example

Source: https://github.com/georgekaraszi/activerecordextended/blob/main/_autodocs/api-reference-predicates.md

Demonstrates how to use `contains` to find records where an array column includes all specified elements. Ensure the column is of an array type.

```ruby
alice = 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]  (both contain 1 AND 4)

# SQL: SELECT "users".* FROM "users" WHERE "users"."tags" @> '{1,4}'
```

--------------------------------

### Multiple Window Functions with `select_window`

Source: https://github.com/georgekaraszi/activerecordextended/blob/main/_autodocs/api-reference-window.md

Demonstrates applying multiple window functions, `row_number` for ranking and `avg` for calculating the average salary, within the same query. This allows for complex analytical queries on grouped and ordered data.

```ruby
User.define_window(:window1).partition_by(:department, order_by: { salary: :desc })
    .select(:id, :name, :salary)
    .select_window(:row_number, over: :window1, as: :salary_rank)
    .select_window(:avg, :salary, over: :window1, as: :dept_avg_salary)
```

```sql
SELECT "users"."id", "users"."name", "users"."salary",
       (ROW_NUMBER() OVER window1) AS "salary_rank",
       (AVG("salary") OVER window1) AS "dept_avg_salary"
FROM "users"
WINDOW window1 AS (PARTITION BY department ORDER BY salary DESC)
```

--------------------------------

### Partition by Multiple Columns with Ordering

Source: https://github.com/georgekaraszi/activerecordextended/blob/main/_autodocs/api-reference-window.md

Partition data across multiple columns by passing multiple symbols to `partition_by`. This allows for more granular partitioning, and the `order_by` option can still be used to define the order within these complex partitions.

```ruby
User.define_window(:complex_window)
    .partition_by(:category, :region, order_by: { created_at: :asc })
```

```sql
WINDOW complex_window AS (PARTITION BY category, region ORDER BY created_at ASC)
```

--------------------------------

### Define Window Partition and Ordering

Source: https://github.com/georgekaraszi/activerecordextended/blob/main/_autodocs/types.md

Defines the `PARTITION BY` and `ORDER BY` clauses for a window function. This specifies how rows are grouped and ordered within the window frame.

```ruby
def partition_by(*cols, order_by: nil)
  # Define partition and ordering for a window
```

--------------------------------

### Lag and Lead with `select_window`

Source: https://github.com/georgekaraszi/activerecordextended/blob/main/_autodocs/api-reference-window.md

Applies `lag` and `lead` window functions to retrieve values from preceding and succeeding rows within a partition, ordered by `created_at`. This is useful for time-series analysis or comparing adjacent records.

```ruby
Order.define_window(:time_window)
     .partition_by(:user_id, order_by: { created_at: :asc })
     .select(:id, :amount, :created_at)
     .select_window(:lag, :amount, over: :time_window, as: :previous_amount)
     .select_window(:lead, :amount, over: :time_window, as: :next_amount)
```

--------------------------------

### Excluding Large Subqueries with not_materialized

Source: https://github.com/georgekaraszi/activerecordextended/blob/main/_autodocs/api-reference-cte.md

Use `not_materialized` for small inline subqueries to avoid unnecessary materialization. This example defines `active_ids` as a non-materialized CTE to efficiently join with posts.

```ruby
active_ids = User.where(active: true).select(:id)

Post.with.not_materialized(active_users: User.where(id: active_ids))
    .joins("JOIN active_users ON active_users.id = posts.user_id")
```

--------------------------------

### Partition by Column with Hash-Style Ordering

Source: https://github.com/georgekaraszi/activerecordextended/blob/main/_autodocs/api-reference-window.md

Use a hash for the `order_by` option to specify multiple sorting criteria within a partition. This provides flexibility in defining complex ordering for window functions.

```ruby
User.define_window(:ordered_window)
    .partition_by(:department, order_by: { salary: :desc, name: :asc })
```

```sql
WINDOW ordered_window AS (PARTITION BY department ORDER BY salary DESC, name ASC)
```

--------------------------------

### Partition by Column with Ordering

Source: https://github.com/georgekaraszi/activerecordextended/blob/main/_autodocs/api-reference-window.md

Combine `partition_by` with the `order_by` option to specify the ordering of records within each partition. This is essential for window functions that depend on the sequence of rows, such as ranking or lead/lag functions.

```ruby
User.define_window(:number_window)
    .partition_by(:number, order_by: { id: :desc })
```

```sql
WINDOW number_window AS (PARTITION BY number ORDER BY id DESC)
```

--------------------------------

### SQL Output for Single CTE

Source: https://github.com/georgekaraszi/activerecordextended/blob/main/README.md

This SQL demonstrates the structure of a query using a single CTE defined by the `.with` method.

```sql
WITH "highly_liked" AS (SELECT "profile_ls".* FROM "profile_ls" WHERE (likes >= 300))
SELECT "users".* FROM "users" JOIN highly_liked ON highly_liked.user_id = users.id
```

--------------------------------

### Create CTE Query with Single With Clause

Source: https://github.com/georgekaraszi/activerecordextended/blob/main/README.md

Use the `.with` method to define Common Table Expressions (CTEs) for complex queries. This example creates a CTE named 'highly_liked'.

```ruby
alice = User.create!
bob   = User.create!
randy = User.create!
ProfileL.create!(user_id: alice.id, likes: 200)
ProfileL.create!(user_id: bob.id,   likes: 400)
ProfileL.create!(user_id: randy.id, likes: 600)

User.with(highly_liked: ProfileL.where("likes > 300"))
    .joins("JOIN highly_liked ON highly_liked.user_id = users.id") #=> [bob, randy]
```

--------------------------------

### Define Multiple Windows

Source: https://github.com/georgekaraszi/activerecordextended/blob/main/_autodocs/api-reference-window.md

Chains multiple window definitions together. Each `define_window` call creates a new named window with its own partitioning and ordering.

```ruby
User.define_window(:number_window).partition_by(:number, order_by: { id: :desc })
    .define_window(:name_window).partition_by(:name, order_by: :id)
    .define_window(:no_order_name).partition_by(:name)
```

--------------------------------

### Window Function with Descending Order

Source: https://github.com/georgekaraszi/activerecordextended/blob/main/_autodocs/api-reference-window.md

Demonstrates how to use `order_by` with `:desc` to select the highest value within each partition. This is useful for ranking or identifying top records.

```ruby
User.define_window(:w1).partition_by(:dept, order_by: { salary: :desc })
    .select_window(:first_value, :name, over: :w1)
```

--------------------------------

### Multi-Source Query with UNION

Source: https://github.com/georgekaraszi/activerecordextended/blob/main/_autodocs/README.md

Illustrates how to combine results from multiple distinct queries using the `.union` method, followed by ordering and distinct selection. This is useful for querying across different conditions or scopes.

```ruby
active = User.where(status: 'active')
recent = User.where("last_login > ?", 1.month.ago)

User.union(active, recent)
    .order_union(last_login: :desc)
    .distinct
```

--------------------------------

### with Method

Source: https://github.com/georgekaraszi/activerecordextended/blob/main/_autodocs/api-reference-cte.md

Main entry point for defining CTEs on a relation. It allows you to define named subqueries that can be referenced in the main query.

```APIDOC
## with Method

### Description
Main entry point for defining CTEs on a relation.

### Signature
```ruby
with(opts = :chain, *rest) -> ActiveRecord::Relation | WithChain
```

### Parameters
#### Path Parameters
- **opts** (Hash, Symbol, WithCTE) - Optional - Default: `:chain` - CTE definition hash or WithCTE object. Use `:chain` to get chain object.
- **rest** (*) - Optional - Additional arguments (for future expansion)

### Return
- If `opts == :chain`: Returns a `WithChain` object for chaining `.recursive`, `.materialized`, etc.
- Otherwise: Returns a new `ActiveRecord::Relation` with CTEs defined.

### Request Example
Basic CTE:
```ruby
highly_liked = ProfileL.where("likes > 300")

User.with(highly_liked: highly_liked)
    .joins("JOIN highly_liked ON highly_liked.user_id = users.id")
# => [users with likes > 300]

# SQL:
# WITH "highly_liked" AS (
#   SELECT "profile_ls".* FROM "profile_ls" WHERE (likes > 300)
# )
# SELECT "users".* FROM "users" 
# JOIN highly_liked ON highly_liked.user_id = users.id
```

Multiple CTEs in one call:
```ruby
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")

# SQL:
# WITH "highly_liked" AS (
#   SELECT "profile_ls".* FROM "profile_ls" WHERE (likes > 300)
# ), "less_liked" AS (
#   SELECT "profile_ls".* FROM "profile_ls" WHERE (likes <= 200)
# )
# SELECT "users".* FROM "users" 
# JOIN highly_liked ON highly_liked.user_id = users.id
# JOIN less_liked ON less_liked.user_id = users.id
```
```

--------------------------------

### Find Common Records with UNION INTERSECT

Source: https://github.com/georgekaraszi/activerecordextended/blob/main/_autodocs/api-reference-union.md

Use `union_intersect` to find records that match criteria across all provided queries. This example finds users who have placed high-value orders AND recent orders.

```ruby
high_value = Order.joins(:user)
                  .where("orders.amount > ?", 10000)
                  .select(:user_id)

recent = Order.joins(:user)
              .where("orders.created_at > ?", 1.month.ago)
              .select(:user_id)

User.union_intersect(high_value, recent)
# => Users with both high-value AND recent orders
```

--------------------------------

### JSONB/HSTORE Type `contains` Example

Source: https://github.com/georgekaraszi/activerecordextended/blob/main/_autodocs/api-reference-predicates.md

Illustrates using `contains` to filter records based on a JSONB or HSTORE column containing specific key-value pairs. The column must be of JSONB or HSTORE type.

```ruby
alice = User.create!(data: { nickname: "ARExtend" })
bob   = User.create!(data: { nickname: "ARExtended" })
randy = User.create!(data: { nickname: "ARExtended" })

User.where.contains(data: { nickname: "ARExtended" }) 
# => [bob, randy]

# SQL: SELECT "users".* FROM "users" WHERE "users"."data" @> '{"nickname": "ARExtended"}'
```

--------------------------------

### JSON Function Initialization

Source: https://github.com/georgekaraszi/activerecordextended/blob/main/_autodocs/types.md

Demonstrates the initialization of Arel JSON function nodes, which accept variable arguments and auto-wrap non-array arguments.

```ruby
Arel::Nodes::RowToJson.new(subquery)
# Or:
Arel::Nodes::RowToJson.new(arg1, arg2, ...)
```

--------------------------------

### Chaining Multiple CTEs in Ruby

Source: https://github.com/georgekaraszi/activerecordextended/blob/main/_autodocs/api-reference-cte.md

Demonstrates how to chain multiple CTEs using the `with` method, both individually and as a hash. Use this when your query requires multiple distinct CTEs.

```ruby
highly_liked = ProfileL.where("likes > 300")
less_liked = ProfileL.where("likes <= 200")

User.with(highly_liked: highly_liked)
    .with(less_liked: less_liked)
    .joins("JOIN highly_liked ON highly_liked.user_id = users.id")
    .joins("JOIN less_liked ON less_liked.user_id = users.id")
```

```ruby
# Equivalent to:
User.with(
  highly_liked: highly_liked,
  less_liked: less_liked
)...
```

--------------------------------

### CTE Definition Hash Example

Source: https://github.com/georgekaraszi/activerecordextended/blob/main/_autodocs/types.md

Defines Common Table Expressions (CTEs) by mapping CTE names to their query definitions. Values can be ActiveRecord::Relations, literal SQL strings, or internal WithCTE objects.

```ruby
{
  active_users: User.where(active: true),
  recent_posts: Post.where("created_at > ?", 1.week.ago)
}
```

--------------------------------

### Window Function with Ascending Order

Source: https://github.com/georgekaraszi/activerecordextended/blob/main/_autodocs/api-reference-window.md

Illustrates using `order_by` with `:asc` to select the lowest value within each partition. This is useful for identifying bottom records or first occurrences.

```ruby
User.define_window(:w2).partition_by(:dept, order_by: { salary: :asc })
    .select_window(:first_value, :name, over: :w2)
```

--------------------------------

### Hash to Dot Notation Conversion

Source: https://github.com/georgekaraszi/activerecordextended/blob/main/_autodocs/api-reference-foster-select.md

Demonstrates how nested hashes are converted into SQL dot notation for querying.

```ruby
{ products: { category: :name } } => "products.category.name"
```

--------------------------------

### Select Window Functions

Source: https://github.com/georgekaraszi/activerecordextended/blob/main/README.md

Apply window functions like `row_number` and `first_value` to a defined window. Use `over:` to specify the window name and `as:` for an alias. Optional arguments can be passed for the window function.

```ruby
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)
```

--------------------------------

### define_window

Source: https://github.com/georgekaraszi/activerecordextended/blob/main/_autodocs/api-reference-window.md

Establishes a named window frame for use with window functions. Returns a `DefineWindowChain` object for chaining `.partition_by()`.

```APIDOC
## define_window

### Description
Establishes a named window frame for use with window functions.

### Signature
```ruby
define_window(name)
```

### Parameters
#### Path Parameters
- **name** (Symbol, String) - Required - Name for the window definition

### Return
Returns a `DefineWindowChain` object for chaining `.partition_by()`.

### Behavior
Creates a PostgreSQL `WINDOW` clause definition that can be referenced by window functions.

### Examples
Basic window definition:

```ruby
User.define_window(:number_window)
    .partition_by(:number, order_by: { id: :desc })

# SQL:
# SELECT * FROM users
# WINDOW number_window AS (PARTITION BY number ORDER BY id DESC)
```

Multiple windows:

```ruby
User.define_window(:number_window).partition_by(:number, order_by: { id: :desc })
    .define_window(:name_window).partition_by(:name, order_by: :id)
    .define_window(:no_order_name).partition_by(:name)

# SQL:
# WINDOW number_window AS (PARTITION BY number ORDER BY id DESC),
#        name_window AS (PARTITION BY name ORDER BY id),
#        no_order_name AS (PARTITION BY name)
```
```

--------------------------------

### Print Formatted SQL with Nice Union

Source: https://github.com/georgekaraszi/activerecordextended/blob/main/_autodocs/integration-patterns.md

Utilize helper methods to print formatted SQL queries, including unions and CTEs (Common Table Expressions), for easier debugging and analysis.

```ruby
# Print formatted SQL
puts User.with(data: Order.select(:id))
         .to_nice_union_sql

# Just the union part
puts User.union(q1, q2).to_union_sql
```

--------------------------------

### DefineWindowChain Builder

Source: https://github.com/georgekaraszi/activerecordextended/blob/main/_autodocs/types.md

Builder for defining window specifications, allowing users to specify partitioning and ordering clauses for window functions.

```APIDOC
## Class: ActiveRecordExtended::QueryMethods::Window::DefineWindowChain

### Description
Builder for window definitions.

### Methods
- `partition_by(*cols, order_by: nil)`: Define partition and ordering for a window.
```

--------------------------------

### SQL Output for Selected Window Functions

Source: https://github.com/georgekaraszi/activerecordextended/blob/main/README.md

This SQL shows the generated query with `ROW_NUMBER` and `FIRST_VALUE` window functions applied over the defined `number_window`.

```sql
SELECT "users"."id",
        "users"."name",
        (ROW_NUMBER() OVER number_window)      AS "row_id",
        (FIRST_VALUE(name) OVER number_window) AS "first_value_name"
FROM "users"
WINDOW number_window AS (PARTITION BY number ORDER BY id DESC)
```

--------------------------------

### Instrument SQL Query Execution

Source: https://github.com/georgekaraszi/activerecordextended/blob/main/_autodocs/integration-patterns.md

Subscribe to ActiveSupport::Notifications to instrument and log specific types of SQL queries, such as those involving CTEs, to monitor their execution time.

```ruby
# Instrument query execution
ActiveSupport::Notifications.subscribe("sql.active_record") do |event|
  if event[:sql].include?("WITH")
    Rails.logger.info("CTE query took #{event[:duration]}ms")
  end
end
```

--------------------------------

### Type Dispatch for Query Parameters

Source: https://github.com/georgekaraszi/activerecordextended/blob/main/_autodocs/README.md

Shows how parameter types influence the behavior of query methods like `.any` and `.with`. For instance, `.any` handles single values differently from arrays, and `.with` distinguishes between ActiveRecord::Relations and raw SQL strings.

```ruby
.where.any(tags: 1)        # Single value (ANY scalar)
.where.any(tags: [1, 2])   # Array → returns empty (must be single)

.with(name: relation)      # ActiveRecord::Relation → subquery
.with(name: "SELECT ...")  # String → raw SQL
```

--------------------------------

### Fluent Query Chaining with ActiveRecord Extended

Source: https://github.com/georgekaraszi/activerecordextended/blob/main/_autodocs/README.md

Demonstrates the chainable syntax provided by ActiveRecord Extended, where methods return `ActiveRecord::Relation` objects. This allows for fluent construction of complex queries.

```ruby
User.with(expensive: subquery)
    .where(active: true)
    .define_window(:win).partition_by(:dept)
    .select_window(:row_number, over: :win)
    .order_union(:id)
    .limit(100)
```

--------------------------------

### PostgreSQL Materialization Hints

Source: https://github.com/georgekaraszi/activerecordextended/blob/main/_autodocs/integration-patterns.md

Determines if PostgreSQL materialization hints can be used based on the database version. It optimizes query execution by conditionally using `materialized` for PostgreSQL 12.0000+.

```ruby
def self.use_materialization_hints?
  ActiveRecord::Base.connection.postgresql_version >= 120000
end

def self.optimized_with(name, subquery)
  if use_materialization_hints?
    with.materialized(name => subquery)
  else
    with(name => subquery)
  end
end
```

--------------------------------

### OrderBy Hash/Symbol Syntax

Source: https://github.com/georgekaraszi/activerecordextended/blob/main/_autodocs/types.md

Specifies ordering for window functions, JSON methods, and aggregate selection. Supports simple symbols for ascending order or hashes for complex multi-column ordering with direction.

```ruby
# Symbol (simple ASC order)
order_by: :id
```

```ruby
# Hash with symbol values
order_by: { id: :asc, name: :desc }
```

```ruby
# Mixed associations
order_by: { 
  products: :name, 
  users: { created_at: :desc }
}
```

--------------------------------

### Offset Functions (Lag/Lead) for Trend Analysis

Source: https://github.com/georgekaraszi/activerecordextended/blob/main/_autodocs/api-reference-window.md

Utilizes lag and lead window functions to access previous or next rows within a partition, enabling analysis of trends and changes like month-over-month growth.

```ruby
SalesData.define_window(:sales_window)
         .partition_by(:product_id, order_by: { month: :asc })
         .select(:id, :product_id, :month, :sales)
         .select_window(:lag, :sales, over: :sales_window, as: :prev_month_sales)
         .select_window(:lead, :sales, over: :sales_window, as: :next_month_sales)

# Calculate month-over-month growth:
# SELECT *, (sales - prev_month_sales) as growth FROM (...)
```

--------------------------------

### Chaining Predicates with ActiveRecord

Source: https://github.com/georgekaraszi/activerecordextended/blob/main/_autodocs/api-reference-predicates.md

Demonstrates how to chain multiple predicate methods with standard ActiveRecord where clauses using an AND conjunction.

```ruby
User.where(active: true)
    .where.overlap(tags: [1, 2])
    .where.contains(data: { role: 'admin' })
# Combines all conditions with AND in WHERE
```

--------------------------------

### WindowSelectBuilder

Source: https://github.com/georgekaraszi/activerecordextended/blob/main/_autodocs/types.md

Constructs Arel nodes for window function select expressions, taking the function name, arguments, and window name as input.

```APIDOC
## Class: ActiveRecordExtended::QueryMethods::Window::WindowSelectBuilder

### Description
Constructs window function select expressions.

### Methods
- `initialize(function_name, args, window_name)`: Initializes the builder.
- `build_select(alias_name = nil)`: Build Arel node for the window function select expression.
```

--------------------------------

### Test Complex Queries with Fixtures

Source: https://github.com/georgekaraszi/activerecordextended/blob/main/_autodocs/integration-patterns.md

Use fixtures to set up test data for complex queries involving unions and window functions. Ensure results match expected outcomes.

```ruby
describe "Complex Queries" do
  let!(:users) { create_list(:user, 10) }
  let!(:orders) { create_list(:order, 50, user: users.sample) }

  describe "union of active and recent users" do
    it "returns correct results" do
      active = User.where(active: true)
      recent = User.where("last_seen > ?", 1.month.ago)

      results = User.union(active, recent).to_a
      expect(results).to match_array(
        active.to_a + recent.to_a - (active.to_a & recent.to_a)
      )
    end
  end

  describe "window function ranking" do
    it "ranks products by price per category" do
      results = Product.define_window(:rank)
                      .partition_by(:category_id, order_by: { price: :desc })
                      .select_window(:row_number, over: :rank, as: :rank)
                      .to_a

      expect(results.first.rank).to eq 1
    end
  end
end
```

--------------------------------

### Where Chain Prepending with Custom Methods

Source: https://github.com/georgekaraszi/activerecordextended/blob/main/_autodocs/overview.md

Illustrates how custom methods like `any` and `inet_contained_within` are prepended to ActiveRecord's `WhereChain` and `Inet` respectively, allowing for extended query capabilities.

```ruby
User.where.any(tags: 1)        # Calls prepended WhereChain#any
User.where.inet_contained_within(ip: "192.168.0.0/24")  # Calls prepended Inet#inet_contained_within
```

--------------------------------

### Use Window Functions for Ranking

Source: https://github.com/georgekaraszi/activerecordextended/blob/main/_autodocs/integration-patterns.md

Applies window functions for per-category rankings within a single query, avoiding the need for separate queries. Filters for top N items per category.

```ruby
Product.define_window(:category_rank)
       .partition_by(:category_id, order_by: { price: :desc })
       .select(:id, :name, :category_id, :price)
       .select_window(:row_number, over: :category_rank, as: :price_rank)
       .where("price_rank <= 5")  # Top 5 per category

# SQL:
# SELECT ... FROM (
#   SELECT *, ROW_NUMBER() OVER category_rank AS price_rank
#   FROM products
#   WINDOW category_rank AS (PARTITION BY category_id ORDER BY price DESC)
# )
# WHERE price_rank <= 5
```

--------------------------------

### First Value in Partition with `select_window`

Source: https://github.com/georgekaraszi/activerecordextended/blob/main/_autodocs/api-reference-window.md

Retrieves the first value of the `name` column within each partition, based on the defined window. Useful for identifying the first record in a group according to a specific order.

```ruby
User.define_window(:number_window)
    .partition_by(:number, order_by: { id: :desc })
    .select(:id, :name)
    .select_window(:first_value, :name, over: :number_window, as: :first_name)
```

```sql
SELECT "users"."id", "users"."name",
       (FIRST_VALUE("name") OVER number_window) AS "first_name"
FROM "users"
WINDOW number_window AS (PARTITION BY number ORDER BY id DESC)
```

--------------------------------

### Chaining foster_select with other Select Methods

Source: https://github.com/georgekaraszi/activerecordextended/blob/main/_autodocs/api-reference-foster-select.md

Illustrates how `foster_select` can be chained with standard ActiveRecord `select` calls to include both direct columns and aggregated associations.

```ruby
User.select(:id)
    .foster_select(
      post_count: { posts: :id, cast_with: :count }
    )

# Both id and post_count are selected
```

--------------------------------

### Equivalent of none_of for simple hash conditions

Source: https://github.com/georgekaraszi/activerecordextended/blob/main/_autodocs/api-reference-conditionals.md

Demonstrates an equivalent and often more concise way to achieve the same result as `none_of` when dealing with simple hash conditions.

```ruby
User.where.not(uid: [1, 2])  # When using simple hash conditions
```

--------------------------------

### Monitor for N+1 Queries

Source: https://github.com/georgekaraszi/activerecordextended/blob/main/_autodocs/integration-patterns.md

Implement a mechanism to detect and warn about potential N+1 query problems by counting the number of SQL queries executed within a given block of code. This helps identify performance bottlenecks.

```ruby
# Monitor for N+1
def self.watch_for_n_plus_one
  count = 0
  ActiveSupport::Notifications.subscribe("sql.active_record") do
    count += 1
  end

  yield

  Rails.logger.warn("#{count} queries executed") if count > 5
end
```

--------------------------------

### Create CTE Query with Multiple With Clauses

Source: https://github.com/georgekaraszi/activerecordextended/blob/main/README.md

Chain multiple `.with` calls or provide multiple CTEs in a single call to build a `WITH` statement with multiple CTEs.

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

# OR

User.with(highly_liked: ProfileL.where("likes > 300"))
    .with(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")
```

--------------------------------

### Window Function without Explicit Ordering

Source: https://github.com/georgekaraszi/activerecordextended/blob/main/_autodocs/api-reference-window.md

Shows the behavior of a window function when no `order_by` clause is specified within the partition. The row numbers assigned will be arbitrary and database-dependent.

```ruby
User.define_window(:unordered).partition_by(:dept)
    .select_window(:row_number, over: :unordered)
```

--------------------------------

### Handle Undefined Column Reference

Source: https://github.com/georgekaraszi/activerecordextended/blob/main/_autodocs/errors.md

Use this when selecting a column that does not exist, referencing a wrong table alias, or encountering a column name typo. Ensure you use existing columns for recovery.

```ruby
User.foster_select(bad_column: :nonexistent)
# PG::UndefinedColumn: ERROR:  column "nonexistent" does not exist
```

```ruby
User.foster_select(id: :id)  # Use existing column
```

--------------------------------

### SQL Output for Recursive CTE

Source: https://github.com/georgekaraszi/activerecordextended/blob/main/README.md

This SQL demonstrates the structure of a query using a recursive CTE, indicated by `WITH RECURSIVE`.

```sql
WITH RECURSIVE "highly_liked" AS (SELECT "profile_ls".* FROM "profile_ls" WHERE (likes >= 300))
SELECT "users".* FROM "users" JOIN highly_liked ON highly_liked.user_id = users.id
```

--------------------------------

### Applying Predicates to Joined Tables

Source: https://github.com/georgekaraszi/activerecordextended/blob/main/_autodocs/api-reference-predicates.md

Illustrates how predicate methods can be applied to columns of joined tables within an ActiveRecord query.

```ruby
Post.joins(:author)
    .where.overlap(author: { tags: [1, 2] })
```

--------------------------------

### Chain Window Scopes for Ranking and Statistics

Source: https://github.com/georgekaraszi/activerecordextended/blob/main/_autodocs/integration-patterns.md

Chain window function scopes to perform operations like ranking orders within a user's monthly activity or calculating annual statistics. Use `define_window` to set up window definitions.

```ruby
class Order < ApplicationRecord
  scope :with_monthly_rank, ->(direction = :desc) {
    define_window(:monthly)
      .partition_by(:user_id, order_by: { created_at: direction })
      .select(:id, :user_id, :amount, :created_at)
      .select_window(:row_number, over: :monthly, as: :monthly_order)
  }

  scope :with_annual_stats, ->(column = :amount) {
    define_window(:annual)
      .partition_by(:user_id)
      .select_window(:avg, column, over: :annual, as: :annual_avg)
      .select_window(:sum, column, over: :annual, as: :annual_total)
  }
end

# Use together:
Order.with_monthly_rank(:asc)
     .with_annual_stats(:amount)
     .where("monthly_order <= 10")
```