Help with query writing
Hi, I'm hoping someone will help me with this query. I have the following query in one of my service files:
'team_projects' is a table for mapping 'team_id' to 'project_id'
'projects' is the table with main project meta data including being the true 'id' for other tables to reference as 'project_id'
'projects_data' is a table with reference to 'project_id' and a 'data' object for storing other data
'projects_users' is a table mapping 'project_id' to 'user_id' along with the 'user_role' (owner, admin etc) for that project
When I run this query in my app, I get three items back when I expect only one. It seems each item has all the same data except the 'project_users'. In my table (local dev) db I have three entries in the 'project_users' table, all for the same user defining their role in 3 projects. It seems my query returns 3 copies of all the project related data, but with each of the 'project_user' data entries, even though the 'projectId' inside two of them doesn't match. This can be seen in the attached image if I'm failing to explain it well.
I'm new to drizzle, and postgresql too, so there is a chance I'm misunderstanding the correct use here. When I'm trying to do is find all projects for the given team_id, pull out the relevant data from other tables for that project, including the user_role for the given user.
Can anyone provide any help or guidance about what I'm doing wrong?
Thanks
const results = await this.db
.select()
.from(team_projects)
.where(eq(team_projects.team_id, teamId))
.leftJoin(projects, eq(projects.project_id, team_projects.project_id))
.leftJoin(
projects_data,
eq(projects_data.project_id, team_projects.project_id),
)
.leftJoin(
project_users,
eq(project_users.project_id, team_projects.project_id) &&
eq(project_users.user_id, userId),
);'team_projects' is a table for mapping 'team_id' to 'project_id'
'projects' is the table with main project meta data including being the true 'id' for other tables to reference as 'project_id'
'projects_data' is a table with reference to 'project_id' and a 'data' object for storing other data
'projects_users' is a table mapping 'project_id' to 'user_id' along with the 'user_role' (owner, admin etc) for that project
When I run this query in my app, I get three items back when I expect only one. It seems each item has all the same data except the 'project_users'. In my table (local dev) db I have three entries in the 'project_users' table, all for the same user defining their role in 3 projects. It seems my query returns 3 copies of all the project related data, but with each of the 'project_user' data entries, even though the 'projectId' inside two of them doesn't match. This can be seen in the attached image if I'm failing to explain it well.
I'm new to drizzle, and postgresql too, so there is a chance I'm misunderstanding the correct use here. When I'm trying to do is find all projects for the given team_id, pull out the relevant data from other tables for that project, including the user_role for the given user.
Can anyone provide any help or guidance about what I'm doing wrong?
Thanks
