typeorm: 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
About this issue
- Original URL
- State: closed
- Created 5 years ago
- Reactions: 17
- Comments: 15 (3 by maintainers)
This is real performance issue
This is especially bad for one-to-one relations, where the is no duplicated row issue. At the very least, this behavior should be disabled for one-to-one relations.
this is still a problem
that’s like pre-select query. typeorm does this because JOINs may cause multiple rows be returned for a single row in the original entity table, making it impossible to properly apply LIMIT. typeorm selects distinct ids applying limits to ids only, and then second (real select) applies WHERE id IN instead of LIMIT, so that you get both JOINs and LIMIT working properly at the same time.
the error you are getting is because your
.select()is wrong. typeorm prefixes column aliases with entity alias, and expects names to match in result. you are overriding this by supplying your own aliases withASthus breaking the query..createQueryBuilder("rating")- there’s your entity alias.SELECT DISTINCT "distinctAlias"."rating_id"– that’s what typeorm expects id column to be named like.rating.id as id– that’s where you broke it.generally, I’d advise to name columns and entity fields exactly the same and let typeorm do the rest.
when you need extra fields in the entity, use
.addSelect(), plain.select()completely overrides autogenerated field names.in case when you need only limited number of fields in result, use entity alias like
.select('rating.id', 'station.name')only. (though you should always select ids anyway)using
.innerJoin()to drop some columns from another table into the entity is just wrong..innerJoin()is intended to be able to filter on related entity without selecting it. if you need another entity data, use.innerJoinAndSelect()if relation is declared as an entity field, use.innerJoinAndMapOne()/.innerJoinAndMapMany()to add a field to an entity without declaring a relation and get one or multiple related entities…@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).
@constb is there an option to disable this? Some weird, not very well documented parameter?
This is a super bad decision to wrap the main query inside another one to filter duplicates.
It’s 2024 and i’m getting massive performance problems because of this. With there was a way to disable it.
I may understand this behavior, but it applies my sorting to the outer query in production. My sorting is tied to a column that isn’t in the outer query, it breaks it. That is a BUG. It’s a query I have zero control over and therefore can’t fix without hacks.
I guess just don’t load relations when you apply limit? This is a part of typeorm architecture, I don’t think this can be changed now without doing massive rewrite.
I’m not typeorm maintainer, @pleerock does great job maintaining this library.
Same here, is there way to remove
ids from the results? I don’t want to select them. Also, when I useaddSelect, DB complains that there are duplicated selects.