dion Posts - Answer Overflow

dion

Explore posts from servers

•Created by dion on 6/26/2024 in #help

Possible bug with nested query + reverse access

I have a pretty nested query.

// fails
const result = await tx.query.projectinternalcost.findMany({
  with: {
    projectinternalcostitem: {
      with: {
          projecttransactionitems: {
              with: {
              projecttransaction: true //<- reverse many-to-one relation
              }
          },
          itemcategory: true
     },
  }
});

// fails
const result = await tx.query.projectinternalcost.findMany({
  with: {
    projectinternalcostitem: {
      with: {
          projecttransactionitems: {
              with: {
              projecttransaction: true //<- reverse many-to-one relation
              }
          },
          itemcategory: true
     },
  }
});

It results in this error:

[0]   code: '42703',
[0]   detail: 'There is a column named "projecttransaction_id" in table "project_projectinternalcosts_projectinternalcostitems_projecttr", but it cannot be referenced from this part of the query.',
[0]   hint: 'To reference that column, you must mark this subquery with LATERAL.',

[0]   code: '42703',
[0]   detail: 'There is a column named "projecttransaction_id" in table "project_projectinternalcosts_projectinternalcostitems_projecttr", but it cannot be referenced from this part of the query.',
[0]   hint: 'To reference that column, you must mark this subquery with LATERAL.',

I can make the query work if i go one level down:

// works
const result = await tx.query.projectinternalcostitem.findMany({
  with: {
    projecttransactionitems: {
      with: {
    projecttransaction: true
       }
    },
    itemcategory: true
    }
});

// works
const result = await tx.query.projectinternalcostitem.findMany({
  with: {
    projecttransactionitems: {
      with: {
    projecttransaction: true
       }
    },
    itemcategory: true
    }
});

Or alternatively if i remove the reverse access:

// works
const result = await tx.query.projectinternalcost.findMany({
  with: {
    projectinternalcostitem: {
      with: {
          projecttransactionitems: true,
          itemcategory: true
     },
  }
});

// works
const result = await tx.query.projectinternalcost.findMany({
  with: {
    projectinternalcostitem: {
      with: {
          projecttransactionitems: true,
          itemcategory: true
     },
  }
});

Hope you guys can assist me with this!

3 replies

DTDrizzle Team

•Created by dion on 3/15/2024 in #help

drizzle-zod not infering jsonb type properly

I have the following table

export const dataQueryRun = pgTable('data_query_run', {
    ...
    parameters: jsonb('parameters').notNull().$type<Record<string, unknown>>(),
        ...
});

export const dataQueryRun = pgTable('data_query_run', {
    ...
    parameters: jsonb('parameters').notNull().$type<Record<string, unknown>>(),
        ...
});

And i use drizzle-zod to do the following types:

const selectDataQueryRunSchema = createSelectSchema(dataQueryRun, {
    logs: z.array(z.string())
});
export type TDataQueryRun = z.infer<typeof selectDataQueryRunSchema>;

const selectDataQueryRunSchema = createSelectSchema(dataQueryRun, {
    logs: z.array(z.string())
});
export type TDataQueryRun = z.infer<typeof selectDataQueryRunSchema>;

I get this:

type TDataQueryRun = {
   ...
    parameters: ((string | number | boolean | {
        [key: string]: Json;
    } | Json[]) & (string | ... 4 more ... | undefined)) | null;
}

type TDataQueryRun = {
   ...
    parameters: ((string | number | boolean | {
        [key: string]: Json;
    } | Json[]) & (string | ... 4 more ... | undefined)) | null;
}

instead of this:

type TDataQueryRun = {
   ...
    parameters: Record<string,unknown>
}

type TDataQueryRun = {
   ...
    parameters: Record<string,unknown>
}

Does anybody know why and how to fix this?

2 replies

DTDrizzle Team

•Created by dion on 11/28/2023 in #help

Infering custom types

I am following the guide https://orm.drizzle.team/docs/custom-types#examples referenced here to create a custom JSONb schema. However, when i create an insert schema, the customtype field is of zod any

2 replies

DTDrizzle Team

•Created by dion on 9/26/2023 in #help

Class inheritance (Inherits keyword)

Is there anyway to easily replicate this behaviour in drizzle?

-- Creating the parent table `person`
CREATE TABLE person (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    birth_date DATE
);

-- Creating the child table `student` which inherits from `person`
CREATE TABLE student (
    grade_level VARCHAR(15)
) INHERITS (person);

-- Creating another child table `staff` which inherits from `person`
CREATE TABLE staff (
    position VARCHAR(50)
) INHERITS (person);

-- Creating the parent table `person`
CREATE TABLE person (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    birth_date DATE
);

-- Creating the child table `student` which inherits from `person`
CREATE TABLE student (
    grade_level VARCHAR(15)
) INHERITS (person);

-- Creating another child table `staff` which inherits from `person`
CREATE TABLE staff (
    position VARCHAR(50)
) INHERITS (person);

Inserting Data:

INSERT INTO student (name, birth_date, grade_level) VALUES ('John Doe', '2000-01-01', 'Sophomore');
INSERT INTO staff (name, birth_date, position) VALUES ('Alice Smith', '1980-05-15', 'Manager');

INSERT INTO student (name, birth_date, grade_level) VALUES ('John Doe', '2000-01-01', 'Sophomore');
INSERT INTO staff (name, birth_date, position) VALUES ('Alice Smith', '1980-05-15', 'Manager');

Querying Data:

SELECT * FROM person;

SELECT * FROM person;

This will return all rows from both student and staff tables.

7 replies

Gaming

Programming