Select from subquery

[dariusmb]

Issue type:

[x] question
[ ] bug report
[ ] feature request
[ ] documentation issue

Database system/driver:

[ ] cordova
[ ] mongodb
[ ] mssql
[x] mysql / mariadb
[ ] oracle
[ ] postgres
[ ] sqlite
[ ] sqljs
[ ] react-native
[ ] expo

TypeORM version:

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

Steps to reproduce or a small repository showing the problem:
How can I make the following select
SELECT * FROM post_image JOIN (SELECT * FROM post LIMIT 2) as posts_shown ON post_image.post_id = posts_shown.id;

[pleerock]

Checkout this example on advanced query builder usage.

[skylight-ity]

still no response. How would you wring something like this in typeorm
select * from (select price,name,brand,market_name,image,type,category,price,give_amount,date,suggested_price,floor,exterior,float,by, RANK() OVER(ORDER BY price) from inventory) as s;

[skylight-ity]

@pleerock Chihel menavesem aka?

[chriszrc]

@skylight-ity Subqueries have been added to the documentation there:

https://typeorm.io/#/select-query-builder/using-subqueries

You would do it like:

const inventory = await connection
    .createQueryBuilder()
    .select("i.*")
    .from(subQuery => {
        return subQuery
            .select("inventory.*")
            //This works for aggregate operators like sum(), I'm guessing this should work for rank()
            .addSelect("Rank() over(order by price)", "rnk")
            .from(Inventory, "inventory")
    }, "i")
    .getRawMany();

[serg06]

@chriszrc Thank you!

Is there any way to do the same thing for .update()?

The best I was able to find was injecting the .getQuery() into .update.

[chriszrc]

@serg06 No idea, sorry. I would probably do a raw sql query in that case-

[weilinzung]

@skylight-ity Subqueries have been added to the documentation there:

https://typeorm.io/#/select-query-builder/using-subqueries

You would do it like:

How to subselect multiple fields? 'Operand should contain 1 column(s)'

const posts = await dataSource
    .createQueryBuilder()
    .select("post.id", "id")
    .addSelect((subQuery) => {
        return subQuery.select("user.name", "name").addSelect("user.id", "id").from(User, "user")
    }, "name")
    .from(Post, "post")
    .getRawMany()