Mito and SXQL: count with select-dao?

user_1506338 0
Viewed 70

With Mito and SxQL, we have count-dao 'model, which doesn't accept a (sxql:select …) clause.

How can we use a (select-dao 'model), our select to filter results, and count the number of rows?

For example, I'd like to count this:

(mito:select-dao 'loan
    (sxql:where (:< :due-date (local-time:now))))
common-lisp
1 Answers

That should do:

(sxql:select
    ((:as (:count :*) :count))
  (sxql:from (sxql:make-sql-symbol (mito.class.table:table-name (find-class 'user))))
  (sxql:where
   (:< :due-date (local-time:now))))
;; => #<SXQL-STATEMENT: SELECT COUNT(*) AS count FROM user WHERE (due-date < '2021-10-05 00:27:23.010665')>

The (mito.class.table:table-name (find-class 'user)) part is to convert from a symbol representing th DAO to a string which contains the actual name of the SQL table.

Then you pass the name of the table to sxql:make-sql-symbol to make the string usable by sxql.

EDIT:

If you want to have the date format "YYYY-MM-DD", you can do

(local-time:format-timestring
 nil                    ; output to string
 (local-time:now)
 :format local-time:+iso-8601-date-format+) ; using  a pre-defined format
user_15644930
Related