[FEATURE]: Joins relational queries with select APIs

[zoriya]

Feature hasn't been suggested before.

• I have verified this feature I'm about to request hasn't been suggested before.

Describe the enhancement you want to request

I have a query that would be way easier with the query api (something like this example bellow):

db.query.movies.findFirst({
	where: eq(movies.id, 12),
	with: {
		studio: true,
		staff: true,
	},
})

but i need this query to be joined to another table with a manual subquery. This prevents me from using the query api completly. I'd love to be able to do something like:

db.query.movies.findFirst({
	where: eq(movies.id, 12),
	join: {
		translation: leftJoin(
			// a complicated query that we don't want to handle with the simples filters available in `with`.
			db.select().from(movieTranslations).where(...).leftJoin(...),
			eq(movies.id, movieTranslations.id)
		),
	},
	with: {
		studio: true,
		staff: true,
	},
})

This would make a sql like this:

select *, translation.* from movies
# the complicated query
left join (select * from "moviesTranslation" ...) as translation on movies.id == translation.id
# the other joins needed for with
left join lateral (select json(...) from studio) on true
...

Since optional joins (#1533) are not available in drizzle right now, making something similar with the query builder is way less plesant than this query api.