DT
Drizzle Team•2mo ago
yasserconnect

[BUG]: orderBy causes relational query to fail

const shipmentQuery = await db.query.shipment.findMany({ with: { products: true, }, // [BUG]: orderBy causes relational query to fail // orderBy: desc(shipment.createdAt), }); same issue here: https://github.com/drizzle-team/drizzle-orm/issues/1249 i cannot use orderBy any fix?
GitHub
[BUG]: orderBy causes relational query to fail · Issue #1249 · driz...
What version of drizzle-orm are you using? 0.28.6 What version of drizzle-kit are you using? n/a Describe the Bug Hi Drizzle Team, thank you for making an awesome library! I think I found a bug, an...
10 Replies
rphlmr ⚡
rphlmr ⚡•2mo ago
Are you sure this is orderBy that bugs? the where is filtering on a table column that is not part of the request if you use where in its functional way where: (table, { and, isNotNull }) => and(isNotNull(table.xx)) I guess you will not have product id but only shipment columns available
rphlmr ⚡
rphlmr ⚡•2mo ago
I have an exemple for filtering in nested queries but it is more a hack than a supported feature: https://drizzle.run/brfzfblyhllb7dz24d630px3
Drizzle Run
Deep relations with filter on match - Drizzle Run
yasserconnect
yasserconnect•2mo ago
const shipmentQuery = await db.query.shipment.findMany({
    with: {
      products: true,
    },
    limit: 10,
    orderBy: desc(shipment.createdAt),
  });
const shipmentQuery = await db.query.shipment.findMany({
    with: {
      products: true,
    },
    limit: 10,
    orderBy: desc(shipment.createdAt),
  });
thank you for share.. So how can i use it orderBy, i just want to order shipments by createdAt?
rphlmr ⚡
rphlmr ⚡•2mo ago
This looks good. Isn’t it working? You have a createdAt in your shipment table?
yasserconnect
yasserconnect•2mo ago
yup, but it take too long until return the data, if i remove orderBy it works normally! it takes more than 3 min with orderBy! and few second without !
rphlmr ⚡
rphlmr ⚡•2mo ago
you need to add index on shipment createdAt. It will be blazing faster
yasserconnect
yasserconnect•2mo ago
that what i thought, but i never do it before.. can you guide me? 
export const shipment = pgTable('Shipment', {
  id: varchar('id')
    .$defaultFn(() => generateId())
    .primaryKey(),
  createdAt: timestamp('createdAt', { withTimezone: true })
    .defaultNow()
    .notNull(),
  updatedAt: timestamp('updatedAt', {
    precision: 6,
    withTimezone: true,
  })
    .notNull()
    .$onUpdate(() => new Date()),
  userId: text('userId')
    .notNull()
    .references(() => user.id, { onDelete: 'cascade', onUpdate: 'cascade' }),
  bayanNO: text('bayanNO').notNull(),
  manifestNO: text('manifestNO').notNull(),
  importerName: text('importerName').notNull(),
  declarantName: text('declarantName'),
  importerPhone: text('importerPhone'),
  declarantPhone: text('declarantPhone'),
  bookmarkIds: text('bookmarkIds').array(),
  note: text('note'),
  portName: text('portName'),
 ...
});
export const shipment = pgTable('Shipment', {
  id: varchar('id')
    .$defaultFn(() => generateId())
    .primaryKey(),
  createdAt: timestamp('createdAt', { withTimezone: true })
    .defaultNow()
    .notNull(),
  updatedAt: timestamp('updatedAt', {
    precision: 6,
    withTimezone: true,
  })
    .notNull()
    .$onUpdate(() => new Date()),
  userId: text('userId')
    .notNull()
    .references(() => user.id, { onDelete: 'cascade', onUpdate: 'cascade' }),
  bayanNO: text('bayanNO').notNull(),
  manifestNO: text('manifestNO').notNull(),
  importerName: text('importerName').notNull(),
  declarantName: text('declarantName'),
  importerPhone: text('importerPhone'),
  declarantPhone: text('declarantPhone'),
  bookmarkIds: text('bookmarkIds').array(),
  note: text('note'),
  portName: text('portName'),
 ...
});
export const product = pgTable('Product', {
  id: varchar('id')
    .$defaultFn(() => generateId())
    .primaryKey(),
  createdAt: timestamp('createdAt', { withTimezone: true })
    .defaultNow()
    .notNull(),
  updatedAt: timestamp('updatedAt', {
    precision: 6,
    withTimezone: true,
  })
    .notNull()
    .$onUpdate(() => new Date()),
  userId: text('userId')
    .notNull()
    .references(() => user.id, { onDelete: 'cascade', onUpdate: 'cascade' }),
  shipmentId: text('shipmentId')
    .notNull()
    .references(() => shipment.id, {
      onDelete: 'cascade',
      onUpdate: 'cascade',
    }),
  item: text('item').notNull(),
  weight: doublePrecision('weight').notNull(),
  status: text('status').notNull(),
  countryOrigin: text('countryOrigin').notNull(),
  rejectReason: text('rejectReason'),
  rejectDetail: text('rejectDetail'),
  hsCode: text('hsCode'),
  sampleName: text('sampleName'),
  ...
});
export const product = pgTable('Product', {
  id: varchar('id')
    .$defaultFn(() => generateId())
    .primaryKey(),
  createdAt: timestamp('createdAt', { withTimezone: true })
    .defaultNow()
    .notNull(),
  updatedAt: timestamp('updatedAt', {
    precision: 6,
    withTimezone: true,
  })
    .notNull()
    .$onUpdate(() => new Date()),
  userId: text('userId')
    .notNull()
    .references(() => user.id, { onDelete: 'cascade', onUpdate: 'cascade' }),
  shipmentId: text('shipmentId')
    .notNull()
    .references(() => shipment.id, {
      onDelete: 'cascade',
      onUpdate: 'cascade',
    }),
  item: text('item').notNull(),
  weight: doublePrecision('weight').notNull(),
  status: text('status').notNull(),
  countryOrigin: text('countryOrigin').notNull(),
  rejectReason: text('rejectReason'),
  rejectDetail: text('rejectDetail'),
  hsCode: text('hsCode'),
  sampleName: text('sampleName'),
  ...
});
export const shipmentRelations = relations(shipment, ({ one, many }) => ({
  products: many(product),
}));
export const shipmentRelations = relations(shipment, ({ one, many }) => ({
  products: many(product),
}));
export const productRelations = relations(product, ({ one }) => ({
  shipment: one(shipment, {
    fields: [product.shipmentId],
    references: [shipment.id],
  }),
}));
export const productRelations = relations(product, ({ one }) => ({
  shipment: one(shipment, {
    fields: [product.shipmentId],
    references: [shipment.id],
  }),
}));
 export const shipment = pgTable('Shipment', {
  id: varchar('id')
    .$defaultFn(() => generateId())
    .primaryKey(),
  createdAt: timestamp('createdAt', { withTimezone: true })
    .defaultNow()
    .notNull(),
  updatedAt: timestamp('updatedAt', {
    precision: 6,
    withTimezone: true,
  })
    .notNull()
    .$onUpdate(() => new Date()),
  userId: text('userId')
    .notNull()
    .references(() => user.id, { onDelete: 'cascade', onUpdate: 'cascade' }),
  bayanNO: text('bayanNO').notNull(),
  manifestNO: text('manifestNO').notNull(),
  importerName: text('importerName').notNull(),
  declarantName: text('declarantName'),
  importerPhone: text('importerPhone'),
  declarantPhone: text('declarantPhone'),
  bookmarkIds: text('bookmarkIds').array(),
  note: text('note'),
  portName: text('portName'),
 ...
}, (table) => ({
  createdAtIdx: index('shipment_created_at_idx').on(table.createdAt),
}));
 export const shipment = pgTable('Shipment', {
  id: varchar('id')
    .$defaultFn(() => generateId())
    .primaryKey(),
  createdAt: timestamp('createdAt', { withTimezone: true })
    .defaultNow()
    .notNull(),
  updatedAt: timestamp('updatedAt', {
    precision: 6,
    withTimezone: true,
  })
    .notNull()
    .$onUpdate(() => new Date()),
  userId: text('userId')
    .notNull()
    .references(() => user.id, { onDelete: 'cascade', onUpdate: 'cascade' }),
  bayanNO: text('bayanNO').notNull(),
  manifestNO: text('manifestNO').notNull(),
  importerName: text('importerName').notNull(),
  declarantName: text('declarantName'),
  importerPhone: text('importerPhone'),
  declarantPhone: text('declarantPhone'),
  bookmarkIds: text('bookmarkIds').array(),
  note: text('note'),
  portName: text('portName'),
 ...
}, (table) => ({
  createdAtIdx: index('shipment_created_at_idx').on(table.createdAt),
}));
is this correct?
rphlmr ⚡
rphlmr ⚡•2mo ago
yes. You could also create an index for shipmentId in product too. Fields and references used in relation benefit from indexes (no need for primary keys, but here shipmentId is a foreign key for product, so this is why I suggest to add an index too). you can even specify .on(table.createdAt.desc()) if it is always how you order it
rphlmr ⚡
rphlmr ⚡•2mo ago
https://orm.drizzle.team/docs/indexes-constraints#indexes
Drizzle ORM - Indexes & Constraints
Drizzle ORM is a lightweight and performant TypeScript ORM with developer experience in mind.
yasserconnect
yasserconnect•2mo ago
it works, thanks a lot @Raphaël M (@rphlmr) ⚡ 😍
Want results from more Discord servers?
Add your server