I have a table User and RecentSearch and a screen on a website which allows the user to find other users. The server returns a user array without taking into account the friends and recent searches, I want to include it client side (the client means an iOS app running these queries). I want friends and recents searches to take precedence from the stuff the server returns.

I am a bit stuck. I am not sure how to do this. I made a query below, but that doesn't work because it requires all users to have the flag showOnSearchScreen set to 1, but that doesn't always apply for friends and recent searches.

The flag showOnSearchScreen is only set to true for users returned by the server. This means that is possible for a User to have flag showOnSearchScreen set to 1 and also be friends.

These are my tables:

create table User (
    userUuid text not null primary key,
    username text not null,
    showOnSearchScreen int not null,
    friends int not null
);

create table RecentSearch (
    userUuid text not null primary key,
    foreign key (userUuid) references User (userUuid) on delete cascade
);

This is my query which does not work (assuming the user is searching for users with a 'g' somewhere):

select *,
       case
           when exists(select 1
                       from User CustomUser
                       where User.userUuid = CustomUser.userUuid
                         and CustomUser.friends = 1
                         and CustomUser.username like '%g%') then 2
           else 0 end as friendScore,
       case
           when exists(select 1
                       from RecentSearch
                                join User CustomUser on RecentSearch.userUuid = CustomUser.userUuid and
                                                        User.userUuid = RecentSearch.userUuid
                       where CustomUser.username like '%g%') then 1
           else 0 end as recentSearchScore
from User
-- This is wrong...
where showOnSearchScreen = 1
order by friendScore   recentSearchScore;

Must this be resolved with outer joins? I am a bit confused.

So keypoints:

• Server sends Users to client, client upserts them in the database and sets flag showOnSearchScreen to 1
• The query should return at least all users with that flag set to 1
• I want users who are friends and have a username similar to the input (I guess %sometext% would work good enough) to take precedence over the users returned from the server
• Same applies for Users from RecentSearch, if a user joined from RecentSearch has a similar username, take precedence over the users returned from the server (but friends should be shown at the top)

CodePudding user response：

IIUC - Consider a UNION query that potentially resolves your bullet points using score to determine precedence or ordering:

SELECT userUuid, username, 3 AS score
FROM User
WHERE friends = 1 
  AND username LIKE '%g%'

UNION

SELECT r.userUuid, u.username, 2 AS score
FROM RecentSearch r
 JOIN User u ON r.userUuid = u.userUuid 
WHERE u.username LIKE '%g%'

UNION

SELECT userUuid, username, 1 AS score
FROM User 
WHERE showOnSearchScreen = 1 
  AND username LIKE '%g%'