Question 216359 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Query which searches in multiple columns for filter string

Using Postgresql and Spring.
The Backend gets User Input from the frontend which is called "filter". I want the database "contacts" to be checked in multiple columns:


@Query("SELECT c FROM Contacts c " +
            "WHERE LOWER(CAST(c.supplierNo AS string)) LIKE %:filter% OR LOWER(c.firstName) LIKE    %:filter%" +
            "OR LOWER(c.lastName) LIKE %:filter% OR LOWER(c.supplierName) LIKE %:filter%")
    List<Contacts> findByFilter(String filter);

This works for single-word-filters. But I would want the user to enter for example: "John Smith"(firstName SPACE lastName), or maybe Smith Foodmarket(lastName SPACE supplierName) or Foodmarket Smith. You get the idea. How would I go about this?

WHAT I TRIED:

1
Find search term in multiple columns
This is not it, since I want to be able to search in multiple cols at once.

2
Search Single Value In Multiple Columns
I tried this like shown:

    @Query("SELECT c FROM Contacts c " +
            "WHERE LOWER(CAST(c.supplierNo AS string)) LIKE CONCAT('%', :filter, '%') OR LOWER(c.firstName) LIKE CONCAT('%', :filter, '%')" +
            "OR LOWER(c.lastName) LIKE CONCAT('%', :filter, '%') OR LOWER(c.supplierName) LIKE CONCAT('%', :filter, '%')")
    List<Contacts> findByFilter(String filter);

It is entirely possible that I translated sql -> jpql the wrong way, but this solution does not work as intended

3
Multiple permutations of this, but to no success.

Tags:

2

Answers


  1. 0

    The problem with your query is that if you pass string which contain more than 1 word then all of your conditions will return FALSE

    SELECT 'JOHN' LIKE '%JOHN DOE%'; -- FALSE
SELECT 'JOHN' LIKE '%JOHN%'; -- TRUE

    try this

    SELECT distinct c.*
FROM Contacts c, unnest(array[c.firstName, c.supplierNo, ...more columns]) key
    INNER JOIN unnest(string_to_array(':filter', ' ')) f ON key LIKE '%' || f || '%';

    SQL code that i tested

    CREATE TABLE mytable(col1 text, col2 text, col3 text);
INSERT INTO mytable VALUES('HELLO', 'WORLD', 'TEST');
INSERT INTO mytable VALUES('FOO', 'BAR', 'DOG');
INSERT INTO mytable VALUES('CAT', 'FISH', 'PARROT');

SELECT distinct c.*
FROM mytable c, unnest(array[c.col1, c.col2, c.col3]) key
    INNER JOIN unnest(string_to_array('HELLO PARROT', ' ')) f ON key LIKE '%' || f || '%';
    Login or Signup to reply.
  2. 0

    You can improve your query for 2 words case.

    If filter contains 1 word:
    filter1=word
    filter2=word

    If filter contains 2 words:
    filter1=word1
    filter2=word2

    Updated query

    @Query(
    "SELECT c FROM Contacts c " +
    "WHERE LOWER(CAST(c.supplierNo AS string)) LIKE %:filter% "
    +" OR LOWER(c.firstName) LIKE    %:filter1%"
    +" OR LOWER(c.lastName) LIKE %:filter2% "
    +" OR LOWER(c.supplierName) LIKE %:filter%"
      )
    List<Contacts> findByFilter(String filter);
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search