Drizzle TeamDT
Drizzle Team•3y ago
hachoter

I need help generating a query for a schema

Hi guys I have the following schema
export const companies = pgTable('company', {
    id:serial('id' as string).primaryKey(),
    name:varchar('name' as string).notNull(),
    owner:integer('owner' as string).notNull().references(() => users.id),
    manager:integer('manager' as string).notNull().references(() => users.id),
    employees:integer('employees' as string).notNull().references(() => users.id),
})

export const users = pgTable('users', {
    id:serial('id' as string).primaryKey(),
    name:varchar('name' as string).notNull(),
    role:varchar('role' as string, {enum:['owner', 'manager', 'employee']}).notNull(),
})

export const userEmails = pgTable('user_emails', {
    id:serial('id' as string).primaryKey(),
    userId:integer('userId' as string).notNull().references(() => users.id),
    email:varchar('email' as string).notNull(),
    position:integer('position' as string).notNull(),
    profile:integer('profile' as string).notNull().references(() => profiles.id),
})

export const userPhoneNumbers = pgTable('user_emails', {
    id:serial('id' as string).primaryKey(),
    userId:integer('userId' as string).notNull().references(() => users.id),
    email:varchar('email' as string).notNull(),
    position:integer('position' as string).notNull(),
    profile:integer('profile' as string).notNull().references(() => profiles.id),
})

export const profiles = pgTable('profiles', {
    id:serial('id' as string).primaryKey(),
    name:varchar('name' as string).notNull(),

})

I want to create a single query that will generate something along the lines of this type
interface _EmployeeType{
    id:number
    role:'owner' | 'manager' | 'employee'
    name:string
    emails:{
        email:string
        profile:{
            name:string
            id:number
        } | null
    }[]
    phoneNumber:{
        phoneNumber:string
        profile:{
            name:string
            id:number
        } | null
    }[]
}
export interface Company{
    id:number
    name:string
    owner:_EmployeeType
    manager:_EmployeeType
    employees:_EmployeeType[]
}

Basically I need to create a left join for manager, owner and employee and attach it to the correct key, I also need the user to include an array of emails and phone number (ideally already sorted by position) and join the profiles on top of that

The actual database schema is not for this type of data so the schema actually makes sense, I don't want to use rqb because I want to learn how to do something like this in sql as well
Was this page helpful?