Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

Add outer and cross apply (mssql) #1074

Open
wants to merge 8 commits into
base: master
Choose a base branch
from
Open

Add outer and cross apply (mssql) #1074

Show file tree
Hide file tree
Changes from all commits
Commits
Show all changes
8 commits
Select commit Hold shift click to select a range
a4d9146
add outer apply to table expression
Jul 7, 2024
7bdb790
add cross apply to table expression
Jul 8, 2024
0195d5e
run query in tests
Jul 8, 2024
282399e
test typings
Jul 8, 2024
112ec82
move test
Jul 8, 2024
feb3cfc
widen types to allow apply with table
Jul 8, 2024
409e21d
clean up rogue import
Jul 8, 2024
e2a3c29
clean up imports
Jul 8, 2024
File filter

Filter by extension

Filter by extension
Conversations
Failed to load comments.
Loading
Jump to
Jump to file
Failed to load files.
Loading
Diff view
Diff view
2 changes: 2 additions & 0 deletions src/operation-node/join-node.ts
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -10,6 10,8 @@ export type JoinType =
| 'LateralInnerJoin'
| 'LateralLeftJoin'
| 'Using'
| 'OuterApply'
| 'CrossApply'

export interface JoinNode extends OperationNode {
readonly kind: 'JoinNode'
Expand Down
16 changes: 11 additions & 5 deletions src/parser/join-parser.ts
View file
Open in desktop
Original file line number Diff line number Diff line change
@@ -1,18 1,18 @@
import { JoinNode, JoinType } from '../operation-node/join-node.js'
import { JoinBuilder } from '../query-builder/join-builder.js'
import {
AnyColumn,
AnyColumnWithTable,
DrainOuterGeneric,
} from '../util/type-utils.js'
import { parseReferentialBinaryOperation } from './binary-operation-parser.js'
import { createJoinBuilder } from './parse-utils.js'
import {
TableExpression,
parseTableExpression,
From,
FromTables,
TableExpression,
parseTableExpression,
} from './table-parser.js'
import { parseReferentialBinaryOperation } from './binary-operation-parser.js'
import { JoinBuilder } from '../query-builder/join-builder.js'
import { createJoinBuilder } from './parse-utils.js'

export type JoinReferenceExpression<
DB,
Expand Down Expand Up @@ -41,6 41,8 @@ export function parseJoin(joinType: JoinType, args: any[]): JoinNode {
return parseSingleOnJoin(joinType, args[0], args[1], args[2])
} else if (args.length === 2) {
return parseCallbackJoin(joinType, args[0], args[1])
} else if (args.length === 1) {
return parseOnlessJoin(joinType, args[0])
} else {
throw new Error('not implemented')
}
Expand All @@ -66,3 68,7 @@ function parseSingleOnJoin(
parseReferentialBinaryOperation(lhsColumn, '=', rhsColumn),
)
}

function parseOnlessJoin(joinType: JoinType, from: TableExpression<any, any>) {
return createJoinBuilder(joinType, from).toOperationNode()
}
96 changes: 62 additions & 34 deletions src/query-builder/select-query-builder.ts
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -845,6 845,14 @@ export interface SelectQueryBuilder<DB, TB extends keyof DB, O>
callback: FN,
): SelectQueryBuilderWithLeftJoin<DB, TB, O, TE>

outerApply<TE extends TableExpression<DB, TB>>(
table: TE,
): SelectQueryBuilderWithLeftJoin<DB, TB, O, TE>

crossApply<TE extends TableExpression<DB, TB>>(
table: TE,
): SelectQueryBuilderWithInnerJoin<DB, TB, O, TE>

