Selecto v0.3.16

Selecto.Advanced.LateralJoin (Selecto v0.3.16)

LATERAL join support for correlated subqueries and advanced join patterns.

LATERAL joins allow the right side of a join to reference columns from the left side, enabling powerful correlated subquery patterns that are not possible with standard joins.

examples

 Examples 

# Basic LATERAL join with correlated subquery
selecto
|> Selecto.lateral_join(
  :left,
  fn base_query ->
    Selecto.configure(rental_domain, connection)
    |> Selecto.select([{:func, "COUNT", ["*"], as: "rental_count"}])
    |> Selecto.filter([{"customer_id", {:ref, "customer.customer_id"}}])
    |> Selecto.filter([{"rental_date", {:>, {:func, "CURRENT_DATE - INTERVAL '30 days'"}}}])
  end,
  as: "recent_rentals"
)

# LATERAL join with table function
selecto
|> Selecto.lateral_join(
  :inner,
  {:unnest, "film.special_features"},
  as: "features"
)

Link to this section Summary

Functions

create_lateral_join(join_type, subquery_builder_or_function, alias_name, opts \\ [])

Create a LATERAL join specification.

validate_correlations(spec, base_selecto)

Validate LATERAL join correlations against the base query.

Link to this section Functions

Link to this function

create_lateral_join(join_type, subquery_builder_or_function, alias_name, opts \\ [])

Create a LATERAL join specification.

parameters

 Parameters

  • join_type - Type of join (:left, :inner, :right, :full)
  • subquery_builder - Function that builds the correlated subquery
  • alias_name - Alias for the LATERAL join results
  • opts - Additional options

examples

 Examples 

# Correlated subquery LATERAL join
lateral_spec = LateralJoin.create_lateral_join(
  :left,
  fn base_query ->
    rental_query
    |> Selecto.filter([{"customer_id", {:ref, "customer.customer_id"}}])
    |> Selecto.limit(5)
  end,
  "recent_rentals"
)

# Table function LATERAL join  
lateral_spec = LateralJoin.create_lateral_join(
  :inner,
  {:unnest, "film.special_features"},
  "features"
)
Link to this function

validate_correlations(spec, base_selecto)

Validate LATERAL join correlations against the base query.

Ensures that all correlation references in the LATERAL subquery refer to valid columns in the parent/left-side tables.