Find entry where value does not intersect with other value

Question

I am having issues making an ActiveRecord query in Ruby on Rails that gives me back all users, whose blocked_dates (autogenerated string value) do not intersect with the formatted_dates (autogenerated string value) of a given event. The problem I'm having is that, for example:

User.where.not("string_to_array(blocked_dates, ',') && string_to_array(?, ',')", "26.12.2015")

Gives back an empty list, whereby:

User.where("string_to_array(blocked_dates, ',') && string_to_array(?, ',')", "26.12.2015")

Gives back the correct users whose blocked_dates actually contain '26.12.2015'.

Is there a reason for this strange behavior? Or does the Postgres overlap operator && not work in conjunction with NOT?

In case the question arises, here is the generated SQL query:

SELECT "users".* FROM "users"  WHERE (NOT (string_to_array(blocked_dates, ',') && string_to_array('26.12.2015', ',')))

Hi, Did you try with `AND` instead of `&&` ? I hope this would be work with `AND` — Nitin Srivastava, Jan 15 '16 at 12:19
PG::DatatypeMismatch: ERROR: argument of AND must be type boolean, not type text[] — Vitaly Stanchits, Jan 15 '16 at 12:20
Date appears to be a scalar type, no need to convert it to array, IMHO. (BTW: why the *german* date format?) — joop, Jan 15 '16 at 12:29
Because the application requires to the dates to be put like this in other components, but since the field is autogenerated I can put them in any format I want. — Vitaly Stanchits, Jan 15 '16 at 12:32

score 1 · Accepted Answer · answered Jan 15 '16 at 12:35

Ok, I think I understood the problem I had. The default value of "blocked_dates" of the users table was nil. Because of that, the query was not able to calculate overlaps. After I changed the default value of blocked_dates to "" instead of nil, the NOT statement started giving me the correct values.

score 1 · Answer 2 · edited May 23 '17 at 12:15

1

You must be aware that only WHERE clause expressions evaluating to TRUE qualify. When inverting a boolean value with NOT, NULL stays NULL and still doesn't qualify. You can use NULL-safe constructs like:

WHERE  (string_to_array(blocked_dates, ',')
     @> string_to_array('26.12.2015', ',')) IS NOT TRUE

(Using the simpler contains operator @> for your case testing for a single date, btw.)

Or:

WHERE  (blocked_dates IS NULL OR
        NOT (string_to_array(blocked_dates, ',') @> string_to_array('26.12.2015', ','))

Or, while working with your awkward string:

WHERE  (blocked_dates LIKE '%26.12.2015%') IS NOT TRUE

But all of this is putting lipstick on a pig and all the constructs are error-prone and depend on a (hopefully) matching date format. Why the string-to-array conversion in the first place? The column blocked_dates should at least be an array of dates (date[]) or, better yet, normalize your relational model with a separate table listing blocked dates instead of the column ~~users.blocked_dates~~:

CREATE TABLE user_blocked_date (
  user_id int REFERENCES users(user_id) ON DELETE CASCADE ON UPDATE CASCADE
, blocked_date date
, PRIMARY KEY (user_id, blocked_date)
);

Depending on data distribution this may or may not occupy more space on disk. But your query would be much faster with one of the standard techniques like:

SELECT *
FROM   users u
WHERE  NOT EXISTS (
   SELECT 1
   FROM   user_blocked_date
   WHERE  user_id = u.user_id
   AND    blocked_date = '2015-12-26';

Using ISO 8601 date format, btw. Why?

How to convert "string" to "timestamp without time zone"

Select rows which are not present in other table

edited May 23 '17 at 12:15

Community

1
1

answered Jan 15 '16 at 14:35

Erwin Brandstetter

605,456
145
1,078
1,228

I first thought of doing that by creating a separate table blocked_dates consisting of date in a normal date format and a user_id foreign key. The question is still, given that I have this table, what do I do to have a normal where query that filters a collection of users excluding those that have a certain number of dates linked to them via this table. The query you posted should work given a single blocked date, but I am not sure whether I could use this approach to filter only those users where no blocked_date entry of the given date is associated. – Vitaly Stanchits Jan 15 '16 at 15:42
Just to be clear, I have in my object model a User, an Event and an EventDay. Event has a number of dates when it takes place, User has a number dates he should be able block. The idea is to be able to filter those users which have not explicitly blocked dates X[] (whatever the input format chosen, the object is stored as standard Postgres Date) so that I can then filter those users that can be displayed for a certain event, as well as for the user, be able to display only those events the dates of which are not blocked by the user. It can be done either with database tables, or with date[]. – Vitaly Stanchits Jan 15 '16 at 15:46
@VitalyStanchits: Interesting question. One has to be very clear about what to test (user has any event date blocked? or all event dates blocked? etc.). I suggest you start a *new question* where you also provide your exact table definitions, your Postgres version and cardinalities for each table. – Erwin Brandstetter Jan 15 '16 at 15:57
Ok, I can do that. http://stackoverflow.com/questions/34821327/implementation-of-blocked-dates-for-a-user-event-orm-model – Vitaly Stanchits Jan 15 '16 at 23:01
@VitalyStanchits: But you didn't. All the the essential information I have asked for is not there. – Erwin Brandstetter Jan 15 '16 at 23:09

Find entry where value does not intersect with other value

2 Answers2

Linked