Constructor Join

Namespace

YndigoBlue.Velocity.Model

Assembly

YndigoBlue.Velocity.dll

Join()

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

public Join()

Join(IFromItem, IFromItem, string)

Creates an INNER JOIN between two items on a column that exists in both.

public Join(IFromItem fromItem1, IFromItem fromItem2, string columnName)

Parameters

fromItem1 IFromItem

The first (left) table or join.

fromItem2 IFromItem

The second (right) table or join.

columnName string

The column name that is present in both items.

Examples

var join = new Join(ordersTable, customersTable, "customer_id");
// SQL: FROM orders INNER JOIN customers ON orders.customer_id = customers.customer_id

Join(IFromItem, IFromItem, Function, JoinType)

Creates a function-based join between two items using a predicate such as a geospatial function.

public Join(IFromItem fromItem1, IFromItem fromItem2, Function function, JoinType joinType = JoinType.Inner)

Parameters

fromItem1 IFromItem

The first (left) table or join.

fromItem2 IFromItem

The second (right) table or join.

function Function

The predicate function that forms the ON condition (e.g. SpatialIntersects).

joinType JoinType

The type of join to perform. Defaults to Inner.

Examples

// Spatial inner join — find which locations fall inside which regions
var join = new Join(
    regionsTable,
    locationsTable,
    new Function(FunctionType.SpatialIntersects, regionGeomCol, locationGeomCol));
// SQL: FROM regions INNER JOIN locations ON ST_Intersects(regions.geom, locations.geom)

// Spatial left join — include regions even when no location matches
var join = new Join(
    regionsTable,
    locationsTable,
    new Function(FunctionType.SpatialContains, regionGeomCol, locationGeomCol),
    JoinType.LeftOuter);
// SQL: FROM regions LEFT JOIN locations ON ST_Contains(regions.geom, locations.geom)

Join(IFromItem, IFromItem, string, JoinType)

Creates a join between two items on a shared column name with a specified join type.

public Join(IFromItem fromItem1, IFromItem fromItem2, string columnName, JoinType joinType = JoinType.Inner)

Parameters

fromItem1 IFromItem

The first (left) table or join.

fromItem2 IFromItem

The second (right) table or join.

columnName string

The column name that is present in both items.

joinType JoinType

The type of join to perform.

Examples

var join = new Join(ordersTable, customersTable, "customer_id", JoinType.LeftOuter);
// SQL: FROM orders LEFT JOIN customers ON orders.customer_id = customers.customer_id

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).

public Join(IFromItem fromItem1, string alias1, IFromItem fromItem2, string alias2, string columnName)

Parameters

fromItem1 IFromItem

The first (left) table or join.

alias1 string

Alias for the first item.

fromItem2 IFromItem

The second (right) table or join.

alias2 string

Alias for the second item.

columnName string

The column name that is present in both items.

Examples

// Self-join: find employees and their managers
var join = new Join(employeesTable, "emp", employeesTable, "mgr", "manager_id");
// SQL: FROM employees emp INNER JOIN employees mgr ON emp.manager_id = mgr.manager_id

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.

public Join(IList<(IFromItem, IFromItem, string)> items)

Parameters

items IList<(IFromItem, IFromItem, string)>

A list of tuples where each entry is (parent, child, sharedColumnName).

Examples

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

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.

public Join(IList<(IFromItem, string, string, IFromItem, string, string)> items)

Parameters

items IList<(IFromItem, string, string, IFromItem, string, string)>

A list of tuples where each entry is (parent, parentAlias, parentColumnName, child, childAlias, childColumnName).

Examples

var join = new Join([
    (ordersTable, "o", "customer_id", customersTable, "c", "id"),
    (ordersTable, "o", "product_id", productsTable, "p", "id"),
]);
// SQL: FROM orders o
//      INNER JOIN customers c ON o.customer_id = c.id
//      INNER JOIN products  p ON o.product_id  = p.id

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.

public Join(IList<(IFromItem, IFromItem, Function)> items)

Parameters

items IList<(IFromItem, IFromItem, Function)>

A list of tuples where each entry is (parent, child, predicateFunction).

Examples

// Multi-table spatial join: find all (region, location, route) combinations
// where the location and route both intersect the region
var join = new Join([
    (regionsTable, locationsTable, new SpatialIntersects(regionGeomCol, locationGeomCol)),
    (regionsTable, routesTable, new SpatialIntersects(regionGeomCol, routeGeomCol)),
]);
// SQL: FROM regions
//      INNER JOIN locations ON ST_Intersects(regions.geom, locations.geom)
//      INNER JOIN routes    ON ST_Intersects(regions.geom, routes.geom)

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.

public Join(IFromItem fromItem1, IFromItem fromItem2, JoinType joinType = JoinType.Inner)

Parameters

fromItem1 IFromItem

The first (left) table or join.

fromItem2 IFromItem

The second (right) table or join.

joinType JoinType

Examples

var join = new Join(colorsTable, sizesTable, JoinType.Cross);
// SQL: FROM colors CROSS JOIN sizes
// If colors has 5 rows and sizes has 3 rows, result will have 15 rows

Join(IFromItem, IFromItem, Filter, JoinType)

Creates a join between two items with a complex multi-condition ON clause using a Filter.

public Join(IFromItem fromItem1, IFromItem fromItem2, Filter filter, JoinType joinType = JoinType.Inner)

Parameters

fromItem1 IFromItem

The first (left) table or join.

fromItem2 IFromItem

The second (right) table or join.

filter Filter

The filter containing compound join conditions with AND/OR logic.

joinType JoinType

The type of join to perform. Defaults to Inner.

Examples

