convert sql query to typeorm query builder-CodePudding

I have this sql query:

SELECT
    TOP 1 parentId,
    name,
    (
        CASE
            WHEN name = 'Category A' THEN 1
            ELSE 0
    ) sortOrder
FROM
    catagories
WHERE
    parentId = 'A45-G65'
ORDER BY
    sortOrder 
DESC

How can I write same query in typeorm using query builder?

I tried something like this but it is not providing proper expected response:

this.getRepository(Categories).createQueryBuilder("categories")
    .select(["parentId"])
    .orderBy("(WHEN name = 'Category A' THEN 1
    ELSE 0 END)")
    .where("parentId": "A45-G65")
    .getOne();

Can anyone help me how to transform the actual sql query into typeorm query builder?

CodePudding user response：

You can write the same query using TypeORM's query builder by chaining the various query builder methods together. Here is an example of how you can write the query using TypeORM's query builder:

const result = await connection
  .getRepository(Category)
  .createQueryBuilder("category")
  .select(["category.parentId", "category.name", `CASE WHEN category.name = 'Category A' THEN 1 ELSE 0 END as sortOrder`])
  .where("category.parentId = :parentId", { parentId: 'A45-G65' })
  .orderBy("sortOrder", "DESC")
  .getOne();

Please note that you need to have the correct import of the entities and the correct connection to your database established.