TypeORM select entity, except some, where id not equal condition

user_5652861 0
Viewed 29338

I Have two entities:

    @Entity()
    export class Point {
    
         @PrimaryGeneratedColumn('uuid')
         id: string;
    
         // some other stuff
 
    }


    @Entity()
    export class Product {
    
         @PrimaryGeneratedColumn('uuid')
         id: string;
    
         @IsOptional()
         @ManyToMany(() => Point)
         @JoinTable()
         prohibitedToSaleOn: Point[];
    
    }

I want to get products, where any object from prohibitedToSaleOn (array of Point's) fulfills the condition

point.id != {idWhatIWant}

So, in final I want to get all product, not banned from sales in selected point. I do something like this:

    return this.productRepository.createQueryBuilder('product')
        .leftJoin('product.prohibitedToSaleOn', 'point')
        .where('point.id != :id', {id})
        .getMany();

But it doesn't work (it should not at all)

I need help with the right request. Thanks =)

P.S. I use PostgreSQL

typeorm typescript
2 Answers

I'm not sure if you need to use query builder. Here you can find the description of the alternative way to write joins using relations.

To filter them by id not being equal to the one you provide (.where('point.id != :id', {id})), you can write something like find({where: {id: Not(id)}}), where Not is imported from TypeORM.

user_40830450

try to move WHERE condition in to join level

return this.productRepository.createQueryBuilder('product')
        .leftJoin('product.prohibitedToSaleOn', 'point', 'point.id != :id', {id})
        .getMany();

this query should return all products contained in prohibitedToSaleOn join table except for the specified point id.

If you need products that are not banned from sales at the selected point and products that have never been banned, you need query like this:

return this.productRepository.createQueryBuilder('product')
        .leftJoin('product.prohibitedToSaleOn', 'point', '(point.id != :id OR point.id IS NULL)', {id})
        .getMany();
user_90189460
Related