leftJoinAndSelect with sub-query #4429
Labels
enhancement
New feature or request
Milestone
Comments
|
You can use the last - 4th, not 3rd - parameter for partial loading: The third one is the join conditions object. This won't use a subquery, not sure why you would want that? edit: oh I see, you want to limit the number of results, not the fields |
|
Sometimes we want to perform a conditional join by not only comparing the ids, but also other columns. A subquery could work on this scenario and even be more flexible. So I would also consider support for specifying more conditions (at least the root columns of the related table) on the .leftJoinAndSelect('property', 'alias', { <extra conditions> }), because right now it only allow the $operators and they are compared to the PK of the related table only. Example: LEFT JOIN product p ON shows.id = p.show_id and p.enabled = true, this is because sometimes we cannot just use this condition on the WHERE part of the query. Of course, if subqueries were supported (and I think it should be because, in my opinion, query builder should be able use almost all SQL capabilities with exception of edge cases), this could be rewritten as using a subquery. TypeORM, for comparison, supports subqueries on joins, so this could be implemented in MikroORM as well and it makes their query builder much more flexible. This is something I think MikroORM should focus on improving in the near future. |
|
While I understand what you want, I dont see how it could work when it comes to entity mapping (which the leftJoinAndSelect method is for). We can surely support any query for |
B4nan
added a commit
that referenced
this issue
Sep 26, 2023
## Joining sub-queries
Sometimes you might want to join a relation, but want to have more
control over the query. The ORM allows you to override the join target
with a sub-query, while keeping the original metadata for hydration:
```ts
// subquery can be a knex query builder as well
const subquery = await em.createQueryBuilder(Book, 'b')
.where({ ... })
.orderBy({ title: 'asc' }).limit(1);
const authors = await em.createQueryBuilder(Author, 'a')
.select('*')
// pass in both the property path and the subquery into the first argument as a tuple
.leftJoinAndSelect(['a.books', subquery], 'b')
// you can join more relations on top of the subquery join
.leftJoinAndSelect('b.tags', 't')
.getResultList();
```
This will produce query similar to the following:
```sql
select `a`.*,
`b`.`id` as `b__id`, `b`.`title` as `b__title`, `b`.`author_id` as `b__author_id`, `b`.`publisher_id` as `b__publisher_id`,
`t`.`id` as `t__id`, `t`.`name` as `t__name`
from `author` as `a`
left join (
select `b`.*, `b`.price * 1.19 as `price_taxed`
from `book` as `b`
order by `b`.`title` asc
limit 1
) as `b` on `b`.`author_id` = `a`.`id`
left join `book_tags` as `e1` on `b`.`uuid_pk` = `e1`.`book_uuid_pk`
left join `book_tag` as `t` on `e1`.`book_tag_id` = `t`.`id`
```
Closes #4429
Closes #4549
|
Closing as implemented in v6 via #4747 |
B4nan
added a commit
that referenced
this issue
Sep 30, 2023
Sometimes you might want to join a relation, but want to have more
control over the query. The ORM allows you to override the join target
with a sub-query, while keeping the original metadata for hydration:
```ts
// subquery can be a knex query builder as well
const subquery = await em.createQueryBuilder(Book, 'b')
.where({ ... })
.orderBy({ title: 'asc' }).limit(1);
const authors = await em.createQueryBuilder(Author, 'a')
.select('*')
// pass in both the property path and the subquery into the first argument as a tuple
.leftJoinAndSelect(['a.books', subquery], 'b')
// you can join more relations on top of the subquery join
.leftJoinAndSelect('b.tags', 't')
.getResultList();
```
This will produce query similar to the following:
```sql
select `a`.*,
`b`.`id` as `b__id`, `b`.`title` as `b__title`, `b`.`author_id` as `b__author_id`, `b`.`publisher_id` as `b__publisher_id`,
`t`.`id` as `t__id`, `t`.`name` as `t__name`
from `author` as `a`
left join (
select `b`.*, `b`.price * 1.19 as `price_taxed`
from `book` as `b`
order by `b`.`title` asc
limit 1
) as `b` on `b`.`author_id` = `a`.`id`
left join `book_tags` as `e1` on `b`.`uuid_pk` = `e1`.`book_uuid_pk`
left join `book_tag` as `t` on `e1`.`book_tag_id` = `t`.`id`
```
Closes #4429
Closes #4549
B4nan
added a commit
that referenced
this issue
Oct 2, 2023
Sometimes you might want to join a relation, but want to have more
control over the query. The ORM allows you to override the join target
with a sub-query, while keeping the original metadata for hydration:
```ts
// subquery can be a knex query builder as well
const subquery = await em.createQueryBuilder(Book, 'b')
.where({ ... })
.orderBy({ title: 'asc' }).limit(1);
const authors = await em.createQueryBuilder(Author, 'a')
.select('*')
// pass in both the property path and the subquery into the first argument as a tuple
.leftJoinAndSelect(['a.books', subquery], 'b')
// you can join more relations on top of the subquery join
.leftJoinAndSelect('b.tags', 't')
.getResultList();
```
This will produce query similar to the following:
```sql
select `a`.*,
`b`.`id` as `b__id`, `b`.`title` as `b__title`, `b`.`author_id` as `b__author_id`, `b`.`publisher_id` as `b__publisher_id`,
`t`.`id` as `t__id`, `t`.`name` as `t__name`
from `author` as `a`
left join (
select `b`.*, `b`.price * 1.19 as `price_taxed`
from `book` as `b`
order by `b`.`title` asc
limit 1
) as `b` on `b`.`author_id` = `a`.`id`
left join `book_tags` as `e1` on `b`.`uuid_pk` = `e1`.`book_uuid_pk`
left join `book_tag` as `t` on `e1`.`book_tag_id` = `t`.`id`
```
Closes #4429
Closes #4549
B4nan
added a commit
that referenced
this issue
Oct 17, 2023
Sometimes you might want to join a relation, but want to have more
control over the query. The ORM allows you to override the join target
with a sub-query, while keeping the original metadata for hydration:
```ts
// subquery can be a knex query builder as well
const subquery = await em.createQueryBuilder(Book, 'b')
.where({ ... })
.orderBy({ title: 'asc' }).limit(1);
const authors = await em.createQueryBuilder(Author, 'a')
.select('*')
// pass in both the property path and the subquery into the first argument as a tuple
.leftJoinAndSelect(['a.books', subquery], 'b')
// you can join more relations on top of the subquery join
.leftJoinAndSelect('b.tags', 't')
.getResultList();
```
This will produce query similar to the following:
```sql
select `a`.*,
`b`.`id` as `b__id`, `b`.`title` as `b__title`, `b`.`author_id` as `b__author_id`, `b`.`publisher_id` as `b__publisher_id`,
`t`.`id` as `t__id`, `t`.`name` as `t__name`
from `author` as `a`
left join (
select `b`.*, `b`.price * 1.19 as `price_taxed`
from `book` as `b`
order by `b`.`title` asc
limit 1
) as `b` on `b`.`author_id` = `a`.`id`
left join `book_tags` as `e1` on `b`.`uuid_pk` = `e1`.`book_uuid_pk`
left join `book_tag` as `t` on `e1`.`book_tag_id` = `t`.`id`
```
Closes #4429
Closes #4549
B4nan
added a commit
that referenced
this issue
Oct 21, 2023
Sometimes you might want to join a relation, but want to have more
control over the query. The ORM allows you to override the join target
with a sub-query, while keeping the original metadata for hydration:
```ts
// subquery can be a knex query builder as well
const subquery = await em.createQueryBuilder(Book, 'b')
.where({ ... })
.orderBy({ title: 'asc' }).limit(1);
const authors = await em.createQueryBuilder(Author, 'a')
.select('*')
// pass in both the property path and the subquery into the first argument as a tuple
.leftJoinAndSelect(['a.books', subquery], 'b')
// you can join more relations on top of the subquery join
.leftJoinAndSelect('b.tags', 't')
.getResultList();
```
This will produce query similar to the following:
```sql
select `a`.*,
`b`.`id` as `b__id`, `b`.`title` as `b__title`, `b`.`author_id` as `b__author_id`, `b`.`publisher_id` as `b__publisher_id`,
`t`.`id` as `t__id`, `t`.`name` as `t__name`
from `author` as `a`
left join (
select `b`.*, `b`.price * 1.19 as `price_taxed`
from `book` as `b`
order by `b`.`title` asc
limit 1
) as `b` on `b`.`author_id` = `a`.`id`
left join `book_tags` as `e1` on `b`.`uuid_pk` = `e1`.`book_uuid_pk`
left join `book_tag` as `t` on `e1`.`book_tag_id` = `t`.`id`
```
Closes #4429
Closes #4549
B4nan
added a commit
that referenced
this issue
Oct 25, 2023
Sometimes you might want to join a relation, but want to have more
control over the query. The ORM allows you to override the join target
with a sub-query, while keeping the original metadata for hydration:
```ts
// subquery can be a knex query builder as well
const subquery = await em.createQueryBuilder(Book, 'b')
.where({ ... })
.orderBy({ title: 'asc' }).limit(1);
const authors = await em.createQueryBuilder(Author, 'a')
.select('*')
// pass in both the property path and the subquery into the first argument as a tuple
.leftJoinAndSelect(['a.books', subquery], 'b')
// you can join more relations on top of the subquery join
.leftJoinAndSelect('b.tags', 't')
.getResultList();
```
This will produce query similar to the following:
```sql
select `a`.*,
`b`.`id` as `b__id`, `b`.`title` as `b__title`, `b`.`author_id` as `b__author_id`, `b`.`publisher_id` as `b__publisher_id`,
`t`.`id` as `t__id`, `t`.`name` as `t__name`
from `author` as `a`
left join (
select `b`.*, `b`.price * 1.19 as `price_taxed`
from `book` as `b`
order by `b`.`title` asc
limit 1
) as `b` on `b`.`author_id` = `a`.`id`
left join `book_tags` as `e1` on `b`.`uuid_pk` = `e1`.`book_uuid_pk`
left join `book_tag` as `t` on `e1`.`book_tag_id` = `t`.`id`
```
Closes #4429
Closes #4549
B4nan
added a commit
that referenced
this issue
Nov 2, 2023
Sometimes you might want to join a relation, but want to have more
control over the query. The ORM allows you to override the join target
with a sub-query, while keeping the original metadata for hydration:
```ts
// subquery can be a knex query builder as well
const subquery = await em.createQueryBuilder(Book, 'b')
.where({ ... })
.orderBy({ title: 'asc' }).limit(1);
const authors = await em.createQueryBuilder(Author, 'a')
.select('*')
// pass in both the property path and the subquery into the first argument as a tuple
.leftJoinAndSelect(['a.books', subquery], 'b')
// you can join more relations on top of the subquery join
.leftJoinAndSelect('b.tags', 't')
.getResultList();
```
This will produce query similar to the following:
```sql
select `a`.*,
`b`.`id` as `b__id`, `b`.`title` as `b__title`, `b`.`author_id` as `b__author_id`, `b`.`publisher_id` as `b__publisher_id`,
`t`.`id` as `t__id`, `t`.`name` as `t__name`
from `author` as `a`
left join (
select `b`.*, `b`.price * 1.19 as `price_taxed`
from `book` as `b`
order by `b`.`title` asc
limit 1
) as `b` on `b`.`author_id` = `a`.`id`
left join `book_tags` as `e1` on `b`.`uuid_pk` = `e1`.`book_uuid_pk`
left join `book_tag` as `t` on `e1`.`book_tag_id` = `t`.`id`
```
Closes #4429
Closes #4549
B4nan
added a commit
that referenced
this issue
Nov 5, 2023
Sometimes you might want to join a relation, but want to have more
control over the query. The ORM allows you to override the join target
with a sub-query, while keeping the original metadata for hydration:
```ts
// subquery can be a knex query builder as well
const subquery = await em.createQueryBuilder(Book, 'b')
.where({ ... })
.orderBy({ title: 'asc' }).limit(1);
const authors = await em.createQueryBuilder(Author, 'a')
.select('*')
// pass in both the property path and the subquery into the first argument as a tuple
.leftJoinAndSelect(['a.books', subquery], 'b')
// you can join more relations on top of the subquery join
.leftJoinAndSelect('b.tags', 't')
.getResultList();
```
This will produce query similar to the following:
```sql
select `a`.*,
`b`.`id` as `b__id`, `b`.`title` as `b__title`, `b`.`author_id` as `b__author_id`, `b`.`publisher_id` as `b__publisher_id`,
`t`.`id` as `t__id`, `t`.`name` as `t__name`
from `author` as `a`
left join (
select `b`.*, `b`.price * 1.19 as `price_taxed`
from `book` as `b`
order by `b`.`title` asc
limit 1
) as `b` on `b`.`author_id` = `a`.`id`
left join `book_tags` as `e1` on `b`.`uuid_pk` = `e1`.`book_uuid_pk`
left join `book_tag` as `t` on `e1`.`book_tag_id` = `t`.`id`
```
Closes #4429
Closes #4549
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
Hi my question is related to qb leftJoinAndSelect. It returns all the associated data. But I need to use a subquery, something like this.
the table that leftJoinAndSelect joins is too large and I just need to get one record from there, but leftJoinAndSelect joins everything at once. How can I do this with queryBuilder?
leftJoinAndSelect also has a third parameter, I thought maybe this is what me need. But it’s not at all clear what it does, it’s not described in the documentation, and when I applied it, the result is just very strange
The text was updated successfully, but these errors were encountered: