DT
Drizzle Team•3mo ago
mattbuttondev

How can I extend queries with additional fields

I have the following model: 
export const titleEnum = pgEnum("title", ["Mr.", "Mrs.", "Ms.", "Miss.", "Dr."]);

export const Client = pgTable("client", {
    id: uuid("id").primaryKey().defaultRandom(),
    title: titleEnum("title"),
    firstName: text("first_name"),
    lastName: text("last_name"),
    companyName: text("company_name")
})
export const titleEnum = pgEnum("title", ["Mr.", "Mrs.", "Ms.", "Miss.", "Dr."]);

export const Client = pgTable("client", {
    id: uuid("id").primaryKey().defaultRandom(),
    title: titleEnum("title"),
    firstName: text("first_name"),
    lastName: text("last_name"),
    companyName: text("company_name")
})
Depending on the information available, I would like to expose a "name" field that contains one of the following values (in order of priority): Title Firstname Lastname Firstname Lastname Title Firstname FirstName Title Lastname Lastname Company Name Null How can I do this?
1 Reply
aleclarson
aleclarson•3mo ago
one way: you can use concat_ws function within the extras option when using db.query.Client.findMany 
extras: {
  name: client => sql`concat_ws(' ', ${client.title}, ${client.firstName}, ${client.lastName})`
}
extras: {
  name: client => sql`concat_ws(' ', ${client.title}, ${client.firstName}, ${client.lastName})`
}
based on your priority list, it would need to be a little more complex though note that you can also use concatWithSeparator from the drizzle-plus npm package, for better type safety and autocomplete

Did you find this page helpful?