Some useful CLSQL helper functions

Quite often, you need to query the database for when all values equal something or the other. For example, you might look for an authenticated user as follows (in SQL):

SELECT USERS.* FROM USERS WHERE USER_LOGIN='sohail' AND USER_PASSWORD='myhashedpassword'

Of course, in the year 2008, no one writes their own SQL anymore (or so Rails propaganda would have you believe!) So you use something like CLSQL and end up writing something like:

(clsql:select 'user :where [ and [ = [ user-login ] "sohail" ] [ user-password ] 'myhashedpassword' ])

Certainly more Lispy but still quite annoying. The Rails and Django guys have you write something that looks like:

ModelObject.find(id=5)

I've written a couple of helper functions for CLSQL (reproduced below) that let you write:

CL-USER> (find-one 'user :user-login "sohail" :user-password "myhashedpassword")
#<USER {B47D0C9}>
CL-USER> (find-all 'user :user-site-id 3)
(#<USER {B729209}> #<USER {B72AD81}>)

Makes it a lot easier to write bespoke queries without resorting to CLSQL's bracket syntax or SQL itself.

The code is here (would appreciate any comments as to how to clean it up a bit!):

(defun find-all (type &rest args &key (clause-op 'and) &allow-other-keys)
  (if (> (length args) 0) ;; if there are any filters to apply
      (let ((expressions (loop for (k v) on args by #'cddr
                              collect
                              (let ((op '=)
                                    (value v))
                                (make-instance 'clsql-sys:sql-relational-exp
                                               :operator op
                                               :sub-expressions
                                               (list (make-instance 'clsql-sys:sql-ident-attribute :name k :qualifier nil :type nil)
                                                     value))))))
        (clsql:select type :flatp t
                      :where (make-instance 'clsql-sys:sql-relational-exp
                                            :operator clause-op
                                            :sub-expressions expressions)))
      (clsql:select type :flatp t)))


(defun find-one (&rest args)
  (let ((result (apply #'find-all args)))
    (if (= 1 (length result))
        (first result)
        (error "More than one result returned when only one expected!"))))

Enjoy!