Trying to create a query - with left join limit 1 and order by

i want to search for all the cards with the last activity (one, ordered by createdBy) this is the naive implementation

import { db } from '~/server/db/neon.db';
import { and, eq, sql } from 'drizzle-orm';

const lastActivityAlias = alias(activitiesTable, 'lastActivity');
const dbQuery = db
  .select({
    ...select,
    ...(showLastActivity ? { lastActivity: lastActivityAlias } : {}),
  })
  .from(cardsTable)
  .where(query.where)
  .limit(query.limit)
  .offset(query.offset)
  .orderBy(...(query.orderBy ?? []));
if (showLastActivity) {
  void dbQuery.leftJoin(
    db
      .select()
      .from(lastActivityAlias)
      .where(eq(lastActivityAlias.cardId, cardsTable.id))
      .orderBy(asc(lastActivityAlias.createdAt))
      .limit(1)
      .as('lastActivity'),
    eq(lastActivityAlias.cardId, cardsTable.id),
  );
}

import { db } from '~/server/db/neon.db';
import { and, eq, sql } from 'drizzle-orm';

const lastActivityAlias = alias(activitiesTable, 'lastActivity');
const dbQuery = db
  .select({
    ...select,
    ...(showLastActivity ? { lastActivity: lastActivityAlias } : {}),
  })
  .from(cardsTable)
  .where(query.where)
  .limit(query.limit)
  .offset(query.offset)
  .orderBy(...(query.orderBy ?? []));
if (showLastActivity) {
  void dbQuery.leftJoin(
    db
      .select()
      .from(lastActivityAlias)
      .where(eq(lastActivityAlias.cardId, cardsTable.id))
      .orderBy(asc(lastActivityAlias.createdAt))
      .limit(1)
      .as('lastActivity'),
    eq(lastActivityAlias.cardId, cardsTable.id),
  );
}

the issue is with ".where", as im getting this error error: invalid reference to FROM-clause entry for table "Cards" i can overcome this with findMany + with but i need another many to many join there that is not supported by the query mechanism i would love for some help pls 🙂

1 Reply

alonmizOP•7mo ago

i think it can be achieved with lateral join, is this supported? or is there any workaround? pretty please 🙏

Gaming

Programming

Trying to create a query - with left join limit 1 and order by