Is there a way to execute an ExpressionBuilder?

Aautobotkilla

5/6/2023

Using the expression hasDogNamed example from the docs

const eb = expressionBuilder<DB, 'person'>()

const query = eb.selectFrom('pet')
  .select('pet.id')
  .whereRef('pet.owner_id', '=', 'person.id')
  .where('pet.species', '=', 'dog')
  .where('pet.name', '=', name)

const result = await query.execute(); <--- This compiles, but doesn't work...

Is there a way I can provide an ExpressionBuilder an instance of Kysely<DB> so that it can execute the query?

Aautobotkilla

5/7/2023

Figured it out. The function createExpressionBuilder accepts an optional QueryExecutor

export function createExpressionBuilder<DB, TB extends keyof DB>(
  executor: QueryExecutor = NOOP_QUERY_EXECUTOR
): ExpressionBuilder<DB, TB

You can get a QueryExecutor from your Kysely<DB> instance.

const eb = createExpressionBuilder<DB, 'User'>(db.getExecutor());

IIgal

5/7/2023

Hey 👋

It's not really meant to be executed, I'd strongly advice you NOT to do this.

Aautobotkilla

5/7/2023

Hmmm. I'm trying to create reusable select queries so that the same shape can be returned in different circumstances. Do you have an alternative suggestion?

Here's an example.

export function selectJob(db?: DittoDb) {
    const eb = createExpressionBuilder<DB, 'Job'>(db?.getExecutor());

    // prettier-ignore
    return eb
        .selectFrom('Job')
        .select([
            'Job.id', 
            'Job.status',
            'Job.title',
            withUserInfo('Job.ownerId').as('owner'),
            withUserInfo('Job.managerId').as('manager'),
        ])
}

export function withJob(jobIdColumn: any) {
    return jsonObjectFrom(selectJob().whereRef('Job.id', '=', jobIdColumn));
}

export function getJobById(id: string) {
    return selectJob(db).where('Job.id', '=', id).execute();
}

This way I can get the same Job object when I select a job directly or include a Job in another query, like getting a JobApplication.

I'm looking for a way to define reusable shapes.

IIgal

5/7/2023

function selectJob(db: Kysely<DB>) {
  return db.selectFrom("Job").select(["Job.id", "Job.status", "Job.title"])
}

const rows = await selectJob(db).execute()

const rows2 = await db
  .selectFrom(["User", selectJob(db).as("Job")])
  .selectAll()
  .execute()

https://kyse.link/?p=s&i=zwh71z75oksUdbqXk3gh

Aautobotkilla

5/7/2023

oh. That's pretty simple. Thanks!!