Order by 2 columns with case

I have a query that searches for user in 2 colums firstUser and secondUser and orders them by first and last name:

WHERE first_user LIKE :user OR second_user LIKE :user ORDER BY 
        CASE WHEN first_user LIKE :user THEN firstUser.firstName END ASC,
        CASE WHEN first_user = :user THEN firstUser.lastName END ASC,
        CASE WHEN second_user = :user THEN secondUser.firstName END ASC,
        CASE WHEN second_user = :user THEN secondUser.lastName END ASC
)

But this first orders the cases when user is at first user then it orders the case when user is secondUser. Is ther a way to order the whole data by first name and last name no matter if the user is firstUser or secondUser?

    SELECT c FROM Chat as c LEFT JOIN c.firstUserModel as firstUser LEFT JOIN c.secondUserModel as secondUser
    WHERE first_user = :user AND lower(concat(secondUser.firstName, ' ', secondUser.lastName))
LIKE lower(concat(:name, '%')) or second_user = :user AND lower(concat(firstUser.firstName, ' ', firstUser.lastName))
LIKE lower(concat(:name, '%')) ORDER BY 
            CASE WHEN first_user LIKE :user THEN firstUser.firstName END ASC,
            CASE WHEN first_user = :user THEN firstUser.lastName END ASC,
            CASE WHEN second_user = :user THEN secondUser.firstName END ASC,
            CASE WHEN second_user = :user THEN secondUser.lastName END ASC
    )

Models:

public class Chat {
    private UserModel firstUserModel;
    private UserModel secondUserModel;
}

public class UserModel {

    private String firstName;
    private String lastName;
}

Answer

I’m not entirely sure what the expectation is, is this what you’re going for?

ORDER BY LEAST(firstUser.firstName, secondUser.firstName) ||
         LEAST(firstUser.lastName, secondUser.lastName) ASC

Advertisement

Answer