Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

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

like image 232
Владимир Михайлов Avatar asked Feb 21 '20 10:02

Владимир Михайлов


Video Answer


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.

like image 61
Vladyslav Zavalykhatko Avatar answered Oct 26 '22 18:10

Vladyslav Zavalykhatko


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();
like image 25
D.Zotov Avatar answered Oct 26 '22 19:10

D.Zotov