1 reply

Nested records using select()

drizzle ormSupabasepostgres-js

Apologies if this has been asked but I can't find it.

Is there an easier way to get nested records (one-to-many) using the query builder approach versus with? Currently can't use with because of the usersInAuth supabase introspect issue. There's a workaround for that but I also just like the flexibility of query builder. I've been able to make this work somewhat but it's a little verbose and hard to get the types 100% right.

I assume 1.0 might address some of these things but just want to make sure I'm not over-engineering or missing something.

const specialtiesAggregation = db
  .select({
    expert_id: expert_specialties.expert_id,
     specialties: sql<{ id: number }[]>`
      coalesce(
        json_agg(
          json_build_object(
            'id', ${expert_specialties.specialty_id}
          )
          ORDER BY ${expert_specialties.specialty_id}
        )
        FILTER (WHERE ${expert_specialties.specialty_id} IS NOT NULL),
        '[]'::json
      )
    `.as('specialties')
    })
    .from(expert_specialties)
    .groupBy(expert_specialties.expert_id)
    .as('specialties_agg')

    const data = await db
    .select({
        id: experts.id,
        slug: experts.slug,
        photo_url: experts.photo_url,
            user: {
                display_name: users.display_name,
                photo_url: users.photo_url
            },
            expert_specialties: specialtiesAggregation.specialties
        })
        .from(experts)
        .leftJoin(users, eq(experts.user_id, users.id))
        .leftJoin(expert_work_experience, eq(expert_work_experience.expert_id, experts.id))
        .leftJoin(specialtiesAggregation, eq(specialtiesAggregation.expert_id, experts.id))
        .where(id ? eq(experts.id, id) : eq(experts.slug, slug))
        .limit(1)

const specialtiesAggregation = db
  .select({
    expert_id: expert_specialties.expert_id,
     specialties: sql<{ id: number }[]>`
      coalesce(
        json_agg(
          json_build_object(
            'id', ${expert_specialties.specialty_id}
          )
          ORDER BY ${expert_specialties.specialty_id}
        )
        FILTER (WHERE ${expert_specialties.specialty_id} IS NOT NULL),
        '[]'::json
      )
    `.as('specialties')
    })
    .from(expert_specialties)
    .groupBy(expert_specialties.expert_id)
    .as('specialties_agg')

    const data = await db
    .select({
        id: experts.id,
        slug: experts.slug,
        photo_url: experts.photo_url,
            user: {
                display_name: users.display_name,
                photo_url: users.photo_url
            },
            expert_specialties: specialtiesAggregation.specialties
        })
        .from(experts)
        .leftJoin(users, eq(experts.user_id, users.id))
        .leftJoin(expert_work_experience, eq(expert_work_experience.expert_id, experts.id))
        .leftJoin(specialtiesAggregation, eq(specialtiesAggregation.expert_id, experts.id))
        .where(id ? eq(experts.id, id) : eq(experts.slug, slug))
        .limit(1)

Nested records using select()

drizzle ormSupabasepostgres-js

const specialtiesAggregation = db
  .select({
    expert_id: expert_specialties.expert_id,
     specialties: sql<{ id: number }[]>`
      coalesce(
        json_agg(
          json_build_object(
            'id', ${expert_specialties.specialty_id}
          )
          ORDER BY ${expert_specialties.specialty_id}
        )
        FILTER (WHERE ${expert_specialties.specialty_id} IS NOT NULL),
        '[]'::json
      )
    `.as('specialties')
    })
    .from(expert_specialties)
    .groupBy(expert_specialties.expert_id)
    .as('specialties_agg')

    const data = await db
    .select({
        id: experts.id,
        slug: experts.slug,
        photo_url: experts.photo_url,
            user: {
                display_name: users.display_name,
                photo_url: users.photo_url
            },
            expert_specialties: specialtiesAggregation.specialties
        })
        .from(experts)
        .leftJoin(users, eq(experts.user_id, users.id))
        .leftJoin(expert_work_experience, eq(expert_work_experience.expert_id, experts.id))
        .leftJoin(specialtiesAggregation, eq(specialtiesAggregation.expert_id, experts.id))
        .where(id ? eq(experts.id, id) : eq(experts.slug, slug))
        .limit(1)

const specialtiesAggregation = db
  .select({
    expert_id: expert_specialties.expert_id,
     specialties: sql<{ id: number }[]>`
      coalesce(
        json_agg(
          json_build_object(
            'id', ${expert_specialties.specialty_id}
          )
          ORDER BY ${expert_specialties.specialty_id}
        )
        FILTER (WHERE ${expert_specialties.specialty_id} IS NOT NULL),
        '[]'::json
      )
    `.as('specialties')
    })
    .from(expert_specialties)
    .groupBy(expert_specialties.expert_id)
    .as('specialties_agg')

    const data = await db
    .select({
        id: experts.id,
        slug: experts.slug,
        photo_url: experts.photo_url,
            user: {
                display_name: users.display_name,
                photo_url: users.photo_url
            },
            expert_specialties: specialtiesAggregation.specialties
        })
        .from(experts)
        .leftJoin(users, eq(experts.user_id, users.id))
        .leftJoin(expert_work_experience, eq(expert_work_experience.expert_id, experts.id))
        .leftJoin(specialtiesAggregation, eq(specialtiesAggregation.expert_id, experts.id))
        .where(id ? eq(experts.id, id) : eq(experts.slug, slug))
        .limit(1)

Nested records using select()

Nested records using select()

Similar Threads

Similar Threads

Similar Threads