PostgreSQL WHERE Clause – Prioritizing Conditions with OR

conditionpostgresqlselect

I'm storing user data (username, email, password hash) in PostgreSQL, and I can only enforce uniqueness on username. I'd like to let users provide either their username or their email when they login. However, when matching their input against usernames and email addresses I want to ensure that a username match gets priority over an email match.

For example, in the following contrived instance:

id | username        | email address    | pw_hash
-------------------------------------------------
1    john_doe          john@domain.com    x1j34
2    john@domain.com   john@domain.com    x1j34

if both users enter their username, I want to guarantee that user 2 gets logged in.

If I use:

SELECT * FROM member WHERE (username='john@domain.com' OR email='john@domain.com') AND pw_hash='x1j34';

this matches both users but provide no guarantee on order.

Is there a way to have to conditions, and only check the second condition if the first isn't satisfied? Can this be done in a single query?

Best Answer

You have two conditions to check during the authentication:

There is a user with matching username; do not need to match by email.
There are no users with matching usernames; need to match by email.

So the query is:

WITH matching_username AS (SELECT * FROM member WHERE username = 'john@domain.com')
SELECT * FROM matching_username WHERE (SELECT COUNT(*) FROM matching_username) = 1
                                   AND pw_hash = 'x1j34'
UNION 
SELECT * FROM member            WHERE (SELECT COUNT(*) FROM matching_username) = 0
                                   AND email = 'john@domain.com' 
                                   AND pw_hash='x1j34';

On the other hand, there is a problem with your design of the table.

What if two users have have the same email, but neither of them have email as their username? If one of them tries to authenticate with an email, you will not be able to tell, which user record to authenticate against.

Related Solutions

Sql-server – Conditional WHERE clause

Dynamic SQL won't be able to see your table variable unless you also declare it and populate it within the same dynamic SQL scope. A #temp table will work fine, and I'm not sure why you "need" to use a table variable, but you can always do this:

SELECT * INTO #tbl FROM @tbl;

Anyway assuming you can change your process:

CREATE TABLE #tbl(Col1 INT, Col2 INT);

INSERT #tbl SELECT 1,4
  UNION ALL SELECT 2,3
  UNION ALL SELECT 5,8;

DECLARE @sql NVARCHAR(MAX);
DECLARE @filter NVARCHAR(255);

SET @sql = N'SELECT Col1, Col2 FROM #tbl';

SET @filter = N'Col1 = 1 OR Col2 = 3';

SET @sql = @sql + COALESCE(' WHERE ' + @filter, '');

EXEC sp_executesql @sql;

DROP TABLE #tbl;

Results:

Col1        Col2
----------- -----------
1           4
2           3

As an aside, you cannot do things like this:

select * from @tbl where @filter

You need to build such statements dynamically. SQL Server won't see @tbl or @filter as entities or where clauses.

Postgresql – Query for cross matching multiple joined tables

Sample Data

This is actually very easy, first let's create your schema. Next time around, it's better and easier for everyone if you provide data like this.

CREATE TEMP TABLE country (
  countryid serial PRIMARY KEY,
  name text
);
CREATE TEMP TABLE city (
  cityid serial PRIMARY KEY,
  name text,
  countryid int REFERENCES country
);
CREATE TEMP TABLE user_country (
  userid int,
  countryid int REFERENCES country
);
CREATE TEMP TABLE user_city (
  userid int,
  cityid int REFERENCES city
);
INSERT INTO country (name) VALUES ('England'),('Canada');
INSERT INTO city (name,countryid) VALUES ('London',1),('Leeds',1),('Vancouver',1);
INSERT INTO user_country (userid, countryid) VALUES (1,1),(2,1),(4,2);
INSERT INTO user_city (userid, cityid) VALUES (3,1),(4,2),(2,3);

Solution

SELECT DISTINCT
  user_city_country.userid AS user1,
  user_country.userid AS user2
FROM user_country
JOIN country USING (countryid)
JOIN (
  SELECT *
  FROM user_city
  JOIN city USING (cityid)
  JOIN country USING (countryid)
) AS user_city_country
  USING (countryid);

This breaks down easy... Path one is to resolve the user to the country..

SELECT DISTINCT user_country.userid
FROM user_country
JOIN country USING (countryid)

Path 2 is to resolve the user_city to the country.

SELECT *
FROM user_city
JOIN city USING (cityid)
JOIN country USING (countryid)

Then we just JOIN them. Note, we're not actually using the country at all. You have it in your schema. So I preserved it here.

This returns...

 user1 | user2 
-------+-------
     4 |     2
     3 |     1
     2 |     1
     3 |     2
     4 |     1
     2 |     2

Now, if you want to know all matches, just add WHERE user_country.userid = '1' OR user_city_country.userid = '1';

 user1 | user2 
-------+-------
     4 |     1
     3 |     1
     2 |     1