Filtering

HoneyEQL supports filtering using the :where parameter. This parameter takes the value similar to HoneySQL's a where clause except that instead of column name, we'll be using the attribute ident.

; :eql.mode/lenient syntax
(heql/query
  db-adapter
  {[[] 
    ; HoneySQL: {:where [:= city_id 3]}
    {:where [:= :city/city-id 3]}]
    [:city/city]})

; :eql.mode/strict syntax
(heql/query
  db-adapter
  `[{([] 
     ; HoneySQL: {:where [:= city_id 3]}
     {:where [:= :city/city-id 3]}) 
     [:city/city]}])

Some sample queries

; Not Equal To

; :eql.mode/lenient syntax
{[[] {:where [:<> :language/name "English"]}]
 [:language/name]}

; :eql.mode/strict syntax
[{([] {:where [:<> :language/name "English"]}) 
  [:language/name]}]

; Greater than

; :eql.mode/lenient syntax
{[[] {:where [:> :payment/amount 11.99M]}]
 [:payment/rental-id]}

; :eql.mode/strict syntax
[{([] {:where [:> :payment/amount 11.99M]}) 
  [:payment/rental-id]}]

; Greater than and equal to

; :eql.mode/lenient syntax
{[[] {:where [:>= :payment/amount 11.99M]}]
 [:payment/rental-id]}

; :eql.mode/strict syntax
[{([] {:where [:>= :payment/amount 11.99M]}) 
  [:payment/rental-id]}]

; Less than

; :eql.mode/lenient syntax
{[[] {:where [:< :payment/amount 11.99M]}]
 [:payment/rental-id]}

; :eql.mode/strict syntax
[{([] {:where [:< :payment/amount 11.99M]}) 
  [:payment/rental-id]}]

; Less than and equal to

; :eql.mode/lenient syntax
{[[] {:where [:<= :payment/amount 11.99M]}]
 [:payment/rental-id]}

; :eql.mode/strict syntax
[{([] {:where [:<= :payment/amount 11.99M]}) 
  [:payment/rental-id]}]

; like

; :eql.mode/lenient syntax
{[[] {:where [:like :actor/first-name "Jo%"]}]
 [:actor/first-name]}

; :eql.mode/strict syntax
[{([] {:where [:like :actor/first-name "Jo%"]}) 
  [:actor/first-name]}]

; ilike (NOTE: works only with Postgres)

; :eql.mode/lenient syntax
{[[] {:where [:ilike :actor/first-name "Jo%"]}]
 [:actor/first-name]}

; :eql.mode/strict syntax
[{([] {:where [:ilike :actor/first-name "Jo%"]}) 
  [:actor/first-name]}]

; using database's pre-defined functions

; :eql.mode/lenient syntax
{[[] {:where [:= :actor/actor-id [:abs :actor/actor-id]]}]
 [:actor/first-name]}

; :eql.mode/strict syntax
[{([] {:where [:= :actor/actor-id [:abs :actor/actor-id]]}) 
  [:actor/first-name]}]

Date, Time & TimeStamp values can be used either as string or the using their corresponding type defined in this mapping.

; Between two timestamps as strings

; :eql.mode/lenient syntax
{[[] {:where [:between :payment/payment-date "2005-08-23T21:00:00" "2005-08-23T21:03:00"]}] 
 [:payment/rental-id]}

; :eql.mode/strict syntax
[{([] {:where [:between :payment/payment-date "2005-08-23T21:00:00" "2005-08-23T21:03:00"]}) 
  [:payment/rental-id]}]

; Between two timestamps as LocalDateTime

; :eql.mode/lenient syntax
(let [from (LocalDateTime/parse "2005-08-23T21:00:00")
      to   (LocalDateTime/parse "2005-08-23T21:03:00")]
  (heql/query db-adapter
              {[[] {:where [:between :payment/payment-date from to]}] 
                [:payment/rental-id]}))

; :eql.mode/strict syntax
(let [from (LocalDateTime/parse "2005-08-23T21:00:00")
      to (LocalDateTime/parse "2005-08-23T21:03:00")]
  (heql/query db-adapter
              `[{([] {:where [:between :payment/payment-date ~from ~to]}) 
                [:payment/rental-id]}]))

