CODEWITHSUNDEEP
datomic
Stepan Parunashvili
Stepan Parunashvili

Reputation: 2845

SQL LIKE operator in datomic

I want to run a sql query, that given a search keyword, will find all users, where their name matches that pattern. i.e in raw SQL something like WHERE users.name LIKE "%foo%"

How owuld I go about doing that?

Current structure of query -> 

(defn find-users [db, search]
  (->> (d/q '[:find ?u :where
              [?u :user/uuid ?id]
              [?u :user/name ..]
            db)
      (map first)))

Upvotes: 2

Views: 1038

Answers (2)

G__
G__

Reputation: 7111

Use the fulltext function. The following example from the Datomic docs illustrates it well:

;; query
[:find ?entity ?name ?tx ?score
 :in $ ?search
 :where [(fulltext $ :artist/name ?search) [[?entity ?name ?tx ?score]]]]

;; inputs
db, "Jane"

;; result
#{[17592186047274 "Jane Birkin" 2839 0.625] 
  [17592186046687 "Jane" 2267 1.0] 
  [17592186047500 "Mary Jane Hooper" 3073 0.5]}

You'll also want to index the fields you're searching for fulltext search in your schema with :db/fulltext true

Upvotes: 2

adamneilson
adamneilson

Reputation: 758

This is what I use. Maybe you can adapt it to your needs.

(defn find-items "Full text search titles and descriptions for [search-term]" [search-term]

    (let [keyys [:item-id :title :description]
          rules '[[(finditem ?item ?term) [(fulltext $ :item/title ?term) [[?item ?name]]]]
                  [(finditem ?item ?term) [(fulltext $ :item/description ?term) [[?item ?name]]]]]
          items (d/q '[:find ?item ?title ?description 
                       :in $ ?term % 
                       :where 
                       (finditem ?item ?term) 
                       [?item :item/title ?title] 
                       [?item :item/description ?description]]
                     (d/db db/CONN) 
                     search-term rules)]
       (map #(zipmap keyys %) items)))

This uses rules which you can have a read about here: http://docs.datomic.com/query.html. Rules work as a pretty good SQL OR equivalent which is how I'm searching for a needle in two haystacks in the above example.

Upvotes: 2

Related Questions