John Posts - Answer Overflow

John

•Created by John on 3/16/2025 in #help

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.

1 replies

DTDrizzle Team

•Created by John on 5/14/2024 in #help

Nested SQL select returning [x: string]: unknown type

This may also be delving into SQL-specific questions, but I have a complex query where I'm joining ten tables and at points, the select object is nested three deep. (I'm fairly intermediate to SQL so if I'm thinking about this wrong, please tell me). I can get the query pretty close using the query builder syntax but I have to do some specific where/orderby selects that throw errors for some reason. No problem, I rewrite it in the db.select() syntax and it is now correctly returning data. However, the return type is just: { [x: string]: unknown; }[] Which is obviously useless for trying to use that data in my application. Not sure what I'm doing wrong here, so any help is appreciated!

2 replies

Gaming

Programming