The same logic applies for UUIDs as well

; in filter with implicit type coercion

; :eql.mode/lenient syntax
{[[] {:where [:in :customer/id ["847f09a7-39d1-4021-b43d-18ceb7ada8f6"  
                                "e5156dce-58ff-44f5-8533-932a7250bd29"]]}]
 [:customer/first-name]}

; :eql.mode/strict syntax
[{([] {:where [:in :customer/id ["847f09a7-39d1-4021-b43d-18ceb7ada8f6"  
                                "e5156dce-58ff-44f5-8533-932a7250bd29"]]}) 
 [:customer/first-name]}]

; not-in filter with explicit type

; :eql.mode/lenient syntax
(let [customer-ids [#uuid "847f09a7-39d1-4021-b43d-18ceb7ada8f6"
                    #uuid "e5156dce-58ff-44f5-8533-932a7250bd29"]]
  (db/query 
   db-adapter 
   {[[] {:where [:not-in :customer/id customer-ids]}] 
    [:customer/first-name]}))

; :eql.mode/strict syntax
(let [customer-ids [#uuid "847f09a7-39d1-4021-b43d-18ceb7ada8f6"
                    #uuid "e5156dce-58ff-44f5-8533-932a7250bd29"]]
  (db/query 
   db-adapter 
   `[{([] {:where [:not-in :customer/id ~customer-ids]}) 
      [:customer/first-name]}]))

We can also filter the results using logical operators and, or & not.

; :eql.mode/lenient syntax
{[[] {:where [:and 
                [:= :payment/customer-id 1] 
                [:> :payment/amount 5.99M]]}]
 [:payment/payment-id :payment/amount]}  

; :eql.mode/strict syntax
[{([] {:where [:and 
                [:= :payment/customer-id 1] 
                [:> :payment/amount 5.99M]]}) 
  [:payment/payment-id :payment/amount]}]  

; :eql.mode/lenient syntax
{[[] {:where [:or 
                [:= :language/name "English"] 
                [:= :language/name "French"]]}]
 [:language/id :language/name]} 

; :eql.mode/strict syntax
[{([] {:where [:or 
                [:= :language/name "English"] 
                [:= :language/name "French"]]}) 
  [:language/id :language/name]}] 

; :eql.mode/lenient syntax
{[[] {:where [:not 
                [:or 
                  [:= :language/name "English"] 
                  [:= :language/name "French"]]]}] 
 [:language/language-id :language/name]}

; :eql.mode/strict syntax
[{([] {:where [:not 
                [:or 
                  [:= :language/name "English"] 
                  [:= :language/name "French"]]]}) 
  [:language/language-id :language/name]}]

Filter Based On Relationship Attributes

With HoneyEQL, we can filter the results based on the attributes of a relationship. The only difference in the syntax is, in the place of the attribute ident, we will be using a vector representing the filter path that can contain one or more relationship attributes followed by a scalar attribute.

For example, to get all the cities of a county using the country' name,

we can use the following query.

; filtering by one-to-one relationship attribute

; :eql.mode/lenient syntax
{[[] {:where [:= [:city/country :country/country] "Algeria"]}]
 [:city/city-id :city/city]}

{[[] {:where [:= [:address/city :city/country :country/country] "Algeria"]}]
 [:address/address]}

; :eql.mode/strict syntax
[{([] {:where [:= [:city/country :country/country] "Algeria"]}) 
  [:city/city-id :city/city]}]

[{([] {:where [:= [:address/city :city/country :country/country] "Algeria"]}) 
  [:address/address]}]

If the relationship attribute is refers a one-to-many or many-to-many relationship, the filter condition yield the results if any of the related entities satisfy the condition.

For the above schema, we can get a list of countries which has at-least one city that starts with Ab.

; filtering by one-to-many relationship attribute

; :eql.mode/lenient syntax
{[[] {:where [:like [:country/cities :city/city] "Ab%"]}]
 [:country/country-id :country/country]}

; :eql.mode/strict syntax
[{([] {:where [:like [:country/cities :city/city] "Ab%"]}) 
  [:country/country-id :country/country]}]

For many-to-many relationships also, the query looks similar.

For the above schema, to get the actors who are part of at-lease one film which has the word LIFE in its title.

; filtering by many-to-many relationship attribute

; :eql.mode/lenient syntax
{[[] {:where [:like [:actor/films :film/title] "%LIFE%"] }]
 [:actor/first-name :actor/last-name]}

; :eql.mode/strict syntax
[{([] {:where [:like [:actor/films :film/title] "%LIFE%"] }) 
  [:actor/first-name :actor/last-name]}]

If we want to retrieve only certain entities only if all of its related entities satisfy the condition, then we need to used the :not and the reverse of the filter condition together.

Let's assume that we have a schema like below

To filter authors who has at-least one course with the rating 5, we can achieve it using the following query.

; :eql.mode/lenient syntax
{[[] {:where [:= [:author/courses :course/rating] 5]}] 
  [:author/first-name :author/last-name]}

; :eql.mode/strict syntax
[{([] {:where [:= [:author/courses :course/rating] 5]}) 
  [:author/first-name :author/last-name]}]

If we want to filter only the authors who has got the rating 5 in all their courses, we can achieve it by

; :eql.mode/lenient syntax
{[[] {:where [:not [:<> [:author/courses :course/rating] 5]]}]
  [:author/first-name :author/last-name]}

; :eql.mode/strict syntax
[{([] {:where [:not [:<> [:author/courses :course/rating] 5]]}) 
  [:author/first-name :author/last-name]}]

Filter Based On Relationship Attributes Existence

Using HoneyEQL, we can also filter by the existence of relationship attributes.

For the above author-course schema, if we want to filter the authors who have at-least one course, we can query it as

; :eql.mode/lenient syntax
{[[] {:where [:exists :author/courses]}]
 [:author/first-name :author/last-name]}

; :eql.mode/strict syntax
[{([] {:where [:exists :author/courses]}) 
 [:author/first-name :author/last-name]}]

The reverse is also possible by using it in conjunction with :not. i.e filtering authors who don't have any courses

; :eql.mode/lenient syntax
{[[] {:where [:not [:exists :author/courses]]}]
 [:author/first-name :author/last-name]}

; :eql.mode/strict syntax
[{([] {:where [:not [:exists :author/courses]]}) 
 [:author/first-name :author/last-name]}]

Filtering Relationships

We can filter the relationships as well!

; :eql.mode/lenient syntax
{[:country/country-id 2] 
 [:country/country
  ; filtering one-to-many relationship
  {[:country/cities {:where [:= :city/city "Batna"]}] 
   [:city/city-id :city/city]}]}

; :eql.mode/strict syntax
[{[:country/country-id 2] 
  [:country/country
   ; filtering one-to-many relationship
   {(:country/cities {:where [:= :city/city "Batna"]}) 
    [:city/city-id :city/city]}]}]

; :eql.mode/lenient syntax
{[:actor/actor-id 148] 
 [:actor/first-name
  ; filtering many-to-many relationship
  {[:actor/films {:where [:= :film/title "SEA VIRGIN"]}] 
   [:film/title]}]}

; :eql.mode/strict syntax
[{[:actor/actor-id 148] 
  [:actor/first-name
   ; filtering many-to-many relationship
   {(:actor/films {:where [:= :film/title "SEA VIRGIN"]}) 
    [:film/title]}]}]