From c2268c285648ef02ece04de0d9df0813c6d70ff8 Mon Sep 17 00:00:00 2001 From: benj Date: Sat, 24 Dec 2022 00:43:38 -0800 Subject: refactor everything with more abstraction and a nicer interface --- crates/secd/store/sqlite/sql/find_identity.sql | 26 +++++++++++++++----------- 1 file changed, 15 insertions(+), 11 deletions(-) (limited to 'crates/secd/store/sqlite/sql/find_identity.sql') diff --git a/crates/secd/store/sqlite/sql/find_identity.sql b/crates/secd/store/sqlite/sql/find_identity.sql index f94e7b1..1528407 100644 --- a/crates/secd/store/sqlite/sql/find_identity.sql +++ b/crates/secd/store/sqlite/sql/find_identity.sql @@ -1,11 +1,15 @@ -select - identity_public_id - , data - , i.created_at - , i.deleted_at -from identity i -join identity_email_validation iev using (identity_id) -join email_validation ev using (email_validation_id) -join email e using (email_id) -where ((?1 is null) or (i.identity_public_id = ?1)) -and ((?2 is null) or (e.address = ?2)); +select distinct + identity_public_id + , data + , i.created_at + , i.updated_at + , i.deleted_at +from identity i +left join address_validation av using (identity_id) +left join address a using (address_id) +left join session s using (identity_id) +where (($1 is null) or (i.identity_public_id = $1)) +and (($2 is null) or (a.value = $2)) +and (($3 is null) or (($3 is true) and (av.validated_at is not null))) +and (($4 is null) or (s.token_hash = $4)) +and i.deleted_at is null; -- cgit v1.2.3