Filter a database table in Ruby, migrating code to Postgres

Question

# Users

| id    | name     | age  |
| ----- | -------- | ---- |
| 1     | Rand     | 25   |
| 2     | Mat      | 24   |
| 3     | Perrin   | 23   |

Given the above table I've been building queries against it in Rails with the basic stuff which works quite well. The real application has about 10 of those fields to query against.

users = User.none
users = users.where(age: params[:age])   if params[:age]
users = users.where(name: params[:name]) if params[:name]

I've always wanted to try to move that into the database and I came up with this function in Postgres

CREATE OR REPLACE FUNCTION filter_users(name character varying, age integer)
RETURNS TABLE(
  user_id int,
  name character varying,
  age integer
)
AS $$

select id as user_id, name, age
from users
where (name IS NULL OR name = $1)
  and (age IS NULL OR age = $2)

$$ LANGUAGE SQL;

What I would like to know is whether there is a better way to do these multiple where queries, where each of them can be null, and should therefore not be used?

Stick to this way. It doesn't look pretty but nobody said that SQL statements should look nice. P.S. I don't see a reason to test if 1 equals 1. — Jonathan Jacobson, Sep 20 '20 at 22:29

GMB · Answer 1 · 2020-09-20T22:53:07.087

0

I think you want:

create or replace function filter_users(
    p_name character varying, 
    p_age integer
)
returns table(
    user_id int,
    name character varying,
    age integer
)
as $$
    select id as user_id, name, age
    from users
    where 1 = 1
      and (p_name is null or name = p_name)
      and (p_age  is null or age = p_age)
$$ language sql;

Each of the condition applies only if the parameter is not null.

You can shorten the conditions a little using colaesce() (this will not make the query more efficient, and might slow it down):

    where 1 = 1
      and coalesce(p_name, name) is not distinct from name
      and coalesce(p_age, age)   is not distinct from age

edited Sep 20 '20 at 22:53

answered Sep 20 '20 at 21:43

GMB

216,147
25
84
135

`IS NOT DISTINCT FROM` will prevent the executor from using indexes. – Jonathan Jacobson Sep 20 '20 at 22:22
@JonathanJacobson: yes, I mentionned that it does *not* make the query more efficient... – GMB Sep 20 '20 at 22:30
that's an understatement. It makes the query run less efficiently. – Jonathan Jacobson Sep 20 '20 at 22:31
@JonathanJacobson: I might argue that it depends whether there are indexes on these columns or not... and other factors as well. The time I spent answering SO questions taught me not to make too many assumptions. – GMB Sep 20 '20 at 22:38
Indeed, you might argue that. And yet, your suggestion doesn't count as good advice, in my opinion. Also observe that you wrote "this will not necessarily..." which hints as if in some cases it would make the query more efficient. – Jonathan Jacobson Sep 20 '20 at 22:43
@JonathanJacobson: so that's a semantics debate, really. I slightly edited my answer to remove the ambiguity. – GMB Sep 20 '20 at 22:53

score -1 · Answer 2 · edited Sep 20 '20 at 22:12

-1

I do not see how your function would even compile.

This is how I would write it:

CREATE OR REPLACE FUNCTION filter_users(_name text, _age integer)
RETURNS TABLE(
  user_id int,
  name text,
  age integer
)
AS $$

select id as user_id, name, age
from users
where 1 = 1
  and name = coalesce(_name, name)
  and age  = coalesce(_age,  age)

$$ LANGUAGE SQL;

This will not work if you allow null values in name or age in your table.

edited Sep 20 '20 at 22:12

sticky bit

36,626
12
31
42

answered Sep 20 '20 at 21:46

Mike Organek

11,647
3
11
26

1

Of course it would compile. He's referencing the arguments by their respective number. – Jonathan Jacobson Sep 20 '20 at 22:27

Filter a database table in Ruby, migrating code to Postgres

2 Answers2