/**
* Adds an `order by` clause to the query.
*
Expand Down Expand Up @@ -2118,6 2126,26 @@ class SelectQueryBuilderImpl<DB, TB extends keyof DB, O>
})
}

outerApply(table: any): any {
return new SelectQueryBuilderImpl({
...this.#props,
queryNode: QueryNode.cloneWithJoin(
this.#props.queryNode,
parseJoin('OuterApply', [table]),
),
})
}

crossApply(table: any): any {
return new SelectQueryBuilderImpl({
...this.#props,
queryNode: QueryNode.cloneWithJoin(
this.#props.queryNode,
parseJoin('CrossApply', [table]),
),
})
}

orderBy(...args: any[]): SelectQueryBuilder<DB, TB, O> {
return new SelectQueryBuilderImpl({
...this.#props,
Expand Down Expand Up @@ -2504,12 2532,12 @@ export type SelectQueryBuilderWithInnerJoin<
? InnerJoinedBuilder<DB, TB, O, A, DB[T]>
: never
: TE extends keyof DB
? SelectQueryBuilder<DB, TB | TE, O>
: TE extends AliasedExpression<infer QO, infer QA>
? InnerJoinedBuilder<DB, TB, O, QA, QO>
: TE extends (qb: any) => AliasedExpression<infer QO, infer QA>
? InnerJoinedBuilder<DB, TB, O, QA, QO>
: never
? SelectQueryBuilder<DB, TB | TE, O>
: TE extends AliasedExpression<infer QO, infer QA>
? InnerJoinedBuilder<DB, TB, O, QA, QO>
: TE extends (qb: any) => AliasedExpression<infer QO, infer QA>
? InnerJoinedBuilder<DB, TB, O, QA, QO>
: never

type InnerJoinedBuilder<
DB,
Expand All @@ -2536,12 2564,12 @@ export type SelectQueryBuilderWithLeftJoin<
? LeftJoinedBuilder<DB, TB, O, A, DB[T]>
: never
: TE extends keyof DB
? LeftJoinedBuilder<DB, TB, O, TE, DB[TE]>
: TE extends AliasedExpression<infer QO, infer QA>
? LeftJoinedBuilder<DB, TB, O, QA, QO>
: TE extends (qb: any) => AliasedExpression<infer QO, infer QA>
? LeftJoinedBuilder<DB, TB, O, QA, QO>
: never
? LeftJoinedBuilder<DB, TB, O, TE, DB[TE]>
: TE extends AliasedExpression<infer QO, infer QA>
? LeftJoinedBuilder<DB, TB, O, QA, QO>
: TE extends (qb: any) => AliasedExpression<infer QO, infer QA>
? LeftJoinedBuilder<DB, TB, O, QA, QO>
: never

type LeftJoinedBuilder<
DB,
Expand All @@ -2558,8 2586,8 @@ type LeftJoinedDB<DB, A extends keyof any, R> = DrainOuterGeneric<{
[C in keyof DB | A]: C extends A
? Nullable<R>
: C extends keyof DB
? DB[C]
: never
? DB[C]
: never
}>

export type SelectQueryBuilderWithRightJoin<
Expand All @@ -2572,12 2600,12 @@ export type SelectQueryBuilderWithRightJoin<
? RightJoinedBuilder<DB, TB, O, A, DB[T]>
: never
: TE extends keyof DB
? RightJoinedBuilder<DB, TB, O, TE, DB[TE]>
: TE extends AliasedExpression<infer QO, infer QA>
? RightJoinedBuilder<DB, TB, O, QA, QO>
: TE extends (qb: any) => AliasedExpression<infer QO, infer QA>
? RightJoinedBuilder<DB, TB, O, QA, QO>
: never
? RightJoinedBuilder<DB, TB, O, TE, DB[TE]>
: TE extends AliasedExpression<infer QO, infer QA>
? RightJoinedBuilder<DB, TB, O, QA, QO>
: TE extends (qb: any) => AliasedExpression<infer QO, infer QA>
? RightJoinedBuilder<DB, TB, O, QA, QO>
: never

type RightJoinedBuilder<
DB,
Expand All @@ -2596,10 2624,10 @@ type RightJoinedDB<
[C in keyof DB | A]: C extends A
? R
: C extends TB
? Nullable<DB[C]>
: C extends keyof DB
? DB[C]
: never
? Nullable<DB[C]>
: C extends keyof DB
? DB[C]
: never
}>

export type SelectQueryBuilderWithFullJoin<
Expand All @@ -2612,12 2640,12 @@ export type SelectQueryBuilderWithFullJoin<
? OuterJoinedBuilder<DB, TB, O, A, DB[T]>
: never
: TE extends keyof DB
? OuterJoinedBuilder<DB, TB, O, TE, DB[TE]>
: TE extends AliasedExpression<infer QO, infer QA>
? OuterJoinedBuilder<DB, TB, O, QA, QO>
: TE extends (qb: any) => AliasedExpression<infer QO, infer QA>
? OuterJoinedBuilder<DB, TB, O, QA, QO>
: never
? OuterJoinedBuilder<DB, TB, O, TE, DB[TE]>
: TE extends AliasedExpression<infer QO, infer QA>
? OuterJoinedBuilder<DB, TB, O, QA, QO>
: TE extends (qb: any) => AliasedExpression<infer QO, infer QA>
? OuterJoinedBuilder<DB, TB, O, QA, QO>
: never

type OuterJoinedBuilder<
DB,
Expand All @@ -2636,10 2664,10 @@ type OuterJoinedBuilderDB<
[C in keyof DB | A]: C extends A
? Nullable<R>
: C extends TB
? Nullable<DB[C]>
: C extends keyof DB
? DB[C]
: never
? Nullable<DB[C]>
: C extends keyof DB
? DB[C]
: never
}>

type TableOrList<TB extends keyof any> =
Expand Down
2 changes: 2 additions & 0 deletions src/query-compiler/default-query-compiler.ts
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -1738,5 1738,7 @@ const JOIN_TYPE_SQL: Readonly<Record<JoinType, string>> = freeze({
FullJoin: 'full join',
LateralInnerJoin: 'inner join lateral',
LateralLeftJoin: 'left join lateral',
OuterApply: 'outer apply',
CrossApply: 'cross apply',
Using: 'using',
})
77 changes: 77 additions & 0 deletions test/node/src/join.test.ts
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -780,5 780,82 @@ for (const dialect of DIALECTS) {
})
})
}
if (dialect === 'mssql') {
describe('apply', () => {
it('should outer apply an expression', async () => {
const q = ctx.db
.selectFrom('person')
.outerApply((eb) =>
eb
.selectFrom('pet')
.whereRef('pet.owner_id', '=', 'person.id')
.select('pet.name')
.as('pets'),
)
.selectAll()
.orderBy('pets.name')

testSql(q, dialect, {
postgres: NOT_SUPPORTED,
mysql: NOT_SUPPORTED,
sqlite: NOT_SUPPORTED,
mssql: {
sql: `select * from "person" outer apply (select "pet"."name" from "pet" where "pet"."owner_id" = "person"."id") as "pets" order by "pets"."name"`,
parameters: [],
},
})

const result = await q.execute()

expect(result).to.have.length(3)

expect(result).to.containSubset([
{
first_name: 'Jennifer',
name: 'Catto',
},
{ first_name: 'Arnold', name: 'Doggo' },
{ first_name: 'Sylvester', name: 'Hammo' },
] satisfies Partial<(typeof result)[number]>[])
})

it('should cross apply an expression', async () => {
const q = ctx.db
.selectFrom('person')
.crossApply((eb) =>
eb
.selectFrom('pet')
.whereRef('pet.owner_id', '=', 'person.id')
.select('pet.name')
.as('pets'),
)
.selectAll()
.orderBy('pets.name')

testSql(q, dialect, {
postgres: NOT_SUPPORTED,
mysql: NOT_SUPPORTED,
sqlite: NOT_SUPPORTED,
mssql: {
sql: `select * from "person" cross apply (select "pet"."name" from "pet" where "pet"."owner_id" = "person"."id") as "pets" order by "pets"."name"`,
parameters: [],
},
})

const result = await q.execute()

expect(result).to.have.length(3)

expect(result).to.containSubset([
{
first_name: 'Jennifer',
name: 'Catto',
},
{ first_name: 'Arnold', name: 'Doggo' },
{ first_name: 'Sylvester', name: 'Hammo' },
] satisfies Partial<(typeof result)[number]>[])
})
})
}
})
}
40 changes: 40 additions & 0 deletions test/typings/test-d/join.test-d.ts
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -159,6 159,46 @@ async function testJoin(db: Kysely<Database>) {
.set({ last_name: 'Jennifer' })
.where('pet.id', '=', '1')

// Cross apply
const r9 = await db
.selectFrom('person')
.crossApply((eb) =>
eb
.selectFrom('pet')
.whereRef('pet.owner_id', '=', 'person.id')
.select('pet.name')
.as('pets'),
)
.select(['person.first_name', 'pets.name'])
.execute()

expectType<
{
first_name: string
name: string
}[]
>(r9)

// Outer apply
const r10 = await db
.selectFrom('person')
.outerApply((eb) =>
eb
.selectFrom('pet')
.whereRef('pet.owner_id', '=', 'person.id')
.select('pet.name')
.as('pets'),
)
.select(['person.first_name', 'pets.name'])
.execute()

expectType<
{
first_name: string
name: string | null
}[]
>(r10)

// Refer to table that's not joined
expectError(
db.selectFrom('person').innerJoin('movie', 'movie.id', 'pet.owner_id'),
Expand Down