multiple join

[neuecc]

JOIN clause https://developers.google.com/bigquery/query-reference#joins

[[INNER|LEFT OUTER|CROSS] JOIN [EACH] table_2|(subselect2) [[AS] tablealias2]
    ON join_condition_1 [... AND join_condition_N ...]]+

BigQuery supports multiple JOIN operations.

• CROSS JOIN clauses must not contain an ON clause.
• The EACH modifier can't be used in CROSS JOIN clauses

Method Signature?

enum JoinType
{
    Inner,
    InnerEach
    LeftOuter,
    LeftOuterEach
}

Join(rightSource, mergeCondition, aliasSelector) // JoinType.Inner
Join(joinType, rightSource, mergeCondition, aliasSelector)
JoinCross(rightSource, aliasSelector) // CROSS JOIN must not contains mergeCondition

.From()
.Join(ys, (x, y) => x.hoge == y.hoge, (x, y) => new { wiki = x, goog = y })
.Join(zs, (x, y) => x.wiki.hoge == y.hoge, (x, y) => new { x.wiki, x.goog, huga = y })
.GroupBy...

// IEnumerable's JOIN
public static IEnumerable<TResult> Join<TOuter, TInner, TKey, TResult>(this IEnumerable<TOuter> outer, IEnumerable<TInner> inner, Func<TOuter, TKey> outerKeySelector, Func<TInner, TKey> innerKeySelector, Func<TOuter, TInner, TResult> resultSelector);

// BigQuery?
public static IBigQueryable<TResult> Join<TOuter, TInner, TResult>(this IBigQueryable<TOuter> outer, IBigQueryable<TInner> inner, Func<TOuter, TInner, bool> mergeCondition, Func<TOuter, TInner, TResult> aliasSelector);

[neuecc]

sample query

select tbl1.repository.url, tbl2.repository_url
from [publicdata:samples.github_nested] as tbl1
join (select * from [publicdata:samples.github_timeline] limit 1000) as tbl2 on tbl1.repository.url = tbl2.repository_url
limit 1

// Pattern1
.From(repositoryA)
.Join(repositoryB, (x, y) => x.repository.url == y.repository_url, (x, y) => new { tbl1 = x, tbl2 = y})
.Limit(1)
.Select(x => new { x.tbl1.repository.url, x.tbl2.repository_url })

// Pattern2 (aliasSelector first!)

.From(repositoryA)
.Join(repositoryB, (tbl1, tbl2) => new { tbl1, tbl2},  x => x.tbl1.repository.url == x.tbl2.repository_url)
.Limit(1)
.Select(x => new { x.tbl1.repository.url, x.tbl2.repository_url })

[neuecc]

// Utility?

// JoinUtil.On(bool mergeCondition, TResult aliasSelector)
.Join(repositoryB, (tbl1, tbl2) => JoinUtil.On(tbl1.repository.url == tbl2.repository_url, new { tbl1, tbl2 })

[neuecc]

// Chain Style? ... bad. Join(repositoryB, aliasSelector).ON(condition)

// more arg ... better Join(repositoryB, aliasSelector, condition)