search

queryverse / QuerySQLite.jl

SQLite backend for Query.jl
Other
4 stars 2 forks source link

Systematic way of dealing with nested queries #7

Open davidanthoff opened 4 years ago

davidanthoff commented 4 years ago

I tried this query on the Chinook db:

db.Track |> @select(:AlbumId, :Composer) |> @mutate(bar = _.AlbumId * 2)

and got an error. Should that work?

bramtayl commented 4 years ago

This is getting close to being user ready. I need to add some docs and write up the blog posts. Sorry it's been a busy semester.

I tried running the code, and it generates the following:

result = database.Track |> @select(:AlbumId, :Composer) |> @mutate(bar = _.AlbumId * 2)
translate(result.code)
SELECT AlbumId AS AlbumId, Composer AS Composer, AlbumId * 2 AS bar SELECT AlbumId AS AlbumId, Composer AS Composer FROM (Track)

Which is pretty close to being reasonable looking SQL code. I have already have an implementation of a nested query for the GROUP_BY translation, but it seems like it might need a more general implementation.

bramtayl commented 4 years ago

Ok, this works on master, so I'm closing it, but not a very satisfying solution. A more satisfying one would be a way to determine when SQL expressions need to be specifically marked as nested. I wonder how much would be lost if all SQL expressions were assumed to be nested? Can the SQLite optimizer optimize them away?