A use case for DISTINCT

[yurkobb]

I'm using a secondary table to store tags/labels attached to the primary table, and I want to be able to query based on these tags such that if it happens that multiple tags match a single entry, that entry is returned only once. Here's a simplified example:

#lang racket/base

(require db
         deta
         threading)

(define conn
  (postgresql-connect #:database "testing"
                      #:user "testing"
                      #:password "<random string>"))

(query-exec conn "CREATE TEMPORARY TABLE deta_items (id INT PRIMARY KEY, sku VARCHAR(250))")
(query-exec conn (string-append
                  "CREATE TEMPORARY TABLE deta_tags ("
                  "  item INT REFERENCES deta_items,"
                  "  tag VARCHAR(250),"
                  "  PRIMARY KEY(item, tag))"))
(query-exec conn "INSERT INTO deta_items VALUES (1, 'hello')")
(query-exec conn "INSERT INTO deta_tags VALUES (1, 'greeting'), (1, 'singleword')")

(define query-tags '("greeting" "singleword"))

(printf "deta: ~a~n"
        (query-rows conn (~> (from "deta_items" #:as di)
                             (join "deta_tags" #:as dt #:on (= di.id dt.item))
                             (where (= dt.tag (any ,query-tags)))
                             (select di.id di.sku))))

(printf "manual / distinct: ~a~n"
        (query-rows conn (string-append
                          "SELECT DISTINCT di.id, di.sku FROM deta_items AS di "
                          "JOIN deta_tags AS dt ON dt.item = di.id "
                          "WHERE dt.tag = ANY($1)")
                    query-tags))

Output:

deta: (#(1 hello) #(1 hello))
manual / distinct: (#(1 hello))

I would be happy to try implementing DISTINCT (maybe as a (distinct) form?) if you think it's okay to do so. Though I probably need a little hint at how to approach it.