CODEWITHSUNDEEP
node.jsknex.js
Lynne Rang
Lynne Rang

Reputation: 121

KNEX: How to get nested data from foreign key using join?

I've been trying to structure a fetch call with a query to return joined data between the two tables in my database. I have one table for projects, and another for palettes that includes a foreign key of "project_id".

Below is one of many iterations I've tried so far that isn't working (it's probably a total mess by now). I tried a join for a while and then totally gave up, because fields with the same name were overwriting each other.

I also couldn't figure out how to get the palette data nested inside the project data, which would also resolve the issue of names overwriting. Finally I got to this point, just forgetting joins altogether and trying to manually structure the output, but I don't get data back or even any error message.

      .select()
      .then(projects => {
        return projects.map(async project => {
          return database('palettes')
            .where({ project_id: project.id })
            .then(palettes => ({ ...project, palettes }))
        })
      }).then(projects => res.status(200).json(projects))
        .catch(error => res.status(500).json({ error }))```

Upvotes: 1

Views: 2877

Answers (1)

madflow
madflow

Reputation: 8490

You did not provide your database type and schema structure.

Assuming: projects (project_id, name), palettes (palette_id, name, project_id)

and that you want to find all projects with a 1:1 relation to their palette this should suffice:

knex
  .select(
    'projects.project_id',
    'projects.name as project_name',
    'palettes.palette_id',
    'palettes.name as palette_name'
  )
  .from('projects')
  .innerJoin('palettes', 'projects.project_id', 'palettes.project_id')

Upvotes: 2

Related Questions