SQL LIKE statement in datomic

Question

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

How am i doing this?

Current request structure →

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

Answer 1

This is what I use. Perhaps 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))) 

The rules are used here, which you can read here: http://docs.datomic.com/query.html . The rules work as a pretty good equivalent to SQL OR , which is how I look for a needle in two haystacks in the above example.

Answer 2

Use the full text function. The following example from the Datomic docs illustrates this 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 will also want to index the fields you are looking for full-text search in schema with :db/fulltext true