[BUG]: Nested object in select query

incredible007 commented 5 months ago

What version of `drizzle-orm` are you using?

0.29.3

What version of `drizzle-kit` are you using?

0.20.14

Describe the Bug

My code is

    const feedbacks = await drizzleClient
        .select({
            ...getTableColumns(feedbackFirst),
            project: {
                ...getTableColumns(project),
            },
            userFrom: {
                ...getTableColumns(users),
                userAbout: {
                    ...getTableColumns(userAbout),
                    avatar: {
                        ...getTableColumns(avatars),
                        photo: {
                            ...getTableColumns(photos),
                        }
                    }
                }
            }
        })
        .from(feedbackFirst)
        .innerJoin(project, eq(project.prid, feedbackFirst.prid))
        .innerJoin(users, eq(users.uid, feedbackFirst.uidFrom))
        .innerJoin(userAbout, eq(userAbout.uid, users.uid))
        .innerJoin(avatars, eq(avatars.uid, users.uid))
        .innerJoin(photos, eq(photos.phid, avatars.phid))

There is an error near avatar

Object literal may only specify known properties, and avatar does not exist in type

Despite of the type error, the query result is correct, with all nesting. How to fix the type error? And is it possible?

Expected behavior

The result has the correct types

Environment & setup

webstorm 2023.3.5 ts 5 node 21.4.0 macos sonoma 14.4

JohnAllenTech commented 5 months ago

I believe its impossible to tell if this is a bug or not without seeing your drizzle schema file

incredible007 commented 5 months ago


import { pgTable, pgSchema, foreignKey, pgEnum, bigint, integer, numeric, timestamp, varchar, smallint, uniqueIndex, boolean, bigserial, json, text, real, unique, index, time, date, serial, doublePrecision, primaryKey } from "drizzle-orm/pg-core"
  import { sql } from "drizzle-orm"

export const language = pgEnum("language", ['french', 'spanish', 'russian', 'english'])

export const cldata = pgSchema("cldata");

export const feedbackFirst = cldata.table("feedback_first", {
    // You can use { mode: "bigint" } if numbers are exceeding js number limitations
    fid: bigint("fid", { mode: "number" }).default(sql`nextval('cldata.s_fid'::regclass)`).primaryKey().notNull(),
    // You can use { mode: "bigint" } if numbers are exceeding js number limitations
    prid: bigint("prid", { mode: "number" }).notNull().references(() => project.prid),
    uidFrom: integer("uid_from").references(() => users.uid),
    aidTo: integer("aid_to").references(() => artist.aid),
    comfort: numeric("comfort"),
    focus: numeric("focus"),
    details: numeric("details"),
    responsible: numeric("responsible"),
    comment: varchar("comment", { length: 300 }),
    missed: boolean("missed").notNull(),
    date: timestamp("date", { precision: 6, withTimezone: true, mode: 'string' }),
},
(table) => {
    return {
        aidUidPrid: uniqueIndex("aid_uid_prid").on(table.prid, table.uidFrom, table.aidTo),
    }
});

export const project = cldata.table("project", {
    // You can use { mode: "bigint" } if numbers are exceeding js number limitations
    prid: bigint("prid", { mode: "number" }).default(sql`nextval('cldata.s_prid'::regclass)`).primaryKey().notNull(),
    uidStarted: integer("uid_started").notNull(),
    started: timestamp("started", { precision: 6, withTimezone: true, mode: 'string' }).notNull(),
    description: varchar("description", { length: 2000 }),
    place: varchar("place", { length: 60 }),
    timesheet: varchar("timesheet", { length: 2000 }),
    resultslink: varchar("resultslink"),
    fromdate: timestamp("fromdate", { precision: 6, withTimezone: true, mode: 'string' }),
    todate: timestamp("todate", { precision: 6, withTimezone: true, mode: 'string' }),
    fullday: boolean("fullday"),
    aid: integer("aid").references(() => artist.aid),
    gone: boolean("gone").default(false),
    tfp: integer("tfp"),
},
(table) => {
    return {
        pridFromdateTodate: uniqueIndex("prid_fromdate_todate").on(table.prid, table.fromdate, table.todate, table.gone),
        ind2: uniqueIndex("project_ind2").on(table.uidStarted, table.started),
    }
});

