Drizzle Team•3mo ago

`Error: no such column: unboxes.rarity` with `findMany()`

Hello. I am getting the error Error: no such column: unboxes.rarity despite not using unboxes.rarity The error comes fron the where in the query below. Is this a bug? Code:

export const getFilteredUnboxes = async (onlyCoverts?: boolean) =>
  db.query.unboxes.findMany({
    with: {
      item: true,
      case: true,
    },
    where: onlyCoverts
      ? inArray(items.rarity, ["Covert", "Extraordinary"])
      : undefined,
  });

export const getFilteredUnboxes = async (onlyCoverts?: boolean) =>
  db.query.unboxes.findMany({
    with: {
      item: true,
      case: true,
    },
    where: onlyCoverts
      ? inArray(items.rarity, ["Covert", "Extraordinary"])
      : undefined,
  });

My items schema:

export const items = sqliteTable(
  "items",
  {
    id: text("id").primaryKey().notNull(),
    name: text("name").notNull(),
    description: text("description"),
    image: text("image").notNull(),
    rarity: text("rarity").notNull(),
    phase: text("phase"),
  },
  table => {
    return {
      idxRarity: index("idx_rarity").on(table.rarity),
    };
  },
);

export const items = sqliteTable(
  "items",
  {
    id: text("id").primaryKey().notNull(),
    name: text("name").notNull(),
    description: text("description"),
    image: text("image").notNull(),
    rarity: text("rarity").notNull(),
    phase: text("phase"),
  },
  table => {
    return {
      idxRarity: index("idx_rarity").on(table.rarity),
    };
  },
);

21 Replies

Kuba•3mo ago

You're using query - did you defined and exported relations for your tables?

MozzyOP•3mo ago

Yes, like this:

MozzyOP•3mo ago

And the "main" table schema

MozzyOP•3mo ago

This works:

db
    .select({
      value: onlyCoverts || onlyPersonal ? count() : max(unboxes.id),
    })
    .from(unboxes)
    .where(
      onlyCoverts
        ? inArray(items.rarity, ["Covert", "Extraordinary"])
        : undefined,
    )
    .leftJoin(items, eq(unboxes.itemId, items.id));

db
    .select({
      value: onlyCoverts || onlyPersonal ? count() : max(unboxes.id),
    })
    .from(unboxes)
    .where(
      onlyCoverts
        ? inArray(items.rarity, ["Covert", "Extraordinary"])
        : undefined,
    )
    .leftJoin(items, eq(unboxes.itemId, items.id));

This doesn't work:

  db.query.unboxes.findMany({
    with: {
      item: true,
      case: true,
    },
    where: onlyCoverts
      ? inArray(items.rarity, ["Covert", "Extraordinary"])
      : undefined,
  });

  db.query.unboxes.findMany({
    with: {
      item: true,
      case: true,
    },
    where: onlyCoverts
      ? inArray(items.rarity, ["Covert", "Extraordinary"])
      : undefined,
  });

Surely you should be able to do this with the query API

?
This is the SQL .findMany() generates
```sql
select "id", "case_id", "item_id", "is_stat_trak", "unboxer_id", "unboxed_at", (select json_array("id", "name", "description", "image", "rarity", "phase") as "data" from (select * from "items" "unboxes_item" where "unboxes_item"."id" = "unboxes"."item_id" limit ?) "unboxes_item") as "item", (select json_array("id", "type", "name", "description", "image") as "data" from (select * from "cases" "unboxes_case" where "unboxes_case"."id" = "unboxes"."case_id" limit ?) "unboxes_case") as "case" from "unboxes" where "unboxes"."rarity" in (?, ?)
```
No clue why it does

