Creating a Select Helper for Easier SQL Joins in SqlClient
Made a select helper, in my limited testing so far been working nicely. Handles aliasing and then nesting column names which makes working with joins in raw sql a lot easier. The implementation is quite minimal (but a bit hacky rn to transform table names). Do you think that with some work on the API there'd be room for this on SqlClient? IMO fits in with the helpers that are already there
const receiptSelect = yield* select(Receipt.fields, 'receipt')
const lineItemSelect = yield* select(LineItem.fields, 'lineItem')
const variableLineItemSelect = yield* select(
VariableLineItem.fields,
'variableLineItem',
)
const depositSelect = yield* select(Deposit.fields, 'deposit')
const lineItemDiscountSelect = yield* select(
LineItemDiscount.fields,
'lineItemDiscount',
)
const getReceiptWithItems = SqlSchema.single({
Request: ReceiptId,
Result: Schema.Struct({
receipt: Receipt,
lineItem: Schema.NullOr(LineItem),
variableLineItem: Schema.NullOr(VariableLineItem),
deposit: Schema.NullOr(Deposit),
lineItemDiscount: Schema.NullOr(LineItemDiscount),
}),
execute: (request) =>
sql`
select
${receiptSelect.columns},
${lineItemSelect.columns},
${variableLineItemSelect.columns},
${depositSelect.columns},
${lineItemDiscountSelect.columns}
from
receipt
left join line_item on receipt.id = line_item.receipt_id
left join variable_line_item on receipt.id = variable_line_item.receipt_id
left join deposit on receipt.id = deposit.receipt_id
left join line_item_discount on line_item.discount_id = line_item_discount.id
where
receipt.id = ${request}
`.pipe(
Effect.map(receiptSelect.nestByPrefix),
Effect.map(lineItemSelect.nestByPrefix),
Effect.map(variableLineItemSelect.nestByPrefix),
Effect.map(depositSelect.nestByPrefix),
Effect.map(lineItemDiscountSelect.nestByPrefix),
),
}) const receiptSelect = yield* select(Receipt.fields, 'receipt')
const lineItemSelect = yield* select(LineItem.fields, 'lineItem')
const variableLineItemSelect = yield* select(
VariableLineItem.fields,
'variableLineItem',
)
const depositSelect = yield* select(Deposit.fields, 'deposit')
const lineItemDiscountSelect = yield* select(
LineItemDiscount.fields,
'lineItemDiscount',
)
const getReceiptWithItems = SqlSchema.single({
Request: ReceiptId,
Result: Schema.Struct({
receipt: Receipt,
lineItem: Schema.NullOr(LineItem),
variableLineItem: Schema.NullOr(VariableLineItem),
deposit: Schema.NullOr(Deposit),
lineItemDiscount: Schema.NullOr(LineItemDiscount),
}),
execute: (request) =>
sql`
select
${receiptSelect.columns},
${lineItemSelect.columns},
${variableLineItemSelect.columns},
${depositSelect.columns},
${lineItemDiscountSelect.columns}
from
receipt
left join line_item on receipt.id = line_item.receipt_id
left join variable_line_item on receipt.id = variable_line_item.receipt_id
left join deposit on receipt.id = deposit.receipt_id
left join line_item_discount on line_item.discount_id = line_item_discount.id
where
receipt.id = ${request}
`.pipe(
Effect.map(receiptSelect.nestByPrefix),
Effect.map(lineItemSelect.nestByPrefix),
Effect.map(variableLineItemSelect.nestByPrefix),
Effect.map(depositSelect.nestByPrefix),
Effect.map(lineItemDiscountSelect.nestByPrefix),
),
})
