Rewrite raw PostgreSQL query to use SelectQueryBuilder from TypeORM

Question

I've written this query that fetches user ids (that's for now, cos I actually need way more fields from the user table as well as from another table called image that is related the user table).

The problem with this query is that it returns a plain object and I need an entity object, I mean I know I could just deserialise it to whatever model I need, but the thing also is that I normally deserialise entity to a required response model. Also, I would like to avoid making a couple of requests: one fetching user ids and the other fetching right entity objects by those ids using queryBuilder.

So, it seems that one possible solution would be to rewrite this query to make use of queryBuilder straight away.

        const matchedUsers = await this.usersRepository.query(
            `
                SELECT id FROM users
                WHERE id IN (
                    SELECT "usersId" FROM locations_available_fighters_users
                    WHERE "locationsId" IN (
                        SELECT "locationsId" FROM locations_available_fighters_users
                        WHERE "usersId" = ${ me.getId() }
                    )
                ) AND is_active IS TRUE
                  AND id != ${ me.getId() }
                  AND weight = '${ me.getWeight() }'
                  AND gender = '${ me.getGender() }'
                  AND role_name = '${ me.getRoleName() }';
            `
        );

Answer 1

If the problems you're trying to solve are:

• Use a single query
• Get the returned data as entity objects rather than raw results
• Use QueryBuilder to avoid writing raw queries

I believe what you should be looking into is typeorm subqueries.

For the query you posted in the question, you can try something like below using QueryBuilder:

// Subquery for your inner most subquery,
const locationsQb = connection.getRepository(LocationsAvailableFightersUser)
    .createQueryBuilder("lafu_1")
    .select("lafu_1.locationsId")
    .where("lafu_1.usersId = :usersID", { usersID: me.getId() });

// Subquery for your middle subquery,
const usersQb = connection.getRepository(LocationsAvailableFightersUser)
    .createQueryBuilder("lafu_2")
    .select("lafu_2.usersId")
    .where("lafu_2.locationsId IN (" + locationsQb.getQuery() + ")");

// Query for retrieving `User` entities as you needed,
const matchedUsers = await connection.getRepository(User)
    .createQueryBuilder("user")
    .where("user.id IN (" + usersQb.getQuery() + ")")
    .setParameters(locationsQb.getParameters())
    .getMany();

Here I assumed that,

• LocationsAvailableFightersUser is the entity for locations_available_fighters_users table
• User is the entity for users table

Hope this helps you. Cheers 🍻 !!!