Need some help with Building a complex query

[AnsBdran]

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'),
});

[AnsBdran]

How to write a query to select the post where it has a two extraData records as follows:

• key => 'surah', value => '1'
• key => 'verse', value => '3'

[GustavoOS]

Would you have some data to help understanding the problem?

[GustavoOS]

could it be something like the following?

await db.select()
.from($postExtraData)
.where(eq($postExtraData, postId))

[AnsBdran]

Sorry about the delay, I didn't see your reply until now

[AnsBdran]

Here's how I did it

[AnsBdran]

import { consts } from '@/config/consts';
import { db } from '@/db';
import { $post, $postExtraData } from '@/db/schema';
import { and, asc, eq, ilike, inArray, SQL } from 'drizzle-orm';

type GetSurahVersePostsProps = {
  page: number;
  surah?: string;
  verse?: string;
  collectionId: number;
  q?: string;
};

export const getSurahVersePosts = async ({
  page,
  surah,
  verse,
  collectionId,
  q,
}: GetSurahVersePostsProps) => {
  // if no filters applied
  if (!verse && !surah && !q) {
    const data = await db.query.$post.findMany({
      where: eq($post.collectionId, collectionId),
      with: {
        collection: true,
        extraData: true,
      },
      limit: consts.ITEMS_PER_PAGE,
      offset: (page - 1) * consts.ITEMS_PER_PAGE,
      orderBy: asc($post.createdAt),
    });

    const count = await db.$count($post, eq($post.collectionId, collectionId));

    return { items: data, count };
  }

  const filters: SQL[] = [];
  const surahVerseFilters: SQL[] = [];

  if (q) {
    filters.push(ilike($post.title, `%${q}%`));
  }

  if (surah) {
    surahVerseFilters.push(
      inArray(
        $postExtraData.postId,
        db
          .select({ postId: $postExtraData.postId })
          .from($postExtraData)
          .where(
            and(
              eq($postExtraData.key, 'SURAH'),
              eq($postExtraData.value, surah)
            )
          )
      )
    );
  }

  if (verse) {
    surahVerseFilters.push(
      inArray(
        $postExtraData.postId,
        db
          .select({ postId: $postExtraData.postId })
          .from($postExtraData)
          .where(
            and(
              eq($postExtraData.key, 'VERSE'),
              eq($postExtraData.value, verse)
            )
          )
      )
    );
  }

  if (surah || verse) {
    filters.push(
      inArray(
        $post.id,
        db
          .select({ postId: $postExtraData.postId })
          .from($postExtraData)
          .where(and(...surahVerseFilters))
      )
    );
  }

  // ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
  // surah and verse searchParams provided;
  const data = await db.query.$post.findMany({
    where: and(...filters),
    with: {
      collection: true,
      extraData: true,
    },
    limit: consts.ITEMS_PER_PAGE,
    offset: (page - 1) * consts.ITEMS_PER_PAGE,
    orderBy: asc($post.createdAt),
  });

  const count = await db.$count(
    $post,
    inArray(
      $post.id,
      db
        .select({ postId: $postExtraData.postId })
        .from($postExtraData)
        .where(and(...filters))
    )
  );

  return { items: data, count };