Filter full name postgresql-CodePudding

SELECT * 
FROM employee 
WHERE is_deleted != true 
  AND (:lastname IS NULL 
       OR lastname ILIKE '%'||lastname||'%'
       OR :firstname IS NULL
       OR firstname ILIKE '%'||:firstname||'%'
       OR :middlename IS NULL
       OR middlename ILIKE '%'||:middlename||'%');

I have a full name column and I need to filter by first name, last name or patronymic, depending on what the user enters (or last name and first name together) b tell me how to implement

CodePudding user response：

Your logic is correct except the the various name criteria should be ANDed together:

SELECT * 
FROM employee 
WHERE is_deleted != true AND
      (:lastname IS NULL OR lastname ILIKE '%' || lastname || '%') AND
      (:firstname IS NULL OR firstname ILIKE '%' || :firstname || '%') AND
      (:middlename IS NULL OR middlename ILIKE '%' || :middlename || '%');

CodePudding user response：

SELECT *
FROM employee
WHERE is_deleted != true
  AND (:search_term IS NULL OR 
       (split_part(full_name, ' ', 1) ILIKE '%' || :search_term || '%' OR
        split_part(full_name, ' ', 2) ILIKE '%' || :search_term || '%' OR
        split_part(full_name, ' ', 3) ILIKE '%' || :search_term || '%'));

This query will use the split_part() function to extract the first, second, and third parts of the full name, and then use them in the filtering. The ILIKE operator is used for case-insensitive matching. If the search_term parameter is null, the filtering will be skipped, and all the rows will be returned.