TypeOrm - NestJS using queryBuilder

Question

I have this db schema in mysql:

     users_tbl
     --------------------------------------------
     id        |  first_name         | last_name
     --------------------------------------------
     1         |  Jon                | Doe
     2         |  Mark               | Smith


     address_tbl
     ------------------------------------------------------------
     id        |  address            | city             | user_id
     ------------------------------------------------------------
     1         |  some address       | some city        | 1

Then, I wanted to use TypeOrm's queryBuilder inorder to get the address of Jon Doe.

My raw sql query: SELECT users.first_name, users.last_name, address.address, address.city FROM users INNER JOIN address ON address.user_id=users.id

My TypeOrm queryBuilder:

const users = await this.userRepo
      .createQueryBuilder('users')
      .select('users.first_name', 'fName')
      .addSelect('users.last_name', 'lName')
      .addSelect('adr.address', 'address')
      .addSelect('adr.city', 'city')
      .innerJoin('address', 'adr', 'adr.user_id=users.id')
      .getMany();
return users;

There doesn't seem to have any error as I am able to run a GET request. However, it returns an empty object. In my console, NestJS (or TypeORM) logs the generated mysql query. Here it is:

SELECT adr.city AS city, adr.country AS country, adr.id AS adr_id, users.first_name AS fName, users.last_name AS lName FROM users users INNER JOIN address adr ON adr.user_id = users.id

I copied and pasted it to perform a manual query in phpmyadmin. And the query seems to work, and gives me the expected output. Am I missing something in my code, or is this some TypeORM limitation?

Can you share you controller method?

Ayoub Touba
– Ayoub Touba

2021-01-24 18:28:09 +00:00
Commented Jan 24, 2021 at 18:28 — Ayoub Touba
– Ayoub Touba, Commented Jan 24, 2021 at 18:28

Dharman · Accepted Answer · 2021-01-25 10:00:37Z

5

Try using getRawMany instead of getMany as follows -

return await this.userRepo
      .createQueryBuilder('users')
      .select('users.first_name', 'fName')
      .addSelect('users.last_name', 'lName')
      .addSelect('adr.address', 'address')
      .addSelect('adr.city', 'city')
      .innerJoin('address', 'adr', 'adr.user_id=users.id')
      .printSql() 
      .getRawMany();

edited Jan 25, 2021 at 10:00

Dharman♦

33.9k27 gold badges105 silver badges157 bronze badges

answered Jan 25, 2021 at 9:55

Soham Lawar

1,2751 gold badge13 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

TypeOrm - NestJS using queryBuilder

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related