export const artist = cldata.table("artist", {
    aid: integer("aid").default(sql`nextval('cldata.s_aid'::regclass)`).primaryKey().notNull(),
    uid: integer("uid").notNull().references(() => users.uid, { onDelete: "restrict" } ),
    atype: smallint("atype").notNull().references(() => dAtype.id, { onDelete: "restrict" } ),
    hourprice: integer("hourprice").default(0).notNull(),
    experience: smallint("experience").notNull().references(() => dExperience.id, { onDelete: "restrict" } ),
    tfp: smallint("tfp").notNull().references(() => dTfp.id, { onDelete: "restrict" } ),
    haveStudio: smallint("have_studio").default(1).notNull(),
    priceList: varchar("price_list", { length: 30000 }),
    rating: doublePrecision("rating"),
    reviews: smallint("reviews").default(0),
    companyName: varchar("company_name"),
    active: boolean("active").default(true).notNull(),
    comfort: doublePrecision("comfort"),
    focus: doublePrecision("focus"),
    details: doublePrecision("details"),
    responsible: doublePrecision("responsible"),
    quality: doublePrecision("quality"),
    quantity: doublePrecision("quantity"),
    intime: doublePrecision("intime"),
},
(table) => {
    return {
        uidAid: uniqueIndex("uid_aid").on(table.aid, table.uid),
        uidAtype: uniqueIndex("artist_uid_atype").on(table.uid, table.atype),
    }
});

export const users = cldata.table("users", {
    uid: integer("uid").default(sql`nextval('cldata.s_uid'::regclass)`).primaryKey().notNull(),
    password: varchar("password").notNull(),
    email: varchar("email", { length: 70 }).notNull(),
    regtime: timestamp("regtime", { precision: 6, withTimezone: true, mode: 'string' }).notNull(),
    authprovider: varchar("authprovider").notNull(),
    vkId: varchar("vk_id"),
    fbId: varchar("fb_id"),
    salt: varchar("salt").notNull(),
    userchannel: varchar("userchannel").notNull(),
    blocked: smallint("blocked"),
    blockedDate: date("blocked_date"),
    issup: smallint("issup"),
    deviceId: text("device_id"),
    isProfileComplete: boolean("is_profile_complete").default(true).notNull(),
},
(table) => {
    return {
        uidKey: uniqueIndex("users_uid_key").on(table.uid),
        emailUnique: unique("email_unique").on(table.email),
        userchannelUnique: unique("userchannel_unique").on(table.userchannel),
    }
});

export const prResults = cldata.table("pr_results", {
    // You can use { mode: "bigint" } if numbers are exceeding js number limitations
    id: bigint("id", { mode: "number" }).default(sql`nextval('cldata.s_pr_result'::regclass)`).primaryKey().notNull(),
    uid: integer("uid"),
    // You can use { mode: "bigint" } if numbers are exceeding js number limitations
    prid: bigint("prid", { mode: "number" }).references(() => project.prid, { onDelete: "cascade", onUpdate: "cascade" } ),
    // You can use { mode: "bigint" } if numbers are exceeding js number limitations
    phid: bigint("phid", { mode: "number" }),
},
(table) => {
    return {
        pridPhid: uniqueIndex("prid_phid").on(table.prid, table.phid),
    }
});

