[bug] where sub query seems not work

[bingtsingw]

In the docs, This kind of query seems not generate correct sqls

[IlyaSemenov]

@bingtsingw Can you come up with a specific reproduction?

I just took the book/author schema from #213 and the following queries worked fine:

await db.author.insertMany([{ name: "Alice" }, { name: "Bob" }])
await db.book.insertMany([
  { title: "Tome I", authorId: 1 },
  { title: "Tome II", authorId: 1 },
  { title: "My life", authorId: 2 },
])

console.log(await db.author.where((author) => author.books.count().equals(1)))
console.log(await db.author.where((author) => author.books.count().equals(2)))

(0.7ms) SELECT * FROM "author" WHERE (SELECT count(*) = $1 FROM "book" AS "books" WHERE "books"."author_id" = "author"."id") [1]
[ { id: 2, name: 'Bob' } ]
(0.3ms) SELECT * FROM "author" WHERE (SELECT count(*) = $1 FROM "book" AS "books" WHERE "books"."author_id" = "author"."id") [2]
[ { id: 1, name: 'Alice' } ]

[romeerez]

@bingtsingw I also can't reproduce:

code that I tried and it works for me

import { createBaseTable } from './baseTable';
import { orchidORM } from './orm';

const BaseTable = createBaseTable();

class PostTable extends BaseTable {
  readonly table = 'post';
  columns = this.setColumns((t) => ({
    id: t.identity().primaryKey(),
    body: t.text(10, 10000),
    title: t.name('title').string(),
    ...t.timestamps(),
  }));

  relations = {
    postTags: this.hasMany(() => PostTagTable, {
      columns: ['id'],
      references: ['postId'],
    }),
    tags: this.hasMany(() => TagTable, {
      through: 'postTags',
      source: 'tag',
    }),
  };
}

class TagTable extends BaseTable {
  readonly table = 'tag';
  columns = this.setColumns((t) => ({
    tag: t.string().primaryKey(),
  }));
}

class PostTagTable extends BaseTable {
  readonly table = 'postTag';
  columns = this.setColumns((t) => ({
    postId: t.integer().foreignKey(() => PostTable, 'id'),
    tag: t.string().foreignKey(() => TagTable, 'tag'),
    ...t.primaryKey(['postId', 'tag']),
  }));

  relations = {
    tag: this.belongsTo(() => TagTable, {
      columns: ['tag'],
      references: ['tag'],
    }),
  };
}

const db = orchidORM(
  {
    databaseURL: process.env.PG_URL,
  },
  {
    post: PostTable,
    postTag: PostTagTable,
    tag: TagTable,
  },
);

describe('repro', () => {
  it('should work', async () => {
    await db.postTag.all().delete();
    await db.post.all().delete();
    await db.tag.all().delete();

    // first post has tags typescript and node.js
    await db.post.create({
      title: 'Post about typescript and node.js',
      body: 'some content...',
      postTags: {
        create: [
          {
            tag: {
              create: {
                tag: 'typescript',
              },
            },
          },
          {
            tag: {
              create: {
                tag: 'node.js',
              },
            },
          },
        ],
      },
    });

    // second post has tags typescript and frontend
    await db.post.create({
      title: 'Post about typescript for frontend',
      body: 'some content...',
      postTags: {
        create: [
          {
            tag: {
              connect: {
                tag: 'typescript',
              },
            },
          },
          {
            tag: {
              create: {
                tag: 'frontend',
              },
            },
          },
        ],
      },
    });

    const givenTags = ['typescript', 'node.js'];

    const result = await db.post.where((post) =>
      post.tags.whereIn('tag', givenTags).count().equals(givenTags.length),
    );

    console.log(result);
  });
});

Please always share the exact code that doesn't work for you, and the error message.