Possible to refactor these two SQL queries into one query?

Question

I have a function that uses two SQL queries based on the input profile type.

I would like to refactor it to be one singular query that changes the profile type it searches for based on the input. However, as a SQL newb I don't know if it's possible.

Here is my code:

async searchProfiles(
        query: string,
        profileType: ProfileTypeEnum,
        pageNr: number
    ): Promise<IProfile[]> {
            const adjustedQuery = this.prepareQuery(query);
            if (profileType === ProfileTypeEnum.Person) {
                return this.profilesRepository.query(
                    `
                SELECT p.id, concat(p."firstName", ' ', p."lastName") as title, p."avatarImageUrl" as "imageUrl", 'person' as type, p."profession" as profession
                from profile p
                WHERE concat(p."firstName", ' ', p."lastName") ~* $1
                  AND p.type = 'person' AND p."deletedOn" IS NULL
                LIMIT 5 OFFSET $2
            `,
                    [adjustedQuery, pageNr * 5]
                );
            } else if (profileType === ProfileTypeEnum.Page) {
                return this.profilesRepository.query(
                    `
                SELECT p.id, p."pageName" as title, p."avatarImageUrl" as "imageUrl", 'page' as type, p."profession" as profession
                from profile p
                WHERE p."pageName" ~* $1
                  AND p.type = 'page' AND p."deletedOn" IS NULL
                LIMIT 5 OFFSET $2
            `,
                    [adjustedQuery, pageNr * 5]
                );
            } else {
                throw new RpcValidationException('Profile type required');
            }
    }

If the profile type is "Person" i'd like to get 'person' types and search by first name, last name fields match. If the profile type is "Page" i'd like to get 'page' types and search where the page name matches.

Possible?

Thanks

edit: I have a hideous query to show that might be close to what I want.

SELECT CASE WHEN ($2 = 'person')
            THEN SELECT p.id, concat(p."firstName", ' ', p."lastName") as title, p."avatarImageUrl" as "imageUrl", 'person' as type, p."profession" as profession
            from profile p
            WHERE concat(p."firstName", ' ', p."lastName") ~* $1
            AND p.type = 'person' AND p."deletedOn" IS NULL
            LIMIT 5 OFFSET $3
            ELSE SELECT p.id, concat(p."firstName", ' ', p."lastName") as title, p."avatarImageUrl" as "imageUrl", 'person' as type, p."profession" as profession
            from profile p
            WHERE p."pageName" ~* $1
                AND p.type = 'page' AND p."deletedOn" IS NULL
            LIMIT 5 OFFSET $3

throws QueryFailedError: syntax error at or near "SELECT" but I'm not hopeful that this is close...

** Some links I visited while trying to make this into one query:

IF Condition Perform Query, Else Perform Other Query

w3schools SQL Operators

Case in Select Statement

SELECT using 'CASE' in SQL

I’m voting to close this question because it is asking for improving working code - ask on [codereview.se] — Daniel A. White, Mar 22 '22 at 00:39

score 0 · Answer 1 · answered Mar 22 '22 at 02:07

Use UNION ALL to combine two queries.
Example:

SELECT id, title, imageUrl, type, profession FROM (
   SELECT p.id, concat(p."firstName", ' ', p."lastName") as title, p."avatarImageUrl" as "imageUrl", 'person' as type, p."profession" as profession
   from profile p
   WHERE p.type = 'person' AND p."deletedOn" IS NUL
   UNION ALL
   SELECT p.id, p."pageName" as title, p."avatarImageUrl" as "imageUrl", 'page' as type, p."profession" as profession
   from profile p
   WHERE p.type = 'page' AND p."deletedOn" IS NULL) union_query
WHERE 
title ~* $1 AND type = $2
LIMIT 5 OFFSET $3

Possible to refactor these two SQL queries into one query?

1 Answers1