Dush Posts - Answer Overflow

Dush

•Created by Dush on 9/12/2024 in #help

Replacing hardcoded text value in `jsonb`

Hi everyone I have this data in my jsonb column of my Postgres database:

[
  {
    "insuranceGTACode": "groupama",
    "excludedPostcodes": [],
    "supportedPostcodes": [],
    "excludedDepartments": [],
    "supportedDepartments": ["75","78","92","95","60"]
  }
]

[
  {
    "insuranceGTACode": "groupama",
    "excludedPostcodes": [],
    "supportedPostcodes": [],
    "excludedDepartments": [],
    "supportedDepartments": ["75","78","92","95","60"]
  }
]

And I have this current query that I search for a row based on a condition that depends on the json value above. This query works fine and returns what is expected:

return db
    .select({
      email: wrecker.contactEmail,
    })
    .from(wrecker)
    .where(
      and(
        sql`${wrecker.zones} @> '[{"insuranceGTACode": "groupama"}]'::jsonb`,
        sql`${wrecker.zones} @> '[{"supportedDepartments": ["75"]}]'::jsonb`,
      ),
    );

return db
    .select({
      email: wrecker.contactEmail,
    })
    .from(wrecker)
    .where(
      and(
        sql`${wrecker.zones} @> '[{"insuranceGTACode": "groupama"}]'::jsonb`,
        sql`${wrecker.zones} @> '[{"supportedDepartments": ["75"]}]'::jsonb`,
      ),
    );

However when I try to replace "75" with a dynamic value I get an error of PostgresError: invalid input syntax for type json Here is the code that I'm trying to work with:

const department = "75";

return db
    .select({
      email: wrecker.contactEmail,
    })
    .from(wrecker)
    .where(
      and(
        sql`${wrecker.zones} @> '[{"insuranceGTACode": "groupama"}]'::jsonb`,
        sql`${wrecker.zones} @> '[{"supportedDepartments": [${department}]}]'::jsonb`,
      ),
    );

const department = "75";

return db
    .select({
      email: wrecker.contactEmail,
    })
    .from(wrecker)
    .where(
      and(
        sql`${wrecker.zones} @> '[{"insuranceGTACode": "groupama"}]'::jsonb`,
        sql`${wrecker.zones} @> '[{"supportedDepartments": [${department}]}]'::jsonb`,
      ),
    );

I would appreciate any help and solution for this problem

1 replies

DTDrizzle Team

•Created by Dush on 2/5/2024 in #help

Issue with SUM in sql template

db
    .select({
      total: count(),
      boPending: sql<number>`SUM(CASE WHEN ${customerFile.state} = 'BO_PENDING' THEN 1 ELSE 0 END)`,
      notLocked: sql<number>`SUM(CASE WHEN ${creator.level} IN ('api') THEN 1 ELSE 0 END)`  
    })

db
    .select({
      total: count(),
      boPending: sql<number>`SUM(CASE WHEN ${customerFile.state} = 'BO_PENDING' THEN 1 ELSE 0 END)`,
      notLocked: sql<number>`SUM(CASE WHEN ${creator.level} IN ('api') THEN 1 ELSE 0 END)`  
    })

This query works fine if the rows are available an produce the correct result. But for conditions that the rows are 0, boPending and notLocked result in null I tried

notLocked: sql<number>`SUM(CASE WHEN ${creator.level} IN ('api') THEN 1 ELSE 0 END)`.mapWith((value) => value ?? 0)

notLocked: sql<number>`SUM(CASE WHEN ${creator.level} IN ('api') THEN 1 ELSE 0 END)`.mapWith((value) => value ?? 0)

but the result is same. Is there any issue related to this that I can follow? Are there any alteranitives using query builer instead of sql template?

2 replies

DTDrizzle Team

•Created by Dush on 1/29/2024 in #help

Is custom mapping possible with Drizzle?

export const iziflow = pgSchema('iziflow')

export const vehicle = iziflow.table(
  'vehicle',
  {
   matriculation: varchar('matriculation')
  }
)

export const iziflow = pgSchema('iziflow')

export const vehicle = iziflow.table(
  'vehicle',
  {
   matriculation: varchar('matriculation')
  }
)

I'm trying to select from the mentioned table but instead of

db.select({matriculation: vehicle.matriculation}).from(...)

db.select({matriculation: vehicle.matriculation}).from(...)

I would like to get this result:

db.select({matriculation: {value: vehicle.matriculation}}).from(...)

db.select({matriculation: {value: vehicle.matriculation}}).from(...)

but I get this TS error:

Object literal may only specify known properties, and 'value' does not exist in type 'SQL<unknown> | Aliased<unknown> | PgColumn<ColumnBaseConfig<ColumnDataType, string>, {}, {}>'

is there a solution for that or should I do the mapping after the query?

1 replies

DTDrizzle Team

•Created by Dush on 1/25/2024 in #help

selecting all the columns of table join

Hi everyone. is there a supported syntax to select all the columns of a table join without defining them?

await db
    .select({
      customerFileId: customer_file.id,
      referenceId: customer_file.reference_id,
      state: customer_file.state,
      backwardStates: customer_file.backward_states,
      forwardStates: customer_file.forward_states,
      cancelStates: customer_file.cancel_states,
      dateCreated: customer_file.date_created,
      // All comulns from user table
    })
    .from(customer_file)
    .innerJoin(user, eq(customer_file.owner_id, user.id))
    .where(and(eq(customer_file.id, customer_file_id), sql`${customer_file.date_created} > NOW() - INTERVAL '10 days'`))

await db
    .select({
      customerFileId: customer_file.id,
      referenceId: customer_file.reference_id,
      state: customer_file.state,
      backwardStates: customer_file.backward_states,
      forwardStates: customer_file.forward_states,
      cancelStates: customer_file.cancel_states,
      dateCreated: customer_file.date_created,
      // All comulns from user table
    })
    .from(customer_file)
    .innerJoin(user, eq(customer_file.owner_id, user.id))
    .where(and(eq(customer_file.id, customer_file_id), sql`${customer_file.date_created} > NOW() - INTERVAL '10 days'`))

in SQL I use something similar to this:

SELECT cf.id, cf.reference_id, cf.state, cf.backward_states, cf.forward_states, cf.cancel_states, cf.date_created, u.*
FROM iziflow.customer_file cf
JOIN iziflow.user u ON cf.owner_id = u.id

SELECT cf.id, cf.reference_id, cf.state, cf.backward_states, cf.forward_states, cf.cancel_states, cf.date_created, u.*
FROM iziflow.customer_file cf
JOIN iziflow.user u ON cf.owner_id = u.id

13 replies

DTDrizzle Team

•Created by Dush on 1/11/2024 in #help

How to deal with the type of response in sql'' template

How can we deal with the types of sql'' template since the result is accessable from the rows property of result, Unlike the ORM itself.

const { rows } = await db.execute(
    sql`SELECT DISTINCT ${vehicleReferences.manufacturer} FROM ${vehicleReferences} WHERE ${vehicleReferences.vehType} = ${category}`
)

// Mapping the result to get only the brands as an Array
const vehTypes = rows.map((row) => row.manufacturer)

res.json({ brands: vehTypes })

const { rows } = await db.execute(
    sql`SELECT DISTINCT ${vehicleReferences.manufacturer} FROM ${vehicleReferences} WHERE ${vehicleReferences.vehType} = ${category}`
)

// Mapping the result to get only the brands as an Array
const vehTypes = rows.map((row) => row.manufacturer)

res.json({ brands: vehTypes })

For example rows are not type-safe here and I have to guess row manufacturer

2 replies

Gaming

Programming