Fabian B. Posts - Answer Overflow

Fabian B.

Explore posts from servers

•Created by Fabian B. on 2/14/2024 in #help

How do I write a subquery inside select?

I have this SQL which is doing what I want

SELECT 
    "job_types"."id", 
    "job_types"."name",
    (
        SELECT 
            json_build_array("job_types_parent"."id", "job_types_parent"."name")
        FROM 
            "job_types" "job_types_parent"
        WHERE 
            "job_types_parent"."id" = "job_types"."parent_id"
        LIMIT 1
    ) AS "parent"
FROM 
    "job_types"
WHERE 
    "job_types"."parent_id" IS NOT NULL;

SELECT 
    "job_types"."id", 
    "job_types"."name",
    (
        SELECT 
            json_build_array("job_types_parent"."id", "job_types_parent"."name")
        FROM 
            "job_types" "job_types_parent"
        WHERE 
            "job_types_parent"."id" = "job_types"."parent_id"
        LIMIT 1
    ) AS "parent"
FROM 
    "job_types"
WHERE 
    "job_types"."parent_id" IS NOT NULL;

The query selection representation for this would be

const allJobTypesQuery = db.query.jobTypes.findMany({
  columns: {
    id: true,
    name: true,
  },
  with: {
    parent: {
      columns: {
        id: true,
        name: true,
      },
    },
  },
  where: (t, { isNotNull }) => isNotNull(t.parentId),
});

const allJobTypesQuery = db.query.jobTypes.findMany({
  columns: {
    id: true,
    name: true,
  },
  with: {
    parent: {
      columns: {
        id: true,
        name: true,
      },
    },
  },
  where: (t, { isNotNull }) => isNotNull(t.parentId),
});

which also works. But I need a representation in a normal select query. I am trying to wrap my head around this, but it just does not seem to work. So far I got this, but not sure if I am on the right path here. I don't see how I can select from the subquery in allJobTypesManual.

const jobTypesParent = aliasedTable(jobTypes, "jobTypes_parent");

const sq = db
  .select({
    data: sql`json_build_array("parent"."id", "parent"."name")`.as('parent_data'),
  })
  .from(jobTypesParent)
  .where(eq(jobTypesParent.id, jobTypes.parentId))
  .limit(1)
  .as("parent");

const allJobTypesManual = await db
  .select({
    jobType: {
      id: jobTypes.id,
      name: jobTypes.name,
    },
    // how do I select the subquery fields here?
  })
  .from(jobTypes)
  .where(isNotNull(jobTypes.parentId))
  .groupBy(jobTypes.id);

const jobTypesParent = aliasedTable(jobTypes, "jobTypes_parent");

const sq = db
  .select({
    data: sql`json_build_array("parent"."id", "parent"."name")`.as('parent_data'),
  })
  .from(jobTypesParent)
  .where(eq(jobTypesParent.id, jobTypes.parentId))
  .limit(1)
  .as("parent");

const allJobTypesManual = await db
  .select({
    jobType: {
      id: jobTypes.id,
      name: jobTypes.name,
    },
    // how do I select the subquery fields here?
  })
  .from(jobTypes)
  .where(isNotNull(jobTypes.parentId))
  .groupBy(jobTypes.id);

Thanks in advance!

2 replies

DTDrizzle Team

•Created by Fabian B. on 1/15/2024 in #help

type "GEOMETRY(POINT)" does not exist

Hi there! I try to use PostGIS with drizzle and having a hard time. I try to run the following migration file

ALTER TABLE "locations" ADD COLUMN "geoPoint" "GEOMETRY(POINT)";

ALTER TABLE "locations" ADD COLUMN "geoPoint" "GEOMETRY(POINT)";

But it errors with

