Drizzle Team•7mo ago

Many to many flatten response

I'm struggling to understand why does drizzle work this way and why there isnt an easy way to flatten the response structure. I have 3 tables, Profile, Interest and Language. I also have 2 other join tables, ProfileInterest and ProfileLanguage. As you may have noticed, I have a many-to-many relationship between Profile and Interest and between Profile and Language. All 5 tables are in place (3 "models" and 2 join tables). I also have all the relations created and everything works as expected. To query for a specific profile including its' interests and languages I run the following.

db.query.Profile.findFirst({
        where: eq(Profile.id, input.id),
        with: {
          interests: {
            with: {
              interest: true,
            },
          },
          languages: {
            with: {
              language: true,
            },
          },
        },
      });

db.query.Profile.findFirst({
        where: eq(Profile.id, input.id),
        with: {
          interests: {
            with: {
              interest: true,
            },
          },
          languages: {
            with: {
              language: true,
            },
          },
        },
      });

The problem? The response looks like this

const currentOutput = {
  ...Profile, // All the fields from Profile
  interests: [
    {
      profileId: 1,
      interestId: 1,
      interest: {
        id: 1,
        name: "Music",
      },
    },
  ],
  languages: [
    {
      profileId: 1,
      languageId: 1,
      language: {
        id: 1,
        name: "English",
        code: "en",
      },
    },
  ],
};

const currentOutput = {
  ...Profile, // All the fields from Profile
  interests: [
    {
      profileId: 1,
      interestId: 1,
      interest: {
        id: 1,
        name: "Music",
      },
    },
  ],
  languages: [
    {
      profileId: 1,
      languageId: 1,
      language: {
        id: 1,
        name: "English",
        code: "en",
      },
    },
  ],
};

And of course, I'd like the following instead

const desiredOutput = {
  ...Profile,
  interests: [
    {
      id: 1,
      name: "Music",
    },
  ],
  languages: [
    {
      id: 1,
      name: "English",
      code: "en",
    },
  ],
};

const desiredOutput = {
  ...Profile,
  interests: [
    {
      id: 1,
      name: "Music",
    },
  ],
  languages: [
    {
      id: 1,
      name: "English",
      code: "en",
    },
  ],
};

Is there a way to achieve this using the queryAPI? At the moment the only thing I can do is either use it this way, which is not the end of the world but very inconvenient, or I can just flatten the structure in my server, which is also quite inconvenient...

1 Reply

Maastonakki•6mo ago

I was just about to write same question, funny enough having also languages relation which I would like to get flattened.

Gaming

Programming

Many to many flatten response