CODEWITHSUNDEEP
mysqlsqlbackendnestjstypeorm
Francesco Baholli
Francesco Baholli

Reputation: 11

Getting all data and only filtered relations

I have a table of products which is related to a pricing table, ideally I would want to get an array of all the products and those who have a pricing relation with the user be associated with the product.

 return this.repo
        .createQueryBuilder("product")
        .leftJoinAndSelect("product.pricings", "pricings")
        .leftJoinAndSelect("pricings.driver", "driver")
        .where("pricings.driver.id = :id", { id: 1 })
        .getMany()

This returns an array of the products which have the aforementioned relation, I would want all the products, even those who don't have a relation with pricing.

Upvotes: 0

Views: 82

Answers (1)

Francesco Baholli
Francesco Baholli

Reputation: 11

         SELECT  product.id, product.price,
            product.name, pricing.driverId, pricing.alteredPrice
            FROM    product   
            LEFT JOIN pricing 
                ON product.id = pricing.productId AND
                    pricing.driverId = '1'
            ORDER   BY product.id

This apparently does the trick

For typeorm the equivalent is:

          this.repo
                .createQueryBuilder('product')
                .leftJoinAndSelect("product.pricings", "pricing", "pricing.driverId = :driverId", { driverId })
                .select('product.id')
                .addSelect('product.price')
                .addSelect('product.name')
                .addSelect('product.saleType')
                .addSelect('pricing.driverId')
                .addSelect('pricing.alteredPrice')
                .addSelect('pricing.id')
                .orderBy("product.id")
                .getMany()

Upvotes: 1

Related Questions