export const photos = cldata.table("photos", {
    // You can use { mode: "bigint" } if numbers are exceeding js number limitations
    phid: bigint("phid", { mode: "number" }).default(sql`nextval('cldata.s_phid'::regclass)`).primaryKey().notNull(),
    uid: integer("uid").notNull(),
    filename: varchar("filename").notNull(),
    date: timestamp("date", { precision: 6, withTimezone: true, mode: 'string' }).notNull(),
    incloud: boolean("incloud").default(false),
    clouduploadtime: timestamp("clouduploadtime", { withTimezone: true, mode: 'string' }),
    localdeleted: boolean("localdeleted").default(false).notNull(),
    seoFilename: varchar("seo_filename"),
    altText: varchar("alt_text"),
    caption: varchar("caption"),
    keywords: varchar("keywords"),
    author: varchar("author"),
    credentials: varchar("credentials"),
    copyright: varchar("copyright"),
    noSeo: boolean("no_seo"),
},
(table) => {
    return {
        idFilename: index("id_filename").on(table.phid, table.filename),
    }
});

export const userAbout = cldata.table("user_about", {
    uid: integer("uid").primaryKey().notNull().references(() => users.uid, { onDelete: "restrict" } ),
    firstname: varchar("firstname", { length: 30 }).notNull(),
    lastname: varchar("lastname", { length: 30 }).notNull(),
    legalentity: smallint("legalentity").references(() => dLegalentity.id),
    residence: smallint("residence").references(() => dCountry.id, { onDelete: "set null" } ),
    passportData: varchar("passport_data", { length: 100 }).default(sql`'null'::character varying`),
    enddate: timestamp("enddate", { precision: 6, withTimezone: true, mode: 'string' }),
    aboutText: varchar("about_text", { length: 2000 }),
    website: varchar("website"),
    instagram: varchar("instagram"),
    facebook: varchar("facebook"),
    vkontakte: varchar("vkontakte"),
    primaryLink: varchar("primary_link"),
    // You can use { mode: "bigint" } if numbers are exceeding js number limitations
    adid: bigint("adid", { mode: "number" }).references(() => address.adid, { onDelete: "set null" } ),
    birthdate: date("birthdate"),
    defaultLang: varchar("default_lang", { length: 16 }),
    // You can use { mode: "bigint" } if numbers are exceeding js number limitations
    avatarPhid: bigint("avatar_phid", { mode: "number" }),
    // You can use { mode: "bigint" } if numbers are exceeding js number limitations
    signPhid: bigint("sign_phid", { mode: "number" }),
    phoneCountryCode: smallint("phone_country_code"),
    phone: varchar("phone", { length: 32 }),
    primaryNameEn: varchar("primary_name_en").notNull(),
    gender: smallint("gender").default(1).notNull().references(() => dGender.id),
    answeringTime: smallint("answering_time"),
    agreeRate: smallint("agree_rate"),
    regexpEngName: varchar("regexp_eng_name", { length: 150 }),
    phoneconfirm: boolean("phoneconfirm").default(false),
    wechat: varchar("wechat"),
    whatsapp: varchar("whatsapp"),
    youtube: varchar("youtube"),
    tiktok: varchar("tiktok"),
    specializations: integer("specializations").array(),
    lastOnline: timestamp("last_online", { mode: 'string' }),
    defaultLangId: integer("default_lang_id").references(() => dLanguages.langId),
},
(table) => {
    return {
        allUa: uniqueIndex("all_ua").on(table.uid, table.firstname, table.lastname, table.birthdate),
    }
});

export const dLegalentity = cldata.table("d_legalentity", {
    id: smallint("id").default(sql`nextval('cldata.d_legalentity_id_seq'::regclass)`).primaryKey().notNull(),
    nameEn: varchar("name_en").notNull(),
    nameRu: varchar("name_ru").notNull(),
},
(table) => {
    return {
        idKey: uniqueIndex("d_legalentity_id_key").on(table.id),
        dLegalentityNameEngKey: unique("d_legalentity_name_eng_key").on(table.nameEn),
        dLegalentityNameRusKey: unique("d_legalentity_name_rus_key").on(table.nameRu),
    }
});

export const dCountry = cldata.table("d_country", {
    nameRu: varchar("name_ru").notNull(),
    nameEn: varchar("name_en").notNull(),
    id: integer("id").primaryKey().notNull(),
    countrycode: varchar("countrycode"),
    show: boolean("show"),
    phoneCode: varchar("phone_code", { length: 255 }),
    defcurrency: smallint("defcurrency"),
},
(table) => {
    return {
        idKey: uniqueIndex("d_country_id_key").on(table.id),
    }
});

