Extending HoneySQL with Array Intersection
Turns out that Postgres has beautiful support for “array” field types. In one database of mine, I use this to keep track of the recipients of emails:
DROP TABLE IF EXISTS "email_log" CASCADE;
CREATE TABLE "email_log" (
id SERIAL PRIMARY KEY,
sender TEXT NOT NULL,
recipients TEXT[] NOT NULL,
message TEXT,
sent_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
info JSONB);
-- Note the recipients TEXT[] definition: that's an array of texts.
Now for the use-case: a user might have multiple email addresses, and the email_log tracks recipient ADDRESSES not user-ids. My task is to retrieve all emails whose recipients are any of my user’s email addresses. Postgres supports array-intersection logic, and HoneySQL is easy to extend to build me the type of query I need:
(defn pg-intersect-arrays
"Determine whether a Postgres array field interesects a provided collection"
[db-array-field coll]
(let [stringy-coll (map #(str "'" % "'") coll)
;; Strings need to be single-quoted in this postgres syntax,
;; and comma-separated within brackets (next line)
array-format (str "["
(clojure.string/join "," stringy-coll)
"]")]
(sql/raw (str (name db-array-field) " && ARRAY" array-format))))
;; Note the && operator, Postgres for "array intersection"
Then I use it (also using a json operator from another HoneySQL/Postgres blog post of mine) and obtain messages from my email log that include ANY of the user’s email addresses among the recipients array in the database:
(defn mail-notifications-for-user
"retrieve all mail approval requests notifications for `user` and application `application-id` "
[user-id application-id]
(let [email-addresses (map :email (email-addresses-for-user user-id))]
(-> {:select [:*]
:from [:email-log]
:where [:and
(db/pg-intersect-arrays :recipients email-addresses)
[:= (db/pg->> :info :application-id) (str application-id)]
[:= (db/pg->> :info :type) (MESSAGE-TYPES :request)]]}
sql/format
db/dbr)))
