matia Posts - Answer Overflow

matia

•Created by matia on 4/8/2025 in #help

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",
      },
    }
  ],
}

2 replies

Gaming

Programming