Build a complex query

the schema:

import {
  pgTable,
  text,
  varchar,
  jsonb,
  timestamp,
  integer,
  boolean,
  pgEnum,
  primaryKey,
  serial,
} from 'drizzle-orm/pg-core';
import { relations } from 'drizzle-orm';
import { postExtensionEnum } from './enums';
import { $collection } from './collection';

export const $post = pgTable('posts', {
  id: serial('id').primaryKey(),
  createdAt: timestamp('created_at').defaultNow().notNull(),
  updatedAt: timestamp('updated_at').defaultNow().notNull(),
  title: text('title').notNull(),
  title_: jsonb('title_'),
  content: text('content').notNull(),
  content_: jsonb('content_'),
  collectionId: integer('collection_id')
    .notNull()
    .references(() => $collection.id, { onDelete: 'cascade' }),
  extension: postExtensionEnum('extension').default('BASE').notNull(),
  order: integer('order'),
  deletedAt: timestamp('deleted_at'),
});

export const $postExtraData = pgTable(
  'post_extra_data',
  {
    key: text('key').notNull(),
    value: text('value').notNull(),
    postId: integer('post_id')
      .notNull()
      .references(() => $post.id, { onDelete: 'cascade' }),
  },
  (table) => [
    {
      pk: primaryKey({
        name: 'post_data',
        columns: [table.key, table.value, table.postId],
      }),
    },
  ]
);

export const $postMetadata = pgTable('post_metadata', {
  id: serial('id').primaryKey(),
  postId: integer('post_id')
    .unique()
    .notNull()
    .references(() => $post.id, { onDelete: 'cascade' }),
  views: integer('views').default(0).notNull(),
  isFeatured: boolean('is_featured').default(false).notNull(),
  publishedAt: timestamp('published_at'),
  archivedAt: timestamp('archived_at'),
});

import {
  pgTable,
  text,
  varchar,
  jsonb,
  timestamp,
  integer,
  boolean,
  pgEnum,
  primaryKey,
  serial,
} from 'drizzle-orm/pg-core';
import { relations } from 'drizzle-orm';
import { postExtensionEnum } from './enums';
import { $collection } from './collection';

export const $post = pgTable('posts', {
  id: serial('id').primaryKey(),
  createdAt: timestamp('created_at').defaultNow().notNull(),
  updatedAt: timestamp('updated_at').defaultNow().notNull(),
  title: text('title').notNull(),
  title_: jsonb('title_'),
  content: text('content').notNull(),
  content_: jsonb('content_'),
  collectionId: integer('collection_id')
    .notNull()
    .references(() => $collection.id, { onDelete: 'cascade' }),
  extension: postExtensionEnum('extension').default('BASE').notNull(),
  order: integer('order'),
  deletedAt: timestamp('deleted_at'),
});

export const $postExtraData = pgTable(
  'post_extra_data',
  {
    key: text('key').notNull(),
    value: text('value').notNull(),
    postId: integer('post_id')
      .notNull()
      .references(() => $post.id, { onDelete: 'cascade' }),
  },
  (table) => [
    {
      pk: primaryKey({
        name: 'post_data',
        columns: [table.key, table.value, table.postId],
      }),
    },
  ]
);

export const $postMetadata = pgTable('post_metadata', {
  id: serial('id').primaryKey(),
  postId: integer('post_id')
    .unique()
    .notNull()
    .references(() => $post.id, { onDelete: 'cascade' }),
  views: integer('views').default(0).notNull(),
  isFeatured: boolean('is_featured').default(false).notNull(),
  publishedAt: timestamp('published_at'),
  archivedAt: timestamp('archived_at'),
});

2 Replies

Anas BadranOP•5d ago

How to write a query to select the post where it's have a two extraData records as follows: - key => 'surah', value => '1' - key => 'verse', value => '3' this sql query works fine, but is there equivalent in drizzle?

  SELECT p.*
  FROM posts p
  JOIN post_extra_data ped1 ON p.id = ped1.post_id AND ped1.key = 'surah' AND ped1.value = '1'
  JOIN post_extra_data ped2 ON p.id = ped2.post_id AND ped2.key = 'verse' AND ped2.value = '3'

  SELECT p.*
  FROM posts p
  JOIN post_extra_data ped1 ON p.id = ped1.post_id AND ped1.key = 'surah' AND ped1.value = '1'
  JOIN post_extra_data ped2 ON p.id = ped2.post_id AND ped2.key = 'verse' AND ped2.value = '3'

scape•4d ago

I suppose you are struggling with making an aliased table for two similar joins Yes, it's definitely possible to do it with drizzle

import { aliasedTable, getTableColumns, and } from "drizzle-orm"

const ped1 = aliasedTable($postExtraData, 'ped1');
const ped2 = aliasedTable($postExtraData, 'ped2');

await db.select(getTableColumns($posts))
  .from($posts)
  .innerJoin(ped1, and(eq($posts.id, ped1.postId), and(eq(ped1.key, 'surah'), eq(ped1.value, '1'))))
  .innerJoin(ped2, and(eq($posts.id, ped2.postId), and(eq(ped2.key, 'verse'), eq(ped1.value, '3'))))

import { aliasedTable, getTableColumns, and } from "drizzle-orm"

const ped1 = aliasedTable($postExtraData, 'ped1');
const ped2 = aliasedTable($postExtraData, 'ped2');

await db.select(getTableColumns($posts))
  .from($posts)
  .innerJoin(ped1, and(eq($posts.id, ped1.postId), and(eq(ped1.key, 'surah'), eq(ped1.value, '1'))))
  .innerJoin(ped2, and(eq($posts.id, ped2.postId), and(eq(ped2.key, 'verse'), eq(ped1.value, '3'))))

more on aliases & selfjoins: https://orm.drizzle.team/docs/joins#aliases--selfjoins

Drizzle ORM - Joins

Drizzle ORM is a lightweight and performant TypeScript ORM with developer experience in mind.

Gaming

Programming

Build a complex query