// Multi-column join: ON (customers.id = orders.customer_id AND customers.region = orders.region)
var filter = new Filter(new List<IFilterItem> {
    new Criterion<Column>(customersTable["id"], ConditionalType.Equals, ordersTable["customer_id"]),
    new BooleanItem(BooleanType.And),
    new Criterion<Column>(customersTable["region"], ConditionalType.Equals, ordersTable["region"])
});

var join = new Join(customersTable, ordersTable, filter);
// SQL: FROM customers
//      INNER JOIN orders ON (customers.id = orders.customer_id AND customers.region = orders.region)

Join(IFromItem, string, IFromItem, string, Filter, JoinType)

Creates a join between two items with explicit aliases and a complex multi-condition ON clause.

public Join(IFromItem fromItem1, string alias1, IFromItem fromItem2, string alias2, Filter filter, JoinType joinType = JoinType.Inner)

Parameters

fromItem1 IFromItem

The first (left) table or join.

alias1 string

Alias for the first item.

fromItem2 IFromItem

The second (right) table or join.

alias2 string

Alias for the second item.

filter Filter

The filter containing compound join conditions with AND/OR logic.

joinType JoinType

The type of join to perform. Defaults to Inner.

Examples

// Self-join with multiple conditions
var filter = new Filter(new List<IFilterItem> {
    new Criterion<Column>(employeesTable["manager_id"], ConditionalType.Equals, employeesTable["employee_id"]),
    new BooleanItem(BooleanType.And),
    new Criterion<Column>(employeesTable["department"], ConditionalType.Equals, employeesTable["department"])
});

var join = new Join(employeesTable, "e", employeesTable, "m", filter);
// SQL: FROM employees e
//      INNER JOIN employees m ON (e.manager_id = m.employee_id AND e.department = m.department)

Join(IFromItem, ISelectItem, IFromItem, ISelectItem, JoinType, JoinOperatorType)

Creates a non-equi join between two items using column references and a comparison operator.

public Join(IFromItem fromItem1, ISelectItem column1, IFromItem fromItem2, ISelectItem column2, JoinType joinType = JoinType.Inner, JoinOperatorType joinOperatorType = JoinOperatorType.Equals)

Parameters

fromItem1 IFromItem

The first (left) table or join.

column1 ISelectItem

Column from the first table.

fromItem2 IFromItem

The second (right) table or join.

column2 ISelectItem

Column from the second table.

joinType JoinType

The type of join to perform. Defaults to Inner.

joinOperatorType JoinOperatorType

Examples

// Range join: JOIN prices ON orders.order_date >= prices.effective_date
var join = new Join(
    ordersTable, ordersTable["order_date"],
    pricesTable, pricesTable["effective_date"],
    JoinOperatorType.GreaterThanOrEqualTo
);
// SQL: FROM orders
//      INNER JOIN prices ON orders.order_date >= prices.effective_date

Join(IFromItem, string, ISelectItem, IFromItem, string, ISelectItem, JoinType, JoinOperatorType)

Creates a non-equi join with explicit aliases using column references and a comparison operator.

public Join(IFromItem fromItem1, string alias1, ISelectItem column1, IFromItem fromItem2, string alias2, ISelectItem column2, JoinType joinType = JoinType.Inner, JoinOperatorType joinOperatorType = JoinOperatorType.Equals)

Parameters

fromItem1 IFromItem

The first (left) table or join.

alias1 string

Alias for the first item.

column1 ISelectItem

Column from the first table.

fromItem2 IFromItem

The second (right) table or join.

alias2 string

Alias for the second item.

column2 ISelectItem

Column from the second table.

joinType JoinType

The type of join to perform. Defaults to Inner.

joinOperatorType JoinOperatorType

Examples

// Range join with aliases: JOIN prices p ON o.price > p.base_price
var join = new Join(
    ordersTable, "o", ordersTable["price"],
    pricesTable, "p", pricesTable["base_price"],
    JoinOperatorType.GreaterThan
);
// SQL: FROM orders o
//      INNER JOIN prices p ON o.price > p.base_price

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.

public Join(IFromItem fromItem1, string alias1, string columnName1, IFromItem fromItem2, string alias2, string columnName2, JoinType joinType = JoinType.Inner, JoinOperatorType joinOperatorType = JoinOperatorType.Equals)

Parameters

fromItem1 IFromItem

The first (left) table or join.

alias1 string

Alias for the first item.

columnName1 string

Column name on the first item.

fromItem2 IFromItem

The second (right) table or join.

alias2 string

Alias for the second item.

columnName2 string

Column name on the second item.

joinType JoinType

The type of join to perform. Defaults to Inner.

joinOperatorType JoinOperatorType

Examples

// LEFT OUTER JOIN with subquery - requires explicit aliases
Query subquery = Query.Create()
    .From(ordersTable)
    .Select(ordersTable["customer_id"])
    .Select(new Expression("order_count", new Aggregate(AggregateType.Count, new ArithmeticOperator(ArithmeticType.All))))
    .Where(new Criterion<string>(ordersTable["status"], ConditionalType.Equals, "completed"))
    .GroupBy(ordersTable["customer_id"]);

var join = new Join(
    customersTable, "c", "customer_id",
    subquery, "o", "customer_id",
    JoinOperatorType.Equals,
    JoinType.LeftOuter
);
// SQL: FROM customers c
//      LEFT JOIN (SELECT customer_id, COUNT(*) as order_count
//                 FROM orders WHERE status = 'completed'
//                 GROUP BY customer_id) o
//      ON c.customer_id = o.customer_id

// Non-equi join with different column names
var join = new Join(
    ordersTable, "o", "order_date",
    pricesTable, "p", "effective_date",
    JoinOperatorType.GreaterThanOrEqualTo
);
// SQL: FROM orders o
//      INNER JOIN prices p ON o.order_date >= p.effective_date