Can't sort by fields added with addSelect?

[syadykin]

Hello,

I'm trying to query and sort data by generated field with limited resulting set, and seems typeorm produces wrong first query where order is not taken into account. My query:

Item
  .createQueryBuilder("item").select()
  .addSelect("ts_rank_cd(item.search, to_tsquery(:query))", "rank")
  .setParameter("query", "term")
  .orderBy("rank", "DESC")
  .take(10)
  .getMany();

which produces incorrect first query:

SELECT DISTINCT "distinctAlias"."item_id" as "ids_item_id" FROM (
  SELECT "item"."created_at" AS "item_created_at",
    . . . . .
    ts_rank_cd("item"."search", to_tsquery($1)) AS "rank" FROM "items" "item"
  ) "distinctAlias" ORDER BY "item_id" ASC LIMIT 10

Notice there is no «rank» field in order by.

Is this a bug or I missed something?

[syadykin]

Ok, absolutely synthetic case:

@Entity("items")
class Item extends BaseEntity {
  @PrimaryGeneratedColumn("increment", { type: "integer" })
  public id: number;

  @Column("integer")
  public val: number;
}

Data:

INSERT INTO items (val) VALUES (6), (5), (7), (8)

Query:

Item.createQueryBuilder("item").select()
  .addSelect("val * 2", "d")
  .orderBy("d", "DESC")
  .take(3)
  .skip(0)
  .getMany();

returns a list of

[ Item { id: 3, val: 7 }, Item { id: 1, val: 6 }, Item { id: 2, val: 5 } ]

forth record isn't included.

[pleerock]

having order by in subquery does not make sense - it does not change anything and even not supported construction in most databases. Prove:

Let's say I have following code:

const posts: Post[] = [];
for (let i = 0; i < 10; i++) {
    const post = new Post();
    if (i > 5 && i < 8) {
        post.name = `timber`;
    } else {
       post.name = `Tim${i}ber`;
    }
    post.count = 2;
    posts.push(post);
}
await connection.manager.save(posts);

const loadedPosts = await connection.manager
    .createQueryBuilder(Post, "post")
    .addSelect("ts_rank_cd(to_tsvector(post.name), to_tsquery(:query))", "rank")
    .orderBy("rank", "DESC")
    .setParameter("query", "timber")
    .getMany();

console.log(loadedPosts);

It produces following query:

SELECT "post"."id" AS "post_id", "post"."name" AS "post_name", "post"."count" AS "post_count", ts_rank_cd(to_tsvector("post"."name"), to_tsquery($1)) AS "rank" FROM "post" "post" ORDER BY rank DESC -- PARAMETERS: ["timber"]

Which gives following results:

[ Post { id: 7, name: 'timber', count: 2 },
  Post { id: 8, name: 'timber', count: 2 },
  Post { id: 3, name: 'Tim2ber', count: 2 },
  Post { id: 4, name: 'Tim3ber', count: 2 },
  Post { id: 5, name: 'Tim4ber', count: 2 },
  Post { id: 6, name: 'Tim5ber', count: 2 },
  Post { id: 9, name: 'Tim8ber', count: 2 },
  Post { id: 1, name: 'Tim0ber', count: 2 },
  Post { id: 10, name: 'Tim9ber', count: 2 },
  Post { id: 2, name: 'Tim1ber', count: 2 } ]

Now let's execute this with ordering in subquery:

SELECT DISTINCT "distinctAlias"."post_id" as "ids_post_id" FROM (SELECT "post"."id" AS "post_id", "post"."name" AS "post_name", "post"."count" AS "post_count", ts_rank_cd(to_tsvector("post"."name"), to_tsquery($1)) AS "rank" FROM "post" "post" ORDER BY rank DESC) "distinctAlias" ORDER BY "post_id" ASC LIMIT 5 -- PARAMETERS: ["timber"]
SELECT "post"."id" AS "post_id", "post"."name" AS "post_name", "post"."count" AS "post_count", ts_rank_cd(to_tsvector("post"."name"), to_tsquery($1)) AS "rank" FROM "post" "post" WHERE "post"."id" IN (1, 2, 3, 4, 5) ORDER BY rank DESC -- PARAMETERS: ["timber"]

results:

[ Post { id: 1, name: 'Tim0ber', count: 2 },
  Post { id: 2, name: 'Tim1ber', count: 2 },
  Post { id: 3, name: 'Tim2ber', count: 2 },
  Post { id: 4, name: 'Tim3ber', count: 2 },
  Post { id: 5, name: 'Tim4ber', count: 2 } ]

Solution? Make complex DISTINCT query built for pagination even complexier and try to fix this issue. Fix will be released in 0.1.7

Btw, you can simply use .limit method instead of .take since take/skip are used for complex queries with joins involved.

[pleerock]

Also in 0.1.7 now when you use skip/take it will not generate two queries if no joins are used