Drizzle Team•5mo ago

Help with Join query

Hi all, I have this three tables: users (id, name, email) account (id, username, public) userAccounts (user_id, account_id, role) I would like to get a query that for a given account it gives me the account data and all the managers that this account have. I have archived this via two queries, but I would like to do it in just one DB call. The expected result: [ { username public managers: { name, email role, } } ]

2 Replies

edarcode•5mo ago

You can use db.query.table and map the result and put the names you like for c/key. I assume you already have the tables related in your schema https://orm.drizzle.team/docs/rqb#select-filters with npx drizzle-kit introspect you can generate all relations auto

Drizzle ORM - Query

Drizzle ORM is a lightweight and performant TypeScript ORM with developer experience in mind.

marcbejarOP•5mo ago

Makes sense, so I'm gonna make a query with two inner joins, so I have the tree tables information in the result. This results in something like:

[
  {
    "username": "test1",
    "public": false,
    "stripe": "test1",
    "name": null,
    "email": "[email protected]",
    "role": 1
  },
  {
    "username": "test1",
    "public": false,
    "stripe": "test1",
    "name": null,
    "email": "[email protected]",
    "role": 0
  }
]

[
  {
    "username": "test1",
    "public": false,
    "stripe": "test1",
    "name": null,
    "email": "[email protected]",
    "role": 1
  },
  {
    "username": "test1",
    "public": false,
    "stripe": "test1",
    "name": null,
    "email": "[email protected]",
    "role": 0
  }
]

Then what I understood is that i can use the map method to convert this array into an object with the common values and a new object "managers" that contains an array of the variable data. What I have done is the following:

  const data = [...query result...]
  let common_data = undefined;
  const managers = [];

  result.map((item, index) => {
    if (index === 1) {
      common_data = {
        username: item.username,
        public: item.public,
        stripe: item.stripe,
      };
    }
    managers.push({
      name: item.name,
      email: item.email,
      role: item.role,
    });
  });

  const final_data = {...settings, managers}

  const data = [...query result...]
  let common_data = undefined;
  const managers = [];

  result.map((item, index) => {
    if (index === 1) {
      common_data = {
        username: item.username,
        public: item.public,
        stripe: item.stripe,
      };
    }
    managers.push({
      name: item.name,
      email: item.email,
      role: item.role,
    });
  });

  const final_data = {...settings, managers}

And this results in:

{
    "username": "test1",
    "public": false,
    "stripe": "test1",
    "managers": [
        {
            "name": null,
            "email": "[email protected]",
            "role": 0
        },
        {
            "name": null,
            "email": "[email protected]",
            "role": 1
        }
    ],
}

{
    "username": "test1",
    "public": false,
    "stripe": "test1",
    "managers": [
        {
            "name": null,
            "email": "[email protected]",
            "role": 0
        },
        {
            "name": null,
            "email": "[email protected]",
            "role": 1
        }
    ],
}

That's the data that I need. Do you think this is the best approach or you think there is an optimal way doing this? Thanks a lot for your time.

Gaming

Programming

Help with Join query