search

fukamachi / mito

An ORM for Common Lisp with migrations, relationships and PostgreSQL support
284 stars 31 forks source link

Question: how to select, filter (when clause) and count the results? #110

Open vindarel opened 2 years ago

vindarel commented 2 years ago

I'd like to count the number of rows a query returns. For example:

(mito:select-dao 'loan
    (sxql:where (:< :due-date (local-time:now))
    (sxql:count) ;; ??? 
))

count-dao accepts a model and doesn't accept a where.

Can I do this with select-dao? Do I have to rely on pure SxQL queries?

Thank you very much.

related: https://github.com/fukamachi/sxql/issues/47

fukamachi commented 2 years ago

Good point. It's complicated a little.

(getf (mito:retrieve-by-sql
        (select ((:count :*))
          (from :loan)
          (where (:< :due_date (local-time:now)))))
      :count)

As this is a common pattern, perhaps it would be better to improve mito:count-dao or add another function.

vindarel commented 2 years ago

thank you very much for the answer. I admit it is not so easy.

With this example I get:

malformed property list: ((:|COUNT(*)| 0)).

because the result of the SQL looks like this

((:|COUNT(*)| 0))

(I just use (cadr (car …)))