export const address = cldata.table("address", {
    // You can use { mode: "bigint" } if numbers are exceeding js number limitations
    adid: bigint("adid", { mode: "number" }).default(sql`nextval('cldata.s_adid'::regclass)`).primaryKey().notNull(),
    uid: integer("uid").notNull().references(() => users.uid),
    city: integer("city").notNull().references(() => dCity.cityId),
    zipcode: varchar("zipcode"),
    exactAddr: varchar("exact_addr", { length: 100 }),
    fromDate: timestamp("from_date", { precision: 6, withTimezone: true, mode: 'string' }).default(sql`NULL::timestamp with time zone`),
    toDate: timestamp("to_date", { precision: 6, withTimezone: true, mode: 'string' }).default(sql`NULL::timestamp with time zone`),
},
(table) => {
    return {
        allInd: uniqueIndex("all_ind").on(table.adid, table.uid, table.city, table.fromDate, table.toDate),
    }
});

export const dGender = cldata.table("d_gender", {
    id: integer("id").default(sql`nextval('cldata.s_gid'::regclass)`).primaryKey().notNull(),
    nameEn: varchar("name_en").notNull(),
    nameRu: varchar("name_ru").notNull(),
},
(table) => {
    return {
        idKey: uniqueIndex("d_gender_id_key").on(table.id),
    }
});

export const dLanguages = cldata.table("d_languages", {
    langId: integer("lang_id").default(sql`nextval('cldata.d_languages_lang_id_seq'::regclass)`).primaryKey().notNull(),
    nameRu: varchar("name_ru", { length: 30 }).notNull(),
    nameEn: varchar("name_en", { length: 30 }).notNull(),
});

export const avatars = cldata.table("avatars", {
    uid: integer("uid").notNull(),
    // You can use { mode: "bigint" } if numbers are exceeding js number limitations
    phid: bigint("phid", { mode: "number" }).notNull(),
    date: timestamp("date", { withTimezone: true, mode: 'string' }),
    text: varchar("text"),
},
(table) => {
    return {
        avatarsPkey: primaryKey({ columns: [table.uid, table.phid], name: "avatars_pkey"})
    }
});

export const dAtype = cldata.table("d_atype", {
    id: smallint("id").default(sql`nextval('cldata.s_atype'::regclass)`).primaryKey().notNull(),
    nameEn: varchar("name_en").notNull(),
    nameRu: varchar("name_ru").notNull(),
    worktype: boolean("worktype").default(false),
    name: json("name"),
    description: json("description"),
},
(table) => {
    return {
        idKey: uniqueIndex("d_atype_id_key").on(table.id),
    }
});

export const dExperience = cldata.table("d_experience", {
    id: smallint("id").default(sql`nextval('cldata.d_experience_id_seq'::regclass)`).primaryKey().notNull(),
    nameEn: varchar("name_en").notNull(),
    nameRu: varchar("name_ru").notNull(),
},
(table) => {
    return {
        idKey: uniqueIndex("d_experience_id_key").on(table.id),
    }
});

export const dTfp = cldata.table("d_tfp", {
    id: smallint("id").default(sql`nextval('cldata.s_tfp'::regclass)`).primaryKey().notNull(),
    nameEn: varchar("name_en").notNull(),
    nameRu: varchar("name_ru").notNull(),
},
(table) => {
    return {
        idKey: uniqueIndex("d_tfp_id_key").on(table.id),
    }
});

