Question 265008 in Javascript Question posted in
A very good W3school tutorial can be found here.

Javascript – PostgreSQL query – find value in ANY clause or return true if list of ANY is empty

In my app I have a list that can be filtered by selecting subcategory badge. User is able to select a badge and show only the items from the particular subcategory. But I would like the app to display all items if no subcategory is selected. The list of selected subcategories is dynamic and injected into an SQL query (see the example below). But when the list is empty, then of course, query doesn’t return any results. How to write a condition that if ANY list of items is empty then the query should return all elements of the table?

SELECT *
FROM table
WHERE column = ANY( {{ $state.categoriesBadge.filter((item) => item.isSelected == true).map((item) => item.value) }})
Tags:

2

Answers


  1. 0

    You can try the below query to achieve your desired result:

    SELECT *
FROM table
WHERE (
  {{ $state.categoriesBadge.filter((item) => item.isSelected == true).length > 0 ? 
    `column = ANY( ${$state.categoriesBadge.filter((item) => item.isSelected == true).map((item) => item.value)} )` :
    `1=1`
  }}
)

    Explanation of above snippet:

    1. Checking if the length of the filtered subcategories list is greater than 0.
      • If it is, we generate the ANY clause as before to filter the results by the selected subcategories.
      • If it is not, we generate a condition that is always true (1=1), which will return all elements of the table.
    Login or Signup to reply.
  2. 0

    You could use conditional expressions that notice the resulting array is empty and react accordingly: demo

    SELECT *
FROM "table"
WHERE "column" = ANY(COALESCE(NULLIF({{ $state
                                      .categoriesBadge
                                      .filter((item) => item.isSelected == true)
                                      .map((item) => item.value) }}
                                   ,'{}'::text[] --empty text array
                                  )
                            ,ARRAY["column"]
                            )
                   )

    If the array isn’t empty, this is equal to what you already had; nullif and coalesce won’t do anything.

    If it’s empty, nullif turns it into a null which in turn makes coalesce skip it and replace with the column value, resulting in a self-comparison column=any(column).

    Note that if the value of column is null, it will never match any and will never be returned – even if you specifically try to target it by supplying a null in the array. That’s because any uses regular = comparison against every element of the array and null=null is null, not true.

    To be able to search and match null, you’ll need array_position() is not null, which uses is not distinct from comparison instead: demo

    SELECT *
FROM "table"
WHERE array_position(COALESCE(NULLIF({{ $state
                                      .categoriesBadge
                                      .filter((item) => item.isSelected == true)
                                      .map((item) => item.value) }}
                                   ,'{}'::text[]
                                  )
                            ,ARRAY["column"]
                            )
                      ,"column"
                   ) is not null;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search