Subquery for where in

[Rockson]

Issue type:

[ ] Question [ ] Bug report [X] Feature request [ ] Documentation issue

Database system/driver:

[ ] Postgres [ ] MSSQL [ ] MySQL [ ] MariaDB [ ] SQLite3 [ ] Oracle [ ] Amazon Redshift

typed-knex version:

[X] latest [ ] @next [ ] 0.x.x (or put your version here)

I was looking for subquery for whereIn in the apis but it looks like it's not implemented. There is a whereExist with subquery but why no subquery for where in? Is this a todo or a design decision?

[wwwouter]

Not a design decision :) Will try to find some time to take a look at this coming week.

[Rockson]

Ok I thought it was a design decision because you should be able to do with whereExists everything you can do whereIn(not sure about this anyway). Also this project is the best I found that's pragmatic and really typesafe. It uses knex and just adds typings so it should share the same performance(?).

I have a question that's a bit off topic, did you consider opting out from the relationship graph navigation?

const query = typedKnex
        .query(User)
        .innerJoin(i => i.category)
        .where(i => i.name, "Hejlsberg")
        .select(i => [i.id, i.category.name]);

    const oneUser = await query.getSingle();

    console.log(oneUser.id); // Ok
    console.log(oneUser.category.name); // Ok
    console.log(oneUser.name); // Compilation error

in this example you can find the category inside the user object but it's hard to move the object user around between functions. let's say that i have a function like this:

function chargeUser(user: User, amount: number, paymentService: PaymentService) {
  console.log("charging user: " + user.name )
  paymentService.charge(user.billingInfo.user_billing_id, amount)
}

Here, is the relationship billingInfo loaded or not? So the idea is to opt out from the traverse of relationships and instead keep only the foreign key. Then you would write the same function like:

function chargeUser(user: User, billingInfo: BillingInfo, amount: number, paymentService: PaymentService) {
  console.log("charging user: " + user.name )
  paymentService.charge(billingInfo, amount)
}

This method would just make the library type-safer(imo) by not having relationships that may be loaded or may not. Of course this is not something I came out with, this method comes from scala slick that has an in-dept explanation of this

Like to know your opinion ^^

[wwwouter]

So the way I envision it, is that the User class is a representation of the table. And the table has a reference available to it. In my own code, I never use the User type directly. I use the resulting object, without typing it further, or when I need to pass it to a function I use Pick or I extract the return type from the function returning the result from the query.

So for example using Pick:

 function chargeUser(user: Pick<User, "name">, billingInfo: Pick<BillingInfo, "relevant"|"properties">, amount: number, paymentService: PaymentService) {
  console.log("charging user: " + user.name )
  paymentService.charge(billingInfo, amount)
}

[wwwouter]

Using a query in whereIn is taking a bit more time, because I'm still deciding on how to handle this. Either adding extra functions like whereInQuery or overloading the existing ones.

[Rockson]

Yeah honestly as you said, typescript allows us to do things like that so why not. It may become too verbose to use Pick for example when you have nested relations but yeah, passing query result around should be limited. Thanks for the answer ^^