export const dCity = cldata.table("d_city", {
    cityId: integer("city_id").primaryKey().notNull(),
    nameRu: varchar("name_ru", { length: 100 }),
    nameEn: varchar("name_en", { length: 100 }),
    countryId: integer("country_id").references(() => dCountry.id, { onUpdate: "cascade" } ),
    latitude: real("latitude"),
    longtitude: real("longtitude"),
    show: boolean("show"),
    population: integer("population"),
},
(table) => {
    return {
        country: uniqueIndex("d_city_country").on(table.cityId, table.countryId, table.population),
        iCityEn: uniqueIndex("i_city_en").on(table.cityId, table.nameRu, table.nameEn, table.countryId, table.latitude, table.longtitude, table.population),
        iCityRu: uniqueIndex("i_city_ru").on(table.cityId, table.nameRu, table.countryId, table.latitude, table.longtitude, table.population),
    }
});

export const dCountry = cldata.table("d_country", {
    nameRu: varchar("name_ru").notNull(),
    nameEn: varchar("name_en").notNull(),
    id: integer("id").primaryKey().notNull(),
    countrycode: varchar("countrycode"),
    show: boolean("show"),
    phoneCode: varchar("phone_code", { length: 255 }),
    defcurrency: smallint("defcurrency"),
},
(table) => {
    return {
        idKey: uniqueIndex("d_country_id_key").on(table.id),
    }
});

incredible007 commented 5 months ago

Another clarification. The only first-level nested object has no type issues

JohnAllenTech commented 5 months ago

Your userAbout table doesnt have an avatar property? You have avatarPhid

incredible007 commented 5 months ago

userAbout doesnt has an avatar prop, only avatarPhid. The avatar prop I want to have in select result. It can be any name

JohnAllenTech commented 5 months ago

ok that makes sense apologies still getting up to speed with Postgres. But you havent let drizzle know that avatarPhid references an id on the avatar table?

JohnAllenTech commented 5 months ago

ie avatarPhid: bigint("avatar_phid").references(() => avatar.id),

incredible007 commented 5 months ago

Yes, avatarPhid isnt fk. And I prefer not to use fk in this case. Thats the main reason why I broke up with prisma 🙂

JohnAllenTech commented 5 months ago

Oh i see so you want it to dynamically generate the typescript based on the query and not the relations you have provided in the drizzle schema... jeez.. afraid I wont be any help

incredible007 commented 5 months ago

Yes, exactly

ExRazor commented 3 months ago

Up for this issue. First level doesn't have type issue... Can't adding nested object inside the first level of object... The query is doing fine, though, despite the type issue.

ExRazor commented 3 months ago

Still no update for this issue...

incredible007 commented 2 months ago

Found this solution with type-preserve and without subqueries

export function jsonBuildObject<T extends SelectedFields<any, any>>(shape: T) {
    const chunks: SQL[] = []

    Object.entries(shape).forEach(([key, value]) => {
        if (chunks.length > 0) {
            chunks.push(sql.raw(`,`))
        }

        chunks.push(sql.raw(`'${key}',`))

        // json_build_object formats to ISO 8601 ...
        if (is(value, PgTimestampString)) {
            chunks.push(sql`timezone('UTC', ${value})`)
        } else {
            chunks.push(sql`${value}`)
        }
    })

    return sql<SelectResultFields<T>>`coalesce(json_build_object(${sql.join(
        chunks
    )}), '{}')`
}

        const res = await tx
            .select({
                ...getTableColumns(commPhotos),
                user: jsonBuildObject({
                    ...getTableColumns(users),
                    userAbout: jsonBuildObject({
                        ...getTableColumns(userAbout),
                        avatar: jsonBuildObject({
                            ...getTableColumns(avatars),
                            photo: jsonBuildObject({
                                ...getTableColumns(photos),
                            }),
                        }),
                    }),
                }),
            })
            .from(commPhotos)
            .leftJoin(users, eq(users.uid, commPhotos.uid))
            .leftJoin(userAbout, eq(userAbout.uid, users.uid))
            .leftJoin(avatars, eq(avatars.phid, userAbout.avatarPhid))
            .leftJoin(photos, eq(photos.phid, avatars.phid))
            .where(eq(commPhotos.commid, dto.commentId))
            .limit(1)

drizzle-team / drizzle-orm