Build a complex query

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