PostgreSQL's ILIKE query with conditional filtering

Question

I'm writing a user search method that is supposed to search for users by their phone number, email and username. The thing is that users are allowed to hide their phone number and email. So, if they do hide it in their privacy settings of the application then they should not be listed in the search result.

I'm using typeorm to build this search query and here's what would be my attempt to do this if that requirement with the hiding personal info were not there:

const whereClause = `
        (
            "user".phone ILIKE :searchTerm
                OR "user".username ILIKE :searchTerm
                OR "user".profile_email ILIKE :searchTerm
        ) AND "user".id != :userId
        `;

const users = await this.userRepository.createQueryBuilder('user')
                                       .where(whereClause, filters)
                                       .leftJoinAndSelect('user.subscription', 'subscription')
                                       .leftJoinAndSelect('user.profilePrivacyPermissions', 'perms')
                                       .limit(ESearchResponseLimit.USERS)
                                       .getMany();

The profile privacy table looks as follows:

Screen type 1 corresponds to phone number and 2 corresponds to email. For now we only consider two possible states: isEverybody and isNobody (that's we don't consider contacts). So, e.g., if a user decided to hide his phone number then the isNobody value will be equal to true, others will be set to false.

What I need is to somehow take these privacy settings into account in that search query I'm building. It should be something like this:

        const whereClause = `
        (
            ("user".phone ILIKE :searchTerm IF (perms.screenType = ${EScreenType.PHONE_NUMBER} AND perms.isEverybody))
                OR "user".username ILIKE :searchTerm
                OR ("user".profile_email ILIKE :searchTerm IF (perms.screenType = ${EScreenType.EMAIL} AND perms.isEverybody))
        ) AND "user".id != :userId
        `;

The problem is that there's no such an operator as IF but the idea is that I need to search by phone if it is not hidden, I need to search by email if it is not hidden either. I can't come up with even a remote idea on how to implement this in a query.

If it's possible to do with a raw query, it would be also good.

I've found out that there's the IF operator in PostgreSQL but I don't see how I would use it here.

EDIT:

If I search by phone when email is hidden (which means that for the email screen perms.isEverybody = false) the profilePrivacyPermissions field of the user object looks as follows:

"profilePrivacyPermissions": [
      {
        "screenType": 1,
        "isEverybody": true,
        "isContacts": false,
        "isNobody": false
      }
    ]

But it should look as follows:

    "profilePrivacyPermissions": [
      {
        "screenType": 1,
        "isEverybody": true,
        "isContacts": false,
        "isNobody": false
      },
      {
        "screenType": 2,
        "isEverybody": false,
        "isContacts": false,
        "isNobody": true
      },
      {
        "screenType": 4,
        "isEverybody": true,
        "isContacts": false,
        "isNobody": false
      },
      {
        "screenType": 5,
        "isEverybody": true,
        "isContacts": false,
        "isNobody": false
      },
      {
        "screenType": 3,
        "isEverybody": true,
        "isContacts": false,
        "isNobody": false
      }
    ]

I understand it happens because of the perms conditions in the where clause of the query.

Answer 1

WHERE 1=1
AND ( True OR nm.strain = :_strain ) -- Boolean logic to short-circuit optional conditions
AND ( False OR nm.strain ILIKE :_strain_like )
...

---

The parametrised query:

---

WHERE 1=1
AND ( " . $ignore_strain . " OR nm.strain = :_strain ) -- Boolean logic to short-circuit optional conditions
AND ( " . $ignore_strain_like . " OR nm.strain ILIKE :_strain_like )
...

---

(this is PHP, but the technique is the same)

Answer 2

IF is not part of SQL in Postgres (nor standard SQL). It's part of the procedural elements added by PL/pgSQL.

In plain SQL, just use boolean logic with AND:

    const whereClause = `
    (   perms.isEverybody AND 
       (perms.screenType = ${EScreenType.PHONE_NUMBER} AND "user".phone ILIKE :searchTerm
     OR perms.screenType = ${EScreenType.EMAIL}        AND "user".profile_email ILIKE :searchTerm)
     OR "user".username ILIKE :searchTerm
    ) AND "user".id != :userId
    `;

Or use SQL CASE, may be easier to understand (logically equivalent):

    const whereClause = `
    (CASE WHEN perms.isEverybody THEN
        CASE perms.screenType
           WHEN ${EScreenType.PHONE_NUMBER} THEN "user".phone ILIKE :searchTerm
           WHEN ${EScreenType.EMAIL}        THEN "user".profile_email ILIKE :searchTerm
        END
     END
     OR "user".username ILIKE :searchTerm
    ) AND "user".id != :userId
    `;

The subtle difference: Only the first branch where the WHEN clause evaluates to true is executed. Other branches of the same CASE statement are not even entered. (So no exception is raised if something illegal is lurking there.)

I left out the ELSE clause, which defaults to NULL in SQL - and that is equivalent to false in the WHERE clause, where only true qualifies.

Note, I am using two different syntax variants of CASE ("simple" and "searched".) See:

• Simplify nested case when statement
• Display column name with max value between several columns

Note also, there is a procedural CASE in PL/pgSQL, which serves a similar purpose, but is strictly distinct from SQL CASE.