How to do this better?

after.ts

export const list = async (query: Query, db: DB) => db
  // Subquery to get the main things we are going to
  // join against. If we don't do this, our JOIN
  // throws off our limit/offset query
  .from((builder: DB) => builder.from('recipes')
    .select('*')
    .limit(query.limit)
    .offset(query.offset)
    .orderBy(query.sort_col, query.sort_ord)
    .where(query.where || {})
    .as('recipes')
  )
  // Then our joins
  .leftJoin('recipe_tags', 'recipe_tags.recipe_id', '=', 'recipes.id')
  .leftJoin('recipe_steps', 'recipe_steps.recipe_id', '=', 'recipes.id')
  .join('recipe_ingredients', 'recipe_ingredients.recipe_id', '=', 'recipes.id')
  .select(
    // Select and rename things from our base tablke
    'recipes.id as id',
    'recipes.title as title',
    'recipes.created_at as created_at',
    'recipes.creator_id as creator_id',
    // Create arrays from the sub tables
    db.raw(`ARRAY_AGG(DISTINCT recipe_tags.tag) as tags`),
    // PSQL doesn't like mixing arrays so you will have to cast them
    // to be what you want
    db.raw(`ARRAY_AGG(DISTINCT ARRAY[recipe_steps.step, recipe_steps.time, CAST(recipe_steps.order AS text)]) as steps`),
    db.raw(`ARRAY_AGG(DISTINCT ARRAY[recipe_ingredients.name, recipe_ingredients.amount, recipe_ingredients.measurement]) as ingredients`)
  ).groupBy('id', 'title', 'created_at', 'creator_id')
  .orderBy(query.sort_col, query.sort_ord)
  .then((result: ArrayedResult[]) =>
    // A single map over the resulting table to transform
    // the list of lists into a list of objects
    // There is probably some ARRAY TO JSON thing that
    // we could do but the query is getting weird as-is
    result.map(({ steps, ingredients, ...rest }: ArrayedResult): any => ({
      ...rest,
      // I _think_ the steps are sorted anyways
      // but I want to make sure
      steps: steps.sort((a, b) => {
        if(a[2] > b[2]) {
          return 1
        }

        if (b[2] > a[2]) {
          return -1
        }

        return 0
      }).map(([step, time,  order]) => ({ step, time, order: Number(order) })),
      ingredients: ingredients.map(([name, amount, measurement]) => ({ name, amount, measurement }))
    }))
  )

all-in-one.ts

// All in one single SQL query!
export const list = async (query: Query, db: DB) => db
  .from((builder: DB) => builder.from('recipes')
    .select('*')
    .limit(query.limit)
    .offset(query.offset)
    .orderBy(query.sort_col, query.sort_ord)
    .where(query.where || {})
    .as('recipes')
  )
  .join('recipe_tags', 'recipe_tags.recipe_id', '=', 'recipes.id')
  .join('recipe_steps', 'recipe_steps.recipe_id', '=', 'recipes.id')
  .join('recipe_ingredients', 'recipe_ingredients.recipe_id', '=', 'recipes.id')
  .select(
    'recipes.id as id',
    'recipes.title as title',
    'recipes.created_at as created_at',
    'recipes.creator_id as creator_id',
    db.raw(`ARRAY_AGG(DISTINCT recipe_tags.tag) as tags`),
    db.raw(`JSON_AGG(DISTINCT
        JSONB_BUILD_OBJECT(
          'step', recipe_steps.step,
          'order', recipe_steps.order,
          'time', COALESCE(recipe_steps.time, 'UNKNOWN'::text)
        )
      ) as steps`),
    db.raw(`JSON_AGG(DISTINCT
        JSONB_BUILD_OBJECT(
          'name', recipe_ingredients.name,
          'amount', recipe_ingredients.amount,
          'measurement', COALESCE(recipe_ingredients.measurement, 'UNKNOWN'::text)
        )
    ) as ingredients`)
  ).groupBy('id', 'title', 'created_at', 'creator_id')
  .orderBy(query.sort_col, query.sort_ord)

example.ts

/**
 * Is there a way to do this in a single SQL query?
 */
export const list = async (query: Query, db: Knex) => {
  const recipes = await db.from('recipes')
    .select('*')
    .limit(query.limit)
    .offset(query.offset)
    .orderBy(query.sort_col, query.sort_ord)
  
  return Promise.all(recipes.map(async ({ id, ...rest }) => ({
    ...rest,
    id,
    steps: await db.from('recipe_steps').select(['step', 'time']).where({ recipe_id: id }).orderBy('order', 'ASC'),
    ingredients: await db.from('recipe_ingredients').select(['name', 'amount', 'measurement']).where({ recipe_id: id }),
    tags: await db.from('recipe_tags').select(['tag']).where({ recipe_id: id }).then(rows => rows.map(({ tag }) => tag))
  })))
}

join-with-helpers.ts

interface JoinedRecipe {
  id: string,
  title: string,
  tag: string,
  step_order: number,
  step_text: string,
  step_time: string,
  ingredient_name: string,
  ingredient_amount: string,
  ingredient_measurement: string,
  created_at: string
}

interface FullRecipe {
  id: string
  title: string,
  tags: Set<string>,
  steps: Map<number, { text: string, time: string }> ,
  ingredients: Map<string, { amount: string, measurement: string}>
  created_at: string
}

const group_joined_recipe = (recipe: JoinedRecipe): FullRecipe => {
  const full_recipe = {
    id: recipe.id,
    title: recipe.title,
    created_at: recipe.created_at,
    tags: new Set(),
    steps: new Map(),
    ingredients: new Map()
  }

  return full_recipe as FullRecipe
}

