[ask] subquery relation query problem

[bingtsingw]

I have three tables, user, branch and branchMember

export class TableBranch extends BaseTable {
  readonly table = 'branch';

  columns = this.setColumns((t) => ({
    id: t.varchar(),
  }));

  relations = {
    members: this.hasMany(() => TableBranchMember, {
      required: false,
      primaryKey: 'id',
      foreignKey: 'branchId',
    }),
  };
}

export class TableBranchMember extends BaseTable {
  readonly table = 'branch_member';

  columns = this.setColumns((t) => ({
    id: t.varchar(),
    branchId: t.varchar(),
    userId: t.varchar(),
  }));

  relations = {
    branch: this.belongsTo(() => TableBranch, {
      required: false,
      primaryKey: 'id',
      foreignKey: 'branchId',
    }),

    user: this.belongsTo(() => TableUser, {
      required: false,
      primaryKey: 'id',
      foreignKey: 'userId',
    }),
  };
}

export class TableUser extends BaseTable {
  readonly table = 'user';

  columns = this.setColumns((t) => ({
    id: t.varchar(),
    signup: t.boolean().default(false),
  }));

  relations = {
    branchMember: this.hasOne(() => TableBranchMember, {
      required: false,
      primaryKey: 'id',
      foreignKey: 'userId',
    }),
  };
}

How can I do this kind of query:

await db.branch.select({
  membersCount: (q) =>
    q.members
      .where((q) =>
        q.user.where({
          signup: true,
        }),
      )
      .count(),
});

It show an error: column members.signup does not exist

[romeerez]

It seems to be a bug, temporary you can try signup: true -> 'user.signup': true

[romeerez]

Maybe it's not a bug, and I still need to handle this case.

Looks like the members.where should be a members.whereExists:

await db.branch.select({
  membersCount: (q) =>
    q.members
      .whereExists((q) =>
        q.user.where({
          signup: true,
        }),
      )
      .count(),
});

But this way of doing this is not documented and may not work, I'll look into this in near days.

[bingtsingw]

I tried whereExists, it throws a typescript error.

[bingtsingw]

It seems to be a bug, temporary you can try signup: true -> 'user.signup': true

'user.signup': true does not work, it need a join() method , but join in subquery has different meanings.

[bingtsingw]

In fact, I found a way to make it work

  q.members.user
    .where({
      signup: true,
    })
    .count(),

But I have another case, it needs a subquery relation:

  lastMonthActivitiesCount: (q) =>
    q.activities
      .where((q) => q.members.count().gte(4)) // this line do not work as expected
      .where({
        state: 'Finish',
        timeStart: { gt: subMonths(startOfMonth(new Date()), 1), lt: startOfMonth(new Date()) },
      })
      .count(),

[bingtsingw]

Hi, any progress on this?

[romeerez]

Hey @bingtsingw, sorry that I have been busy lately.

Maybe there is a way I can improve it, but, I don't see what's wrong with your query.

You copied a part of query and added comment "this line do not work as expected". I don't know what do you expect, and how it works for you, and I don't have a full query.

I created tables "some" (for the top level table which is missing in your code sample), activities belongs to "some", and "members" belongs to activities:

db.some.select({
  lastMonthActivitiesCount: (q) =>
    q.activities
      .where((q) => q.members.count().gte(4))
      .where({
        state: "Finish",
        timeStart: {
          gt: subMonths(startOfMonth(new Date()), 1),
          lt: startOfMonth(new Date()),
        },
      })
      .count(),
});

This query is selecting activities count with status "Finish" with timeStart in a given range, and having at least 4 members. It works for me, seems fine.

[bingtsingw]

I'll check it later and provide a reproduction repo.

[bingtsingw]

@romeerez Hi, I create a reproduction repo, the issue test should be passed.

https://github.com/bingtsingw/orchid-reproduction

[romeerez]

Fixed!

Nice repro, the test makes it very clear what's going on, and happy to see that the ORM works fine with bun.

After updating to the latest version, both tests are passing.

As a side note, whereExists is a good tool to use instead of count().gte(1):

const data = await db.city.select({
  activityCount: (q) =>
    q.activities
      .whereExists('members') // practically same as .where((q) => q.members.count().gte(1))
      .count(),
});

whereExists is more efficient because it doesn't have to count records. But if you'd want to query activities with at least 2 or more members, count().gte(x) is the way.

[bingtsingw]

Thank you for quick fix and helpful advice, I have run orchid with bun and hono for a few months, works quite well.