PostgresError: type "GEOMETRY(POINT)" does not exist
    at ErrorResponse (file:///Users/fabian/<redacted>/node_modules/.pnpm/[email protected]/node_modules/postgres/src/connection.js:790:26)
    at handle (file:///Users/fabian/<redacted>/node_modules/.pnpm/[email protected]/node_modules/postgres/src/connection.js:476:6)
    at Socket.data (file:///Users/fabian/<redacted>/node_modules/.pnpm/[email protected]/node_modules/postgres/src/connection.js:315:9)
    at Socket.emit (node:events:514:28)
    at addChunk (node:internal/streams/readable:324:12)
    at readableAddChunk (node:internal/streams/readable:297:9)
    at Readable.push (node:internal/streams/readable:234:10)
    at TCP.onStreamRead (node:internal/stream_base_commons:190:23) {
  severity_local: 'ERROR',
  severity: 'ERROR',
  code: '42704',
  position: '47',
  file: 'parse_type.c',
  line: '270',
  routine: 'typenameType'
}

PostgresError: type "GEOMETRY(POINT)" does not exist
    at ErrorResponse (file:///Users/fabian/<redacted>/node_modules/.pnpm/[email protected]/node_modules/postgres/src/connection.js:790:26)
    at handle (file:///Users/fabian/<redacted>/node_modules/.pnpm/[email protected]/node_modules/postgres/src/connection.js:476:6)
    at Socket.data (file:///Users/fabian/<redacted>/node_modules/.pnpm/[email protected]/node_modules/postgres/src/connection.js:315:9)
    at Socket.emit (node:events:514:28)
    at addChunk (node:internal/streams/readable:324:12)
    at readableAddChunk (node:internal/streams/readable:297:9)
    at Readable.push (node:internal/streams/readable:234:10)
    at TCP.onStreamRead (node:internal/stream_base_commons:190:23) {
  severity_local: 'ERROR',
  severity: 'ERROR',
  code: '42704',
  position: '47',
  file: 'parse_type.c',
  line: '270',
  routine: 'typenameType'
}

Even though PostGIS is setuped successfully. When I run this SQL in supabase directly, it correctly adds the new column with the special data type. I'm not the only one: https://github.com/drizzle-team/drizzle-orm/issues/1315 Maybe someone has an idea, would be grateful!

12 replies

DTDrizzle Team

•Created by Fabian B. on 8/27/2023 in #help

on every `migrate()`: schema "drizzle" already exists, skipping

Hi there! I have setuped a postgres DB. Everytime I call migrate(), it works and migrates, but returns these two warnings:

{
  severity_local: 'NOTICE',
  severity: 'NOTICE',
  code: '42P06',
  message: 'schema "drizzle" already exists, skipping',
  file: 'schemacmds.c',
  line: '128',
  routine: 'CreateSchemaCommand'
}
{
  severity_local: 'NOTICE',
  severity: 'NOTICE',
  code: '42P07',
  message: 'relation "__drizzle_migrations" already exists, skipping',
  file: 'parse_utilcmd.c',
  line: '209',
  routine: 'transformCreateStmt'
}

{
  severity_local: 'NOTICE',
  severity: 'NOTICE',
  code: '42P06',
  message: 'schema "drizzle" already exists, skipping',
  file: 'schemacmds.c',
  line: '128',
  routine: 'CreateSchemaCommand'
}
{
  severity_local: 'NOTICE',
  severity: 'NOTICE',
  code: '42P07',
  message: 'relation "__drizzle_migrations" already exists, skipping',
  file: 'parse_utilcmd.c',
  line: '209',
  routine: 'transformCreateStmt'
}

Does anyone have an idea on why this happens and how I can stop it from happening? Clogs up my console 😄 Thanks! -- I am using [email protected], [email protected], [email protected]

11 replies

DTDrizzle Team

•Created by Fabian B. on 5/22/2023 in #help

`where` inside relational queries `with` does not work

Hi there! I'm new to drizzle and tried out the relational queries. I want to do a nested where rule inside a with relation. But doing it is giving me type errors (Object literal may only specify known properties, and where does not exist in type) and it's just ignored by the orm. Maybe someone knows what I am doing wrong and why I don't get the option to do a where. Thanks in advance! My query:

const spaceId = '...'

const found = await db.query.projects.findMany({
  with: {
    customer: {
      columns: {
        spaceId: true,
      },
      where: (customers, { eq }) => eq(customers.spaceId, spaceId),
    },
  },
}),

const spaceId = '...'

const found = await db.query.projects.findMany({
  with: {
    customer: {
      columns: {
        spaceId: true,
      },
      where: (customers, { eq }) => eq(customers.spaceId, spaceId),
    },
  },
}),

My schema (simplified):

export const spaces = pgTable('spaces', {
    id: uuid('id').primaryKey().defaultRandom(),
    companyName: text('company_name').notNull(),
    companyTaxNumber: text('company_tax_number').notNull(),
    companyTaxIdNumber: text('company_tax_id_number'),
    companyTaxUstNumber: text('company_tax_ust_number'),
})

export const spacesRelations = relations(spaces, ({ one, many }) => ({
    customers: many(customers),
}))

export const projects = pgTable('projects', {
    id: uuid('id').primaryKey().defaultRandom(),
    name: text('name').notNull(),
    status: projectsStatusEnum('status').notNull(), 
    customerId: uuid('customer_id').references(() => customers.id, {
        onDelete: 'set null',
    }),
})

export const projectsRelations = relations(projects, ({ one }) => ({
    customer: one(customers, {
        fields: [projects.customerId],
        references: [customers.id],
    }),
}))

export const spaces = pgTable('spaces', {
    id: uuid('id').primaryKey().defaultRandom(),
    companyName: text('company_name').notNull(),
    companyTaxNumber: text('company_tax_number').notNull(),
    companyTaxIdNumber: text('company_tax_id_number'),
    companyTaxUstNumber: text('company_tax_ust_number'),
})

export const spacesRelations = relations(spaces, ({ one, many }) => ({
    customers: many(customers),
}))

export const projects = pgTable('projects', {
    id: uuid('id').primaryKey().defaultRandom(),
    name: text('name').notNull(),
    status: projectsStatusEnum('status').notNull(), 
    customerId: uuid('customer_id').references(() => customers.id, {
        onDelete: 'set null',
    }),
})

export const projectsRelations = relations(projects, ({ one }) => ({
    customer: one(customers, {
        fields: [projects.customerId],
        references: [customers.id],
    }),
}))

9 replies

Gaming

Programming