Why my "with" many-to-many query don't work as expected, but return all items instead?

[brachkow]

Imagine that I have an Item and Collection:

export const itemsTable = sqliteTable('items', {
  id: integer('id', { mode: 'number' }).primaryKey({ autoIncrement: true }),
  name: text('name').notNull(),
});

export const collectionsTable = sqliteTable('collections', {
  id: integer('id', { mode: 'number' }).primaryKey({ autoIncrement: true }),
  name: text('name').notNull(),
});

I want to put items in collections, so I created a table to store the relations between them:

export const collectionsThingsTable = sqliteTable(
  'collections_items',
  {
    id: integer('id', { mode: 'number' }).primaryKey({ autoIncrement: true }),
    collectionId: integer('collection_id', { mode: 'number' })
      .notNull()
      .references(() => collectionsTable.id, { onDelete: 'cascade' }),
    itemId: integer('item_id', { mode: 'number' })
      .notNull()
      .references(() => itemsTable.id, { onDelete: 'cascade' }),
  }
);

export const itemRelations = relations(itemsTable, ({ many }) => ({
  collections: many(collectionsThingsTable),
}));

export const collectionsRelations = relations(collectionsTable, ({ many }) => ({
  items: many(collectionsThingsTable),
}));

export const collectionsThingsRelations = relations(collectionsThingsTable, ({ one }) => ({
  collection: one(collectionsTable, {
    fields: [collectionsThingsTable.collectionId],
    references: [collectionsTable.id],
  }),
  item: one(itemsTable, {
    fields: [collectionsThingsTable.itemId],
    references: [itemsTable.id],
  }),
}));

Then I want to do three queries:

1. Get items that assigned to some collection

await db.query.itemsTable.findMany({
  with: { collections: true },
});

2. Get items that not assigned to any collection

await db.query.itemsTable.findMany({
  with: { collections: false },
});

3. Get items that assigned to specific collection

await db.query.itemsTable.findMany({
  with: { collections: { where: eq(collectionsThingsTable.collectionId, collection) } },
});

Surprisingly all three snippets, just return all items. What's also interesting second snippet says that value can't be falsy. I can't understand why it is so.

[brachkow]

After having a good sleep, I came to realization that with just joins tables while I thought it will filter query by having or not having described relationship.

https://orm.drizzle.team/docs/rqb#include-relations

Included in collections [
  { id: 1, name: 'item 1', collections: [ [Object] ] },
  { id: 2, name: 'item 2', collections: [ [Object] ] },
  { id: 3, name: 'item 3', collections: [] }
]
Not included in collections [
  { id: 1, name: 'item 1' },
  { id: 2, name: 'item 2' },
  { id: 3, name: 'item 3' }
]
Included in specific collections [
  { id: 1, name: 'item 1', collections: [ [Object] ] },
  { id: 2, name: 'item 2', collections: [] },
  { id: 3, name: 'item 3', collections: [] }
]