K
Kysely•2y ago
autobotkilla

Is there a way to execute an ExpressionBuilder?

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...
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?
5 Replies
autobotkilla
autobotkillaOP•2y ago
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
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());
const eb = createExpressionBuilder<DB, 'User'>(db.getExecutor());
Igal
Igal•2y ago
Hey 👋 It's not really meant to be executed, I'd strongly advice you NOT to do this.
autobotkilla
autobotkillaOP•2y ago
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();
}
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.
Igal
Igal•2y ago
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()
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
autobotkilla
autobotkillaOP•2y ago
oh. That's pretty simple. Thanks!!

Did you find this page helpful?