where "unboxes"."rarity" in (?, ?)`

MozzyOP•3mo ago

Apologies for the ping @Angelelz, but I saw you in an issue that might be relevant RE: https://github.com/drizzle-team/drizzle-orm/issues/1069 Is my issue related to this do you think?

GitHub

[FEATURE]: Add back filtering by nested relations in relational que...

Describe what you want Filtering by nested relations is a common thing to do and was recently removed to improve performance. I propose a syntax to add this feature back into the relational queries...

Angelelz•3mo ago

Yes, you are hitting that issue You can make it work by using a subquery in the where Can you put your code in drizzle.run and send me the link, I might be able to help you later

MozzyOP•3mo ago

Thanks, I'll try that

MozzyOP•3mo ago

Got it here: https://drizzle.run/tt5dzixcs01b0bkfwzj0e6mj

Drizzle Run

Mozzy's issue - Drizzle Run

MozzyOP•3mo ago

I managed to solve it I think Full function

/** Gets the 100 latest unboxes. */
export const getFilteredUnboxes = async (
  onlyCoverts?: boolean,
  onlyPersonal?: boolean,
) => {
  return db.query.unboxes.findMany({
    with: {
      item: true,
      case: true,
    },
    where: and(
      onlyCoverts
        ? inArray(
            unboxes.itemId, // Compare unbox.itemId with item IDs from the subquery
            db
              .select({ id: items.id }) // Subquery selecting item IDs
              .from(items)
              .where(inArray(items.rarity, ["Covert", "Extraordinary"])), // Filter by item rarity
          )
        : undefined,
      onlyPersonal
        ? itemIsPersonal(await getOrCreateUnboxerIdCookie())
        : undefined,
    ),
    orderBy: [desc(unboxes.id)],
    limit: 100,
  });
};

/** Gets the 100 latest unboxes. */
export const getFilteredUnboxes = async (
  onlyCoverts?: boolean,
  onlyPersonal?: boolean,
) => {
  return db.query.unboxes.findMany({
    with: {
      item: true,
      case: true,
    },
    where: and(
      onlyCoverts
        ? inArray(
            unboxes.itemId, // Compare unbox.itemId with item IDs from the subquery
            db
              .select({ id: items.id }) // Subquery selecting item IDs
              .from(items)
              .where(inArray(items.rarity, ["Covert", "Extraordinary"])), // Filter by item rarity
          )
        : undefined,
      onlyPersonal
        ? itemIsPersonal(await getOrCreateUnboxerIdCookie())
        : undefined,
    ),
    orderBy: [desc(unboxes.id)],
    limit: 100,
  });
};

It does feel like a very janky workaround though. What were the performance issues that lead this to being removed?

Angelelz•3mo ago

This is the workaround I use for this type of scenario: https://drizzle.run/eaos0upq5ce52yuvezbqhg1s

Drizzle Run

undefined - Drizzle Run

Angelelz•3mo ago

You can't have items.rarity in the where of a unboxes table directly. It's basically what you did

MozzyOP•3mo ago

I can't access that link

Angelelz•3mo ago

https://drizzle.run/jq8379x2k0ldobw3gpq34l7h

Drizzle Run

mozzy's issue - Drizzle Run

Angelelz•3mo ago

It's exactly what you did, I had not seen your response by the time I changed it

MozzyOP•3mo ago

Okay thanks drizzle.run is a really cool tool

Angelelz•3mo ago

It was written by one of the advocates One of the reasons is that you could hit performance penalties unless you know what you're doing The relational query API doesn't require foreign keys or indexes to work, but if you don't use them, it will probably be very bad

MozzyOP•3mo ago

The relational query API being findFirst() and findMany()?

Angelelz•3mo ago

Yes

MozzyOP•3mo ago

Is the performance of the workaround similar to the removed feature? or, could-be-performance

Angelelz•3mo ago

I'm not sure, I didn't understand how it worked when it existed But the work around will be fine if you have foreign keys and correct indexes

MozzyOP•3mo ago

Good, I think I do

Gaming

Programming

`Error: no such column: unboxes.rarity` with `findMany()`