Typeorm add select distinct in my query. I dont want select distinct. Help!

Issue type:

[x] question

[x] postgres

Hello I want to make a query where I get the data above, but the typeorm is putting select distinct in the query and because of this is giving error.

const [response, count] = await this.database .getRepository(Rating) .createQueryBuilder("rating") .select( 'rating.id as id, station.name as stationname, rating.stationtext as text, rating.operatorrating as orating, rating.stationrating as srating, fillin.created_at, customer.name as cname, rating.operatorname as oname' ) .innerJoin("rating.fillin", "fillin") .innerJoin("fillin.customer", "customer") .innerJoin("rating.station", "station") .where("fillin.is_deleted = false AND station.chain_id = :chainId", { chainId }) //.setParameter("chainId", chainId) .skip(skip) .take(take) .getManyAndCount();

The console return this:

query: SELECT DISTINCT "distinctAlias"."rating_id" as "ids_rating_id" FROM (SELECT "rating"."id" as id, "station"."name" as stationname, rating.stationtext as text, [1] rating.operatorrating as orating, rating.stationrating as srating, fillin.created_at, [1] "customer"."name" as cname, rating.operatorname as oname FROM "rating" "rating" INNER JOIN "fillin" "fillin" ON "fillin"."id"="rating"."fillinid" INNER JOIN "customer" "customer" ON "customer"."id"="fillin"."customer_id" INNER JOIN "station" "station" ON "station"."id"="rating"."stationid" WHERE fillin.is_deleted = false AND station.chain_id = $1) "distinctAlias" ORDER BY "rating_id" ASC LIMIT 10 -- PARAMETERS: ["aedbcc7f-d336-45d3-b04b-b598bd2445ee"] [1] query failed: SELECT DISTINCT "distinctAlias"."rating_id" as "ids_rating_id" FROM (SELECT "rating"."id" as id, "station"."name" as stationname, rating.stationtext as text, [1] rating.operatorrating as orating, rating.stationrating as srating, fillin.created_at, [1] "customer"."name" as cname, rating.operatorname as oname FROM "rating" "rating" INNER JOIN "fillin" "fillin" ON "fillin"."id"="rating"."fillinid" INNER JOIN "customer" "customer" ON "customer"."id"="fillin"."customer_id" INNER JOIN "station" "station" ON "station"."id"="rating"."stationid" WHERE fillin.is_deleted = false AND station.chain_id = $1) "distinctAlias" ORDER BY "rating_id" ASC LIMIT 10 -- PARAMETERS: ["aedbcc7f-d336-45d3-b04b-b598bd2445ee"] [1] Debug: handler, error [1] QueryFailedError: column distinctAlias.rating_id does not exist

Issue Analytics

State:
Created 4 years ago
Reactions:12
Comments:13 (3 by maintainers)

Top GitHub Comments

23reactions

ardyfebcommented, Sep 14, 2020

This is real performance issue

6reactions

constbcommented, Sep 28, 2020

@ardyfeb @nlucero I think all ORMs use some sort of “magic” when it comes to loading relations with LIMITs on main entity. TypeORM prefers joins, which makes for better performance when pagination is not used. Also, first “distinct” query warms up database cache, so I believe “real” query should be very fast after that.

An alternative would be to load main entity without any relations at all. This would allow one to use plain old LIMIT/OFFSET right there on the main query. But – and this is unavoidable – to load relations, ORM would have to issue separate queries on every requested relation (and relations of relations too). I’m not sure if this would make query faster when pagination is involved, but I’m guessing it would make querying slower when no pagination is used.

I know Laravel Eloquent uses the latter approach. TypeORM prefers former one, with all its drawbacks and benefits. Personally I used both and I haven’t noticed any performance issues with either. Every time I had slow queries, the real issue turned out to be either my mistake (lack of indexes or wrong choice of fields for an index, or forgetting to eagerly load a relation resulting in infamous N+1) or a quirk in a database implementation (like using numeric values in WHERE on a string primary key oddly causes full table scan in mariadb).