Clojurians Log v2

It's impossible in general. Unless for some reason you need a fully correct automated process, I'd suggest to just use ChatGPT and validate its results.

But this is the right place for such questions.

I started to implement something like this a couple of years ago using Instaparse. I never finished it and I quickly discovered that it’s probably not worth trying to include the full breadth of SQL. I ended up targeting my very specific use case. It worked very well for that

I just want to target a specific use case (not cover the breadth of ANSI SQL) so if there are any open source libraries like that I would love to see them! If not I can whip something up too, just wanted to save some time is all 😃

As I recall, I used Instaparse to parse the sql, a little meander for the data structure manipulation and i validated the result against a malli schema. I’m afraid I don’t remember much else. Hope this helps

And yea, I am using a LLM to convert natural language into SQL filter (WHERE) statements, then converting those into HoneySQL. I could again try to use the LLM for this but I need specific results and cannot validate them by hand every time. It would just be quicker to parse them out! And that does help thanks!

I could also of course fine tune my own LLM/Model instead of parsing it on my own but choosing my battles here

Just in case - why is the conversion needed in the first place?

Generating honeysql from the free LLMs I have used is not as good as generating SQL. I could fine tune them, but it would probably take me more time to get that correct than to write a simple parser for the SQL

What I meant is - why not use SQL directly, as a string?

Oh! Because the users are not engineers, and I do that already with a filter builder but it gets complex with how many filters people add, and the logic is hard to follow for not developers.

There are no set fields, the filters are dynamic based on the fields they have

I'm still not sure we're on the same page regarding my question. I'm not asking for anyone to write SQL. Someone uses natural language to make some LLM create an SQL string. And then that string gets used by Clojure code - without HoneySQL anywhere in the process.

The short answer: the current query I have already joins a bunch of tables together and we just tack on the filters to end. And it would be a much larger lift to redo all that.

Ah, so you need to post-process the query, alright.

Yea, just thought it would be nicer to change the UI to not use the gnarly filter blocks for regular users

Thanks for talking me through that!

FWIW I would probably still have some kind of "gnarly filter blocks" in there, even if they're hidden. • An LLM can then be asked not to create a full-on SQL query string but to create some e.g. JSON data structure similar to what MongoDB uses, just to reduce the chance of it breaking • Whatever reads the result of the LLM would then convert it to a data structure that can be used by the gnarly filter blocks UI • The users will have a switch in the UI - to see their entered prompt or to see the resulting blocks (or to see the blocks instead of entering a prompt in NL - that would definitely be my personal preference) • Only that data structure will have to be converted to HoneySQL - no need for SQL parsing • Significantly reduced chance of creating a query that does something bad

That makes a lot of sense, and for compatibility with the existing backend I could just make an adapter from the intermediate format if needed to the one we already had...

FWIW, at work we use Instaparse to support an "English-like" query language specific to our domain, and then we translate the AST into 1) Elastic Search queries, 2) predicate functions (anonymous Clojure functions that can be applied to data structures), and 3) SQL queries via HoneySQL.

That allows our business team to write a query like is male and has photos and registered between 30 and 60 days and we can run queries against Elastic Search, MySQL, or test whether a given user profile satisfies that query.

That is worth a lot actually! Thanks everyone 😃 I am learning a lot

This is part of our AST to HoneySQL code:

:male [:= :gender [:inline "Male"]]
    :female [:= :gender [:inline "Female"]]
    :approved [:= :statusid [:inline 1]]
    :rejected [:= :statusid [:inline 2]]
    :new [:= :statusid [:inline 3]]
    :platinum (do
                (swap! *sql-add-ons* assoc :join-active-membership
                       [:active_membership
                        [:and
                         [:= :user.id :active_membership.member_id]
                         [:= :active_membership.active [:inline 1]]
                         [:> :active_membership.date_expires [:now]]]])
                [:<> :active_membership.id nil])
    :has (let [expr (keyword (second ast))]
           (if (= :photos expr)
             [:> expr [:inline 0]]
             [:= expr [:inline 1]]))

(cond-> {:select [:user.*] :from :user}
        where
        (assoc :where where)
        (:join-active-membership @*sql-add-ons*)
        (update :left-join (fnil into []) (:join-active-membership @*sql-add-ons*))
        (:join-transaction @*sql-add-ons*)
        (update :left-join (fnil into []) (:join-transaction @*sql-add-ons*))
...

The :and-rules AST expands into [:and ...] HoneySQL like this:

:and-rules (into [:and] (map (ast-expand sql-producer data) (rest ast)))

Where the ast-expand function generically handles and/or/not logic expansion and simplification, and the *-producer functions are for Elastic Search, SQL, and predicates.

The predicate-producer looks like this -- same AST, different output:

(case (first ast)
    ;; partially expanded:
    :or-rules (apply some-fn (map (ast-expand predicate-producer data) (rest ast)))
    :and-rules (apply every-pred (map (ast-expand predicate-producer data) (rest ast)))
    :not' (let [expr (second ast)]
            (if (string? expr)
              #(not (->boolean (get-element % expr)))
              (complement expr))) ; already canonicalized
    ;; low-level:
    :in #(contains? (let [items (expand-vars ast data)]
                      (if (string? items)
                        #{}
                        (into #{} (map maybe-number items))))
                    (get-element % (second ast)))
    :equality #(= (get-element % (second ast))
                  (maybe-number (expand-var2 ast data)))
    :in-equality #(not= (get-element % (second ast))
                        (maybe-number (expand-var2 ast data)))
    :age (let [[from to] (map maybe-number (rest ast))]
           #(<= from (field/calc-age (field/date-of-birth %)) to))

Here's the matching chunk for predicates to the SQL stuff above:

:male #'field/male?
    :female (complement #'field/male?)
    :approved #'field/approved?
    :rejected #'field/rejected?
    :new #'field/new?
    :platinum #((requiring-resolve 'ws.billing-shared.interface/active-membership?)
                *application-component*
                (:id %))
    :has (let [expr (second ast)]
           (if (= "photos" expr)
             (comp pos? :photos)
             #(->boolean (get-element % expr))))

https://github.com/plooney81/nectar-sql just sharing because it wasn't mentioned before here (afaict)