Create TypeORM query to retrieve all object from one table and add column based on indication from other table (Node/NestJS)

Question

I'm using TypeORM query builder for my backend and I have an issue i will appreciate your help with. I have three data base tables - USERS, ITEMS & USER_FAVORITES_ITEMS (relation table). each USER have MANY favorites items and each item can be FAVORITE to many USERS (Many To Many relation).

Now, the result I'm looking for is to create query that will return all items on the database with extra column called IS_FAV (boolean) -> if the item appears in the USER_FAVORITES_ITEMS for this user I want it to be TRUE, else it should be FALSE.

This is how my MANY To Many table looks like (very simple).

Can you pls. show the expected structure of the result set? And users is the same as tourists, right? — Stefanov.sm
– Stefanov.sm, Commented Jun 29, 2021 at 13:06

ehsan mandegar · Accepted Answer · 2021-06-30 06:36:47Z

0

you should use this:

 let query = this.userRepository
      .createQueryBuilder('ITEMS')
      .leftJoin('ITEMS.USERS', 'USERS')
      .select('ITEMS')
      .addSelect('ITEMS.USERS)', 'USER_FAVORITES_ITEMS');
    const inputs = await query.getRawMany();
inputs.map((item)=>{
if(item.USERS){
return {
...item,
USER_FAVORITES_ITEMS: true,
}
} else {
return {
...item,
USER_FAVORITES_ITEMS: true,
}
}
});

you may change the code to what you want, but you can use this way.

answered Jun 30, 2021 at 6:36

ehsan mandegar

3721 silver badge8 bronze badges

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

2 Comments

Amir Foghel Over a year ago

Hi, thank a lot. I was able to achieve the required result using the 'getRawMany' - BUT I wonder if there is a good way to keep the entity structure (which means not using getRawMany OR something else I didn't think about). any idea ?

ehsan mandegar Over a year ago

i searched for some feature like this, but i was unable, only raw many work, structure is a bit disrupt, if yoy log the result can find how to extract data. let me write a piece of code about how to extract data

ehsan mandegar · Accepted Answer · 2021-07-13 04:37:20Z

0

this is how to derive values from getRawMany():

export default class **** {
  constructor(data: any) {
    const product: Product = {
      createdById: data.product_createdById,
      updatedById: data.product_updatedById,
      productCategory: {
        createdById: data.category_createdById,
        updatedById: data.category_updatedById,
      },
    };
  ********
}

answered Jul 13, 2021 at 4:37

ehsan mandegar

3721 silver badge8 bronze badges

Collectives™ on Stack Overflow

Create TypeORM query to retrieve all object from one table and add column based on indication from other table (Node/NestJS)

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related