[BUG]: RQB-V2 creates low performing queries in MySQL and Postgres

### Report hasn't been filed before.

- [x] I have verified that the bug I'm about to report hasn't been filed before.

### What version of `drizzle-orm` are you using?

v1:beta8

### What version of `drizzle-kit` are you using?

v1:beta8

### Other packages

_No response_

### Describe the Bug

RQB-V2 generates a very low performing query if including relatinoships in the query, causing performance issues on the database if it's used often enough.

The result of the operation itself is correct, it's just that the query being generated and executed is terrible and literally kills our database after a few hours.

## Scenario

Here's the scenario: 2 tables, related to each other (one-to-many from orders to orderItems), and a relatively simple query:

```typescript
// table definitions
export const orders = table("orders", {
id: int({ unsigned: true }).notNull().primaryKey().autoincrement(),
catalog_id: int({ unsigned: true }).notNull().default(0),
created_at: timestamp("created_at", { mode: "date", fsp: 3 }).notNull().default(sql`CURRENT_TIMESTAMP(3)`),
},
(table) => [
t.index("orders_created_at").on(table.created_at),
t.index("orders_catalog_id").on(table.catalog_id)
])

export const orderItems = table("orderitems", {
id: int({ unsigned: true }).notNull().primaryKey().autoincrement(),
catalog_id: int({ unsigned: true }).notNull().default(0),
order_id: t.int({ unsigned: true }).notNull(),
sku: varchar({ length: 255 }).default('').notNull()
}, (table) => [
t.index("orderitems_order_id").on(table.order_id),
t.index("orderitems_catalog_id").on(table.catalog_id),
t.index("orderitems_sku").on(table.sku),
t.primaryKey({ columns: [table.id] }),
]);

// relations

export const relations = defineRelations(schema, (r) => ({
orders: {
orderItems: r.many.orderItems({
from: r.orders.id,
to: r.orderItems.order_id,
})
},
orderItems: {
order: r.one.orders({
from: r.orderItems.order_id,
to: r.orders.id,
})
}
}));

// the query

const filteredOrders = await db.query.orders.findMany({
where: {
catalog_id: 195,
orderItems: {
sku: {
in: ['123', '456']
}
}
},
with: {
orderItems: true
},
orderBy: {
id: 'desc'
}
})

```

## Generated Query

```sql
select
`d0`.`id` as `id`,
`d0`.`catalog_id` as `catalog_id`,
`d0`.`created_at` as `created_at`,
`orderItems`.`r` as `orderItems`
from
`orders` as `d0`
left join lateral(select coalesce(json_arrayagg(json_object('id', `id`, 'sku', `sku`, 'catalog_id', `catalog_id`, 'order_id', `order_id`)), json_array()) as `r` from (select `d1`.`id` as `id`, `d1`.`sku` as `sku`, `d1`.`catalog_id` as `catalog_id`, `d1`.`order_id` as `order_id` from `orderitems` as `d1` where `d0`.`id` = `d1`.`order_id`) as `t`) as `orderItems` on
true
where
(`d0`.`catalog_id` = 195
and exists (
select
*
from
`orderitems` as `f0`
where
(`d0`.`id` = `f0`.`order_id`
and `f0`.`sku` in ('123','456'))
limit 1))
order by
`d0`.`id` desc
limit 25 offset 0;
```

The query above misuses indexes and forces terrible scanning practices at the DB level.

## What's expected

The expectation is that RQB-v2 creates the best performing query possible taking into account these limitations. Which in this and all cases where there's a need for a "join lateral" with a generated table seems to be the creation of a CTE with the conditions of the where, which is then queried and joined.

Something like has the best possible performance

```sql
with `orders_cte` as (
select
`d0`.`id` as `id`
from
`orders` as `d0`
where
(`d0`.`catalog_id` = 195
and exists (
select
*
from
`orderitems` as `f0`
where
(`d0`.`id` = `f0`.`order_id`
and `f0`.`sku` in ('123', '456'))
limit 1))
order by
`d0`.`id` desc
limit 25 offset 0
)
select
`d0`.`id` as `id`,
`orderItems`.`r` as `orderItems`
from
`orders_cte` as `d0`
left join lateral(select coalesce(json_arrayagg(json_object('id', `id`, 'sku', `sku`, 'catalog_id', `catalog_id`, 'order_id', `order_id`)), json_array()) as `r` from (select `d1`.`id` as `id`, `d1`.`sku` as `sku`, `d1`.`catalog_id` as `catalog_id`, `d1`.`order_id` as `order_id` from `orderitems` as `d1` where `d0`.`id` = `d1`.`order_id`) as `t`) as `orderItems` on
true
```