stramel Posts - Answer Overflow

stramel

•Created by stramel on 6/15/2023 in #help

Trouble getting query to work with subquery

I'm having trouble getting this query to work in drizzle,

SELECT i.*, h1.*
FROM items i
left JOIN (
    SELECT h.*
    FROM history h
    order by h.item_id, h.timestamp desc
    limit 2
) h1 ON i.id = h1.item_id
WHERE i.name LIKE '%foo%'
ORDER BY i.name
limit 10;

SELECT i.*, h1.*
FROM items i
left JOIN (
    SELECT h.*
    FROM history h
    order by h.item_id, h.timestamp desc
    limit 2
) h1 ON i.id = h1.item_id
WHERE i.name LIKE '%foo%'
ORDER BY i.name
limit 10;

This is what I'm attempting:

const sq = db
  .select()
  .from(history)
  .where(eq(history.itemId, items.id))
  .orderBy(desc(history.timestamp))
  .limit(2)
  .as('sq')
const itemSearchByNameWithLatestPriceData = db
  .select({
    item: items,
    category: categories,
    history,
  })
  .from(items)
  .leftJoin(sq, eq(history.itemId, items.id))
  .where(or(like(items.name, placeholder('query')), like(items.name, placeholder('query'))))
  .limit(20)
  .prepare()

await searchWithPriceData.execute({ query: '%foo%' });

const sq = db
  .select()
  .from(history)
  .where(eq(history.itemId, items.id))
  .orderBy(desc(history.timestamp))
  .limit(2)
  .as('sq')
const itemSearchByNameWithLatestPriceData = db
  .select({
    item: items,
    category: categories,
    history,
  })
  .from(items)
  .leftJoin(sq, eq(history.itemId, items.id))
  .where(or(like(items.name, placeholder('query')), like(items.name, placeholder('query'))))
  .limit(20)
  .prepare()

await searchWithPriceData.execute({ query: '%foo%' });

Can someone please help me? 🙂

1 replies

DTDrizzle Team

•Created by stramel on 6/13/2023 in #help

Schema not added to FK constraints

Hi! 👋🏽 I'm trying to use drizzle with MySQL. I have a mysqlSchema that I made and then am trying to create a FK but the generated SQL doesn't include the schema.

export const mySchema = mysqlSchema('my')

export const categories = mySchema.table('categories', {
  id: tinyint('id').primaryKey(),
  name: varchar('name', { length: 256 }).notNull(),
})

export const items = mySchema.table('items', {
  id: int('item_id').notNull().primaryKey(),
  name: varchar('name', { length: 256 }).notNull(),
  category: tinyint('category')
    .notNull()
    .references(() => categories.id, {
      onDelete: 'cascade',
    })
})

export const mySchema = mysqlSchema('my')

export const categories = mySchema.table('categories', {
  id: tinyint('id').primaryKey(),
  name: varchar('name', { length: 256 }).notNull(),
})

export const items = mySchema.table('items', {
  id: int('item_id').notNull().primaryKey(),
  name: varchar('name', { length: 256 }).notNull(),
  category: tinyint('category')
    .notNull()
    .references(() => categories.id, {
      onDelete: 'cascade',
    })
})

Generated:

CREATE DATABASE `my`;

CREATE TABLE `my`.`categories` (
    `id` tinyint PRIMARY KEY NOT NULL,
    `name` varchar(256) NOT NULL);

CREATE TABLE `my`.`items` (
    `item_id` int PRIMARY KEY NOT NULL,
    `name` varchar(256) NOT NULL,
    `category` tinyint NOT NULL);

ALTER TABLE `items` ADD CONSTRAINT `items_category_categories_id_fk` FOREIGN KEY (`category`) REFERENCES `categories`(`id`) ON DELETE cascade ON UPDATE no action;

CREATE DATABASE `my`;

CREATE TABLE `my`.`categories` (
    `id` tinyint PRIMARY KEY NOT NULL,
    `name` varchar(256) NOT NULL);

CREATE TABLE `my`.`items` (
    `item_id` int PRIMARY KEY NOT NULL,
    `name` varchar(256) NOT NULL,
    `category` tinyint NOT NULL);

ALTER TABLE `items` ADD CONSTRAINT `items_category_categories_id_fk` FOREIGN KEY (`category`) REFERENCES `categories`(`id`) ON DELETE cascade ON UPDATE no action;

Am I doing something wrong?

11 replies

Gaming

Programming