fukamachi/sxql

Nested calls of internal functions generate incorrect SQL in ‘where’

Closed this issue · 3 comments

lu4nx commented 
(select (fields (:as (:strftime  "%m" (:datetime :add_date)) :month)
                                        (:count 1))
                          (from :books)
                          (where (:= (:strftime "%Y" (:datetime :add_date))
                                     (:strftime "%Y" (:datetime "now"))))
                          (group-by :month)
                          (order-by :month))

Generate an SQL statement:

SELECT STRFTIME('%m', DATETIME(add_date)) AS month, COUNT(1) FROM books WHERE (STRFTIME('%Y', DATETIME(add_date)) = STRFTIME('%Y', DATETIME('now'))) GROUP BY month ORDER BY month

The where statement looks wrong, this should be:

WHERE (STRFTIME('%Y', DATETIME(add_date) = STRFTIME('%Y', DATETIME('now'))))
fukamachi commented

Is this what you're trying to do?

(sxql:where (:strftime "%Y" (:= (:datetime :add_date) (:strftime "%Y" (:datetime "now")))))
;=> #<SXQL-CLAUSE: WHERE STRFTIME('%Y', (DATETIME(add_date) = STRFTIME('%Y', DATETIME('now'))))>
lu4nx commented

Is this what you're trying to do?

(sxql:where (:strftime "%Y" (:= (:datetime :add_date) (:strftime "%Y" (:datetime "now")))))
;=> #<SXQL-CLAUSE: WHERE STRFTIME('%Y', (DATETIME(add_date) = STRFTIME('%Y', DATETIME('now'))))>

This is I want to do:

strftime('%Y', datetime(add_date))=strftime('%Y', datetime('now'))
fukamachi commented

I don't have time to experiment for you. Write the exact problem you have. If you like to make it the exact same way you write, stop using SxQL and use a string for SQLs.

  • 👍1