DT
Drizzle Team•2w ago
matia

How can I query data without having to map the values with JavaScript?

I only want to get the movie and actors, but I get repeated data: 
async function getMovieWithActors(title: string) {
  return db
    .select({
      id: schema.movie.id,
      title: schema.movie.title,
      released: schema.movie.released,
      actor: { id: schema.actor.id, actor: schema.actor.name },
    })
    .from(schema.movie)
    .innerJoin(schema.movieActor, eq(schema.movie.id, schema.movieActor.movieId))
    .innerJoin(schema.actor, eq(schema.actor.id, schema.movieActor.actorId))
    .where(eq(schema.movie.title, title))
}

// output
[
  {
    id: 1,
    title: "The Shawshank Redemption",
    released: "1994",
    actor: {
      id: 1,
      actor: "Tim Robbins",
    },
  }, {
    id: 1,
    title: "The Shawshank Redemption",
    released: "1994",
    actor: {
      id: 6,
      actor: "Morgan Freeman",
    },
  }
]
async function getMovieWithActors(title: string) {
  return db
    .select({
      id: schema.movie.id,
      title: schema.movie.title,
      released: schema.movie.released,
      actor: { id: schema.actor.id, actor: schema.actor.name },
    })
    .from(schema.movie)
    .innerJoin(schema.movieActor, eq(schema.movie.id, schema.movieActor.movieId))
    .innerJoin(schema.actor, eq(schema.actor.id, schema.movieActor.actorId))
    .where(eq(schema.movie.title, title))
}

// output
[
  {
    id: 1,
    title: "The Shawshank Redemption",
    released: "1994",
    actor: {
      id: 1,
      actor: "Tim Robbins",
    },
  }, {
    id: 1,
    title: "The Shawshank Redemption",
    released: "1994",
    actor: {
      id: 6,
      actor: "Morgan Freeman",
    },
  }
]
Unless I map the values with JavaScript, or use the relational API: 
async function getMovieWithActors(title: string) {
  return actors = await db
    .select({
      id: schema.movie.id,
      title: schema.movie.title,
      released: schema.movie.released,
      actor: { id: schema.actor.id, actor: schema.actor.name },
    })
    .from(schema.movie)
    .innerJoin(schema.movieActor, eq(schema.movie.id, schema.movieActor.movieId))
    .innerJoin(schema.actor, eq(schema.actor.id, schema.movieActor.actorId))
    .where(eq(schema.movie.title, title))

  return {
    id: actors[0]?.id,
    title,
    released: actors[0]?.released,
    actors: actors.map(({ actor }) => ({ actor })),
  }
}

// output
{
  id: 1,
  title: "The Shawshank Redemption",
  released: "1994",
  actors: [
    {
      actor: {
        id: 1,
        actor: "Tim Robbins",
      },
    }, {
      actor: {
        id: 6,
        actor: "Morgan Freeman",
      },
    }
  ],
}
async function getMovieWithActors(title: string) {
  return actors = await db
    .select({
      id: schema.movie.id,
      title: schema.movie.title,
      released: schema.movie.released,
      actor: { id: schema.actor.id, actor: schema.actor.name },
    })
    .from(schema.movie)
    .innerJoin(schema.movieActor, eq(schema.movie.id, schema.movieActor.movieId))
    .innerJoin(schema.actor, eq(schema.actor.id, schema.movieActor.actorId))
    .where(eq(schema.movie.title, title))

  return {
    id: actors[0]?.id,
    title,
    released: actors[0]?.released,
    actors: actors.map(({ actor }) => ({ actor })),
  }
}

// output
{
  id: 1,
  title: "The Shawshank Redemption",
  released: "1994",
  actors: [
    {
      actor: {
        id: 1,
        actor: "Tim Robbins",
      },
    }, {
      actor: {
        id: 6,
        actor: "Morgan Freeman",
      },
    }
  ],
}
1 Reply
Drew
Drew•2w ago
the relational API is there for that very purpose. See docs on aggregating results, which notes that the regular ORM does not aggregate but you can do so yourself (in JavaScript) instead: If you already have the details of the movie, you could simply select from schema.actor instead, which would give you a list without the repeated movie details.
Drizzle ORM - Joins
Drizzle ORM is a lightweight and performant TypeScript ORM with developer experience in mind.

Did you find this page helpful?