DT
Drizzle Team•2mo ago
John

Best practices for complex queries

Forgive my lack of depth as I've been primarily a front-end dev, but I'm having issues getting complex queries to work with TypeScript. This is an example of one of my queries: 
export function listOlympiadsQuery(db: GDDB) {
  return db
    .select({
      id: OlympiadTable.id,
      slug: OlympiadTable.slug,
      year: OlympiadTable.year,
      startDate: OlympiadTable.startDate,
      endDate: OlympiadTable.endDate,
      olympiadType: OlympiadTable.olympiadType,
      realOlympiad: OlympiadTable.realOlympiad,
      city: {
        id: CityTable.id,
        name: CityTable.name,
        slug: CityTable.slug,
        country: {
          id: CountryTable.id,
          name: CountryTable.name,
          slug: CountryTable.slug,
          flag: {
            id: FlagTable.id,
            png: FlagTable.png
          }
        }
      }
    })
    .from(OlympiadTable)
    .innerJoin(CityTable, eq(OlympiadTable.cityId, CityTable.id))
    .innerJoin(
      OlympiadCountryTable,
      and(
        eq(OlympiadCountryTable.olympiadId, OlympiadTable.id),
        eq(OlympiadCountryTable.countryId, CityTable.countryId)
      )
    )
    .innerJoin(CountryTable, eq(OlympiadCountryTable.countryId, CountryTable.id))
    .leftJoin(FlagTable, eq(OlympiadCountryTable.flagId, FlagTable.id))
    .where(eq(OlympiadTable.realOlympiad, true))
    .orderBy(desc(OlympiadTable.startDate))
    .prepare('listOlympiadsQuery');
}

const query = listOlympiadsQuery(db);
const result = await query.execute();
export function listOlympiadsQuery(db: GDDB) {
  return db
    .select({
      id: OlympiadTable.id,
      slug: OlympiadTable.slug,
      year: OlympiadTable.year,
      startDate: OlympiadTable.startDate,
      endDate: OlympiadTable.endDate,
      olympiadType: OlympiadTable.olympiadType,
      realOlympiad: OlympiadTable.realOlympiad,
      city: {
        id: CityTable.id,
        name: CityTable.name,
        slug: CityTable.slug,
        country: {
          id: CountryTable.id,
          name: CountryTable.name,
          slug: CountryTable.slug,
          flag: {
            id: FlagTable.id,
            png: FlagTable.png
          }
        }
      }
    })
    .from(OlympiadTable)
    .innerJoin(CityTable, eq(OlympiadTable.cityId, CityTable.id))
    .innerJoin(
      OlympiadCountryTable,
      and(
        eq(OlympiadCountryTable.olympiadId, OlympiadTable.id),
        eq(OlympiadCountryTable.countryId, CityTable.countryId)
      )
    )
    .innerJoin(CountryTable, eq(OlympiadCountryTable.countryId, CountryTable.id))
    .leftJoin(FlagTable, eq(OlympiadCountryTable.flagId, FlagTable.id))
    .where(eq(OlympiadTable.realOlympiad, true))
    .orderBy(desc(OlympiadTable.startDate))
    .prepare('listOlympiadsQuery');
}

const query = listOlympiadsQuery(db);
const result = await query.execute();
1. This does not get automatic TypeScript parsing because it's too deeply nested. Is there something I can do about that? 2. Is this even the correct way to think about this? Should these be multiple queries? Is there a way to separate out all the queries without harming performance querying the database so many times? Any and all help is appreciated, even if it's just pointing me in the right direction of additional information.
0 Replies
No replies yetBe the first to reply to this messageJoin

Did you find this page helpful?