Drizzle Team•2mo ago

limiting a join when filtering by an extra field.

I have a query which finds properties based on how far away they are from a certain point and then joins some other tables.

 const sq = db
    .select({
      ...getTableColumns(placeDetailsTable),
      distance: sql<number>`.....`.as("distance"),
    })
    .from(placeDetailsTable)
    .as("subquery");

  const query = await db
    .select()
    .from(sq)
    .where(
      and(lt(sq.distance, maxDistanceInKm), eq(postingsTable.isActive, true)),
    )
    .orderBy(sq.distance)
    .innerJoin(propertiesTable, eq(sq.id, propertiesTable.placeId))
    .innerJoin(
      postingsTable,
      eq(propertiesTable.publicFacingId, postingsTable.propertyPublicId),
    );

 const sq = db
    .select({
      ...getTableColumns(placeDetailsTable),
      distance: sql<number>`.....`.as("distance"),
    })
    .from(placeDetailsTable)
    .as("subquery");

  const query = await db
    .select()
    .from(sq)
    .where(
      and(lt(sq.distance, maxDistanceInKm), eq(postingsTable.isActive, true)),
    )
    .orderBy(sq.distance)
    .innerJoin(propertiesTable, eq(sq.id, propertiesTable.placeId))
    .innerJoin(
      postingsTable,
      eq(propertiesTable.publicFacingId, postingsTable.propertyPublicId),
    );

I would also like to join a propertyImages table but I only want to bring the first 5 of them. I know that this is beautifully easy using relational queries because I can do

{ with: { images: { limit: 5 } } }

{ with: { images: { limit: 5 } } }

However, I am not sure if this filtering based on a calculated field is possible. extras is there but then I can't include that in the where clause of the relational query. Maybe I should not join the images table and do a subquery there?

1 Reply

titongo•2mo ago

basically, turning this from the drizzle docs into a relational query: https://orm.drizzle.team/docs/select#select-from-subquery Select from subquery Just like in SQL, you can embed queries into other queries by using the subquery API:

const sq = db.select().from(users).where(eq(users.id, 42)).as('sq');
const result = await db.select().from(sq);
select "id", "name", "age" from (select "id", "name", "age" from "users" where "id" = 42) "sq";

const sq = db.select().from(users).where(eq(users.id, 42)).as('sq');
const result = await db.select().from(sq);
select "id", "name", "age" from (select "id", "name", "age" from "users" where "id" = 42) "sq";

Subqueries can be used in any place where a table can be used, for example in joins:

const sq = db.select().from(users).where(eq(users.id, 42)).as('sq');
const result = await db.select().from(users).leftJoin(sq, eq(users.id, sq.id));
select "users"."id", "users"."name", "users"."age", "sq"."id", "sq"."name", "sq"."age" from "users"
  left join (select "id", "name", "age" from "users" where "id" = 42) "sq"
    on "users"."id" = "sq"."id";

const sq = db.select().from(users).where(eq(users.id, 42)).as('sq');
const result = await db.select().from(users).leftJoin(sq, eq(users.id, sq.id));
select "users"."id", "users"."name", "users"."age", "sq"."id", "sq"."name", "sq"."age" from "users"
  left join (select "id", "name", "age" from "users" where "id" = 42) "sq"
    on "users"."id" = "sq"."id";

Gaming

Programming

limiting a join when filtering by an extra field.