Allow query to explicitly join tables so as to allow for filtering based on tables not included in output data

[rmtmckenzie]

I originally posted this in the V2 discussion but since it's out of scope I'm making a new discussion. See #2316 (comment)

---

In my database, I use relations with a primary key that I treat as an implementation detail and have a separate public ID that is used externally (very handy for migrating database providers which have different key requirements). I'm not sure it'd work at all with the new syntax proposed in #2316, but rather than doing extra database calls to get the public id -> internal, it'd be awesome to be able to do something like this:

const columns = [ ... ]; 
const withMap = { ... }; 

db.query.purchaseFeedbacks.findFirst({
  columns,
  with: withMap,
  where: and(eq(receipts.publicId, receiptPublicId), eq(users.publicId, userPublicId)),
  joins: [
    { table: receipts, on: eq(purchaseFeedbacks.receiptId, receipts.id), type: 'inner' },
    { table: users, on: eq(receipts.userId, users.id), type: 'inner' },
  ],
});

Being able to do this with a query rather than the more sql-like syntax makes implementation much simpler as the columns & withMap are parsed from a graphql request - this same operation would take at least a few hundred lines of code if I had to write it all out more explicitly.

The idea of what I've done above is that tables which are specified in the joins are not read from directly but rather used exclusively for the where clause as any data being read is specified in the columns/with. There may be a more elegant way to do this, but it's what I came up with.

I've been using a very basic implementation of this in a fork (https://github.com/rmtmckenzie/drizzle-orm/tree/query-joins-v2) but it's definitely not production ready and only implemented for mysql and postgres. It seems like the proposed changes in #2316 will make this less possible, but if this sparks any ideas I'd appreciate it being considered. (and feel free to lift any code in that branch if it's at all helpful).