Filtering by the result returned by subquery

[fredachang]

Hi there,

Thanks so much for a great library - been enjoying using it so far. Something I am running into that I haven't been able to figure out myself is how to insert a where condition on the result returned by a subquery helper.

We have a subquery that is doing a count, and when that is returned to the main query, I want to be able to filter on the results based on the count <= a number. This is doable in sql but not sure how to compose correctly in Kysely.

I have potshotted the following code in the playground and getting a reference error:

import { Species, Database } from "type-editor";

const species: Species = "hamster";

const filterByCount = true;

function withCount() {
  const eb = expressionBuilder<Database, "person">();

  return eb
    .selectFrom("pet")
    .whereRef("pet.owner_id", "=", "person.id")
    .select(({ fn }) =>
      fn.countAll<number>().as("count"),
    )
    .as("count");
}

const rows = await db
  .selectFrom("person")
  .select(withCount)
  .$if(!!filterByCount, (qb) =>
    qb.where((eb) => withCount(eb), "<", 3),
  )
  .execute();

if I include the subquery directly in the body of the where like this, it compiles and returns expected result.

  .selectFrom("person")
  .select(withCount)
  .$if(!!filterByCount, (qb) =>
    qb.where(
      (eb) =>
        eb
          .selectFrom("pet")
          .whereRef("pet.owner_id", "=", "person.id")
          .select(({ fn }) =>
            fn.countAll<number>().as("assignedDocumentsCount"),
          ),
      "<",
      3,
    ),
  )
  .execute();

We are using the withCount helper in more than one place, so would be great to make it reusable.

Any guidance or input would be much appreciated!

[koskimas]

You can't have an alias when you use the query in a where statement. Drop the .as('count') and add it outside the helper only in select.

import { Species, Database } from "type-editor";

const species: Species = "hamster";

const filterByCount = true;

function withCount() {
  const eb = expressionBuilder<Database, "person">();

  return eb
    .selectFrom("pet")
    .whereRef("pet.owner_id", "=", "person.id")
    .select(({ fn }) =>
      fn.countAll<number>().as("count"),
    )
}

const rows = await db
  .selectFrom("person")
  .select(withCount().as('count'))
  .$if(!!filterByCount, (qb) =>
    qb.where(withCount(), "<", 3),
  )
  .execute();

[koskimas]

By the way, you can share a link to the playground by saving (shift+cmd+s) or by clicking the More(F1) button and clicking save and shorten link.

[fredachang]

Thanks so much for a speedy reply. Implemented the suggested changes and got it to work how we wanted it. Good to know about the aliases - since we have quite a few helpers implemented this way.

[koskimas]

The alias is just an SQL alias. It literally adds AS "theaAlias" at the end of the expression. That's an SQL thing, not a Kysely limitation.