Class Join

Namespace: YndigoBlue.Velocity.Model

Assembly: YndigoBlue.Velocity.dll

Represents a SQL JOIN operation that combines rows from two or more tables based on column equality or function-based predicates such as geospatial relationships.

public class Join : IFromItem

Inheritance: object

Join

Implements: IFromItem

Remarks

The simplest constructors accept two tables and a shared column name for an equi-join. For joins where the column names differ, or where a non-equality operator is required, use AddJoinItem(IFromItem, string, string, IFromItem, string, string, JoinType, JoinOperatorType) to supply explicit columns and a JoinOperatorType. All standard SQL join types are supported via JoinType: Inner, LeftOuter, RightOuter and FullOuter.

Function-based joins allow a Function to act as the ON condition instead of a column comparison. This is used for geospatial joins (e.g. ST_Intersects, ST_Contains) and any other predicate that is expressed as a function call over columns from both tables.

Multi-table joins are created by passing a list of tuples to a single Join constructor. Each tuple defines a pair of tables and their join condition, and Velocity generates the correct chain of JOIN clauses.

Examples

Equi-join on a shared column name (defaults to INNER JOIN):

var join = new Join(ordersTable, customersTable, "customer_id");

var q = new Query()
    .Select([ordersTable["order_date"], customersTable["name"]])
    .From(join);

// SQL: FROM orders
//      INNER JOIN customers ON orders.customer_id = customers.customer_id

Equi-join with a specific join type:

var join = new Join(ordersTable, customersTable, "customer_id", JoinType.LeftOuter);

// SQL: FROM orders
//      LEFT JOIN customers ON orders.customer_id = customers.customer_id

Self-join with aliases:

var join = new Join(employeesTable, "e", employeesTable, "m", "manager_id");

// SQL: FROM employees e
//      INNER JOIN employees m ON e.manager_id = m.manager_id

Function-based join for geospatial predicates:

var join = new Join(
    regionsTable,
    locationsTable,
    new Function(FunctionType.SpatialIntersects, regionsTable["geom"], locationsTable["geom"]));

var q = new Query()
    .Select([
        (regionsTable["name"], "region_name"),
        (locationsTable["name"], "location_name"),
    ])
    .From(join);

// SQL: FROM regions
//      INNER JOIN locations ON ST_Intersects(regions.geom, locations.geom)

Multi-table join using a list of tuples with shared column names:

var join = new Join([
    (ordersTable, customersTable, "customer_id"),
    (ordersTable, productsTable, "product_id"),
]);

// SQL: FROM orders
//      INNER JOIN customers ON orders.customer_id = customers.customer_id
//      INNER JOIN products  ON orders.product_id  = products.product_id

Multi-table join using function-based predicates:

var join = new Join([
    (regionsTable, locationsTable, new SpatialIntersects(regionGeom, locationGeom)),
    (regionsTable, routesTable, new SpatialIntersects(regionGeom, routeGeom)),
]);

// SQL: FROM regions
//      INNER JOIN locations ON ST_Intersects(regions.geom, locations.geom)
//      INNER JOIN routes    ON ST_Intersects(regions.geom, routes.geom)

Constructors

Join(): Creates an empty Join that can be populated via AddJoinItem(IFromItem, string, string, IFromItem, string, string, JoinType, JoinOperatorType) calls.

Join(IList<(IFromItem, string, string, IFromItem, string, string)>): Creates a multi-table INNER JOIN from a list of tuples with explicit aliases and separate column names for each side of the join.

Join(IList<(IFromItem, IFromItem, string)>): Creates a multi-table INNER JOIN from a list of (parent, child, columnName) tuples. Each tuple joins the child to the parent on a column that exists in both.

Join(IList<(IFromItem, IFromItem, Function)>): Creates a multi-table INNER JOIN from a list of (parent, child, function) tuples. Each tuple joins the child to the parent using a function-based predicate as the ON condition.

Join(IFromItem, string, string, IFromItem, string, string, JoinType, JoinOperatorType): Creates a join between two items with explicit aliases, separate column names, and a comparison operator. This is the most flexible constructor, essential for outer joins with subqueries where explicit aliasing is required.

Join(IFromItem, string, IFromItem, string, string): Creates an INNER JOIN between two items with explicit aliases, on a shared column name. Aliases are required when joining a table to itself (self-join).

Join(IFromItem, string, IFromItem, string, Filter, JoinType): Creates a join between two items with explicit aliases and a complex multi-condition ON clause.

Join(IFromItem, string, ISelectItem, IFromItem, string, ISelectItem, JoinType, JoinOperatorType): Creates a non-equi join with explicit aliases using column references and a comparison operator.

Join(IFromItem, IFromItem, string): Creates an INNER JOIN between two items on a column that exists in both.

Join(IFromItem, IFromItem, string, JoinType): Creates a join between two items on a shared column name with a specified join type.

Join(IFromItem, IFromItem, JoinType): Creates a CROSS JOIN between two items, returning the Cartesian product (all combinations). No join condition is required. The result will have table1_rows × table2_rows rows.

Join(IFromItem, IFromItem, Filter, JoinType): Creates a join between two items with a complex multi-condition ON clause using a Filter.

Join(IFromItem, IFromItem, Function, JoinType): Creates a function-based join between two items using a predicate such as a geospatial function.

Join(IFromItem, ISelectItem, IFromItem, ISelectItem, JoinType, JoinOperatorType): Creates a non-equi join between two items using column references and a comparison operator.

Properties

FromItems: Gets the list of child IFromItem instances in this Join.

Name: Gets the name of the FROM item (table, view, or alias).

SelectItems: Gets the collection of select items (columns or expressions) available from this FROM item.

Methods

AddJoinItem(IFromItem, string, string, IFromItem, string, string, JoinType, JoinOperatorType): Adds a column-based join item to this Join, with explicit aliases and column names for each side.

AddJoinItem(IFromItem, string, IFromItem, string, JoinType, Function): Adds a function-based join item to this Join, where a predicate function forms the ON condition.

AddJoinItem(IFromItem, string, ISelectItem, IFromItem, string, ISelectItem, JoinType, JoinOperatorType): Adds a column-based join item to this Join, using explicit ISelectItem references for each side of the condition.

GetSelectItem(string): Retrieves the select item with the specified name.

HasSelectItem(string): Determines whether this FROM item contains a select item with the specified name.