export const list = async (query: Query, db: DB) => db.from((builder: DB) => builder
    .from('recipes')
    .select('*')
    .limit(query.limit)
    .offset(query.offset)
    .orderBy(query.sort_col, query.sort_ord)
    .as('recipes')
    .where(query.where ?? {})
  )
  .select(
    'recipes.*',
    'recipe_tags.tag as tag',
    'recipe_steps.time as step_time',
    'recipe_steps.order as step_order',
    'recipe_steps.step as step_text',
    'recipe_ingredients.name as ingredient_name',
    'recipe_ingredients.amount as ingredient_amount',
    'recipe_ingredients.measurement as ingredient_measurement'
  )
  .join('recipe_tags', 'recipe_tags.recipe_id', '=', 'recipes.id')
  .join('recipe_steps', 'recipe_steps.recipe_id', '=', 'recipes.id')
  .join('recipe_ingredients', 'recipe_ingredients.recipe_id', '=', 'recipes.id')
  .orderBy(`recipes.${query.sort_col}`, query.sort_ord)
  .then((results: JoinedRecipe[]) => Object.values(results.reduce((a, recipe) => {    
    if (!(recipe.id in a)) {
      a[recipe.id] = group_joined_recipe(recipe)
    }
    
    if(recipe.tag) {
      a[recipe.id].tags.add(recipe.tag)
    }

    if (typeof recipe.step_order !== 'undefined') {
      // add it to our map
      a[recipe.id].steps.set(recipe.step_order, { text: recipe.step_text, time: recipe.step_time })
    }

    if (recipe.ingredient_name) {
      a[recipe.id].ingredients.set(recipe.ingredient_name, { amount: recipe.ingredient_amount, measurement: recipe.ingredient_measurement })
    }

    return a
  }, {} as { [x: string]: FullRecipe })))
  .then(grouped => grouped.map((recipe: FullRecipe) => {

    const tags = Array.from(recipe.tags.values())

    const steps = Array.from(recipe.steps.entries()).sort((a, b) => {
      if(a[0] > b[0]) {
        return 1
      }

      if(b[0] > a[0]) {
        return -1
      }

      return 0
    }).map(([_, step]: any) => step)

    
    const ingredients = Array.from(recipe.ingredients.entries()).map(([name, ingredient]: any) => ({
      ...ingredient,
      name
    }))
    
    return ({
      ...recipe,
      tags,
      steps,
      ingredients
    })
  }))

with-join.ts

/**
  * while probably better for performance, this is hardly readable
  */
export const list = async (query: Query, db: DB) => db.from((builder: DB) => builder
    .from('recipes')
    .select('*')
    .limit(query.limit)
    .offset(query.offset)
    .orderBy(query.sort_col, query.sort_ord)
    .as('recipes')
  )
  .select(
    'recipes.*',
    'recipe_tags.tag as tag',
    'recipe_steps.time as step_time',
    'recipe_steps.order as step_order',
    'recipe_steps.step as step_text',
    'recipe_ingredients.name as ingredient_name',
    'recipe_ingredients.amount as ingredient_amount',
    'recipe_ingredients.measurement as ingredient_measurement'
  )
  .join('recipe_tags', 'recipe_tags.recipe_id', '=', 'recipes.id')
  .join('recipe_steps', 'recipe_steps.recipe_id', '=', 'recipes.id')
  .join('recipe_ingredients', 'recipe_ingredients.recipe_id', '=', 'recipes.id')
  .then(results => Object.values(results.reduce((a, c) => {

    // I have not seen this result yet
    
    if (!(c.id in a)) {
      a[c.id] = {
        ...c,
        tags: new Set(),
        ingredients: new Map(),
        steps: new Map()
      }
    }
    
    // if this step has a tag
    if(c.tag) {
      // add it to the set
      a[c.id].tags.add(c.tag)
    }

    // if this has a step order, it is a step
    if (typeof c.step_order !== 'undefined') {
      // add it to our map
      a[c.id].steps.set(c.step_order, { text: c.step_text, time: c.step_time })
    }

    // if i
    if (c.ingredient_name) {
      a[c.id].ingredients.set(c.ingredient_name, { amount: c.ingredient_amount, measurement: c.ingredient_measurement })
    }

    return a
  }, {})))
  .then(grouped => grouped.map((recipe: any) => {
    recipe.tags = Array.from(recipe.tags.values())
    recipe.steps = Array.from(recipe.steps.entries()).sort((a: any, b: any) => {
      if(a[0] > b[0]) {
        return 1
      }

      if(b[0] > a[0]) {
        return -1
      }

      return 0
    }).map(([order, step]: any) => step)

    recipe.ingredients = Array.from(recipe.ingredients.entries()).map(([name, ingredient]: any) => ({
      ...ingredient,
      name
    }))
    
    delete recipe.tag
    delete recipe.step_time
    delete recipe.step_order
    delete recipe.step_text
    delete recipe.recipe_id
    delete recipe.ingredient_name
    delete recipe.ingredient_amount
    delete recipe.ingredient_measurement

    return recipe
  }))

example.ts is the easiest, more direct way of doing this but it makes 4 * n queries. join-with-helpers.ts makes one query but it is a beast to write and far harder to grok what it's doing and why. I would love if I could write example.ts and the computer figure out how to make it a single query.

The after.ts file is the final guess at how best to do this. It is one query plus one mapping of the returned results. I think there is probably a better way to do it but this is clear-ish plus performant-ish

all-in-one.ts makes it a single SQL query and I do not have to do anything in userland! What a journey!