Filtering shop products by categories (SQL)-CodePudding

For example I have a list of houses which have parameters below

price
numberOfRooms
WithBalcony

I need to create a global SQL Query that will filter that list by provided parameters. That is like

select * from database where price = x and numberOfRooms = y and WithBalcony = z;

But there may be cases when one of the parameters will be null in request, so my query wouldn't work.

For example if parameter "price = null" my query must be without price logically like this

select * from database where numberOfRooms = y and WithBalcony = z;

That is normal for 3 parameters, but what if I have 10 parameters? Should I write all queries, the number of which will be factorial of 10?

Is there one any other solution?

CodePudding user response：

Here is a general pattern you may use:

SELECT *
FROM yourTable
WHERE (price = x OR x IS NULL) AND
      (numberOfRooms = y OR y IS NULL) AND
      (WithBalcony = z OR z IS NULL);

The IS NULL clauses as used above will effectively cause each restriction to be ignored should the parameter be undefined.

CodePudding user response：

Java can invoke functions.
Explanation: function input argument value correspond with column value. for example: on_null_column_omit({'1', null}') means that col1 value is 1 and col2 value is null. In my demo, the function input value correspond to col1 to col10 column's value. Obviously you can reorder the input argument, but in the mean time, you also need to reorder the column's name.
There is many raise info, raise notice, that's for debug purpose.
demo

CREATE OR REPLACE FUNCTION on_null_column_omit (args text[10])
    RETURNS json
    LANGUAGE plpgsql
    AS $$
DECLARE
    str text;
    i int := 1;
    all_col_name text[];
    _partial_sql text := ' select row_to_json(t.*) from on_null_omit t where ';
    _temp1 text := '';
    _tempnull text[];
    _result json;
    _m text;
    _str1 text;
BEGIN
    SELECT
        ARRAY (
            SELECT
                column_name::text
            FROM
                information_schema.columns
            WHERE
                table_name = 'on_null_omit') INTO all_col_name;
    RAISE info 'all_col_name[1]: %', all_col_name[1];
    foreach str IN ARRAY args LOOP
        IF str IS NULL THEN
            _m := ' and ' || all_col_name[i] || ' = null ';
            _m := TRIM(BOTH FROM _m);
            _tempnull := array_append(_tempnull, _m);
        END IF;
        _temp1 := _temp1 || all_col_name[i] || ' = ' || COALESCE(quote_literal(str), 'null') || ' and ';
        i := i   1;
    END LOOP;
    _temp1 := TRIM(BOTH FROM _temp1);
    RAISE info '_temp_null: %', _tempnull;
    RAISE info '_temp1: %', _temp1;
    IF _temp1 ILIKE '%and col10 = null and%' THEN
        SELECT
            regexp_replace(_temp1, 'and col10 = null and', '') INTO _temp1;
    END IF;
    RAISE info '1_temp1:%', _temp1;
    IF _temp1 ILIKE '%col1 = null%' THEN
        SELECT
            regexp_replace(_temp1, 'col1 = null and', '') INTO _temp1;
    END IF;
    RAISE info '2_temp1:%', _temp1;
    foreach _str1 IN ARRAY _tempnull LOOP
        SELECT
            regexp_replace(_temp1, _str1, '') INTO _temp1;
    END LOOP;
    _temp1 := TRIM(BOTH FROM _temp1);
    RAISE info '3_temp1:%', _temp1;
    EXECUTE _partial_sql || _temp1 INTO _result;
    RETURN _result;
END;
$$;