search

AugustNagro / magnum

A 'new look' for database access in Scala
Apache License 2.0
153 stars 10 forks source link

Embedding a fragment into a query? #34

Open adamw opened 1 month ago

adamw commented 1 month ago

In my first attempts to constructing dynamic queries, I had something like:

def findFiltered(filter: Frag) = sql"SELECT * FROM table WHERE $filter"

findFiltered(sql"age = 12")

However, it turns out that you can't embed Frags into interpolated sql queries. Is this by design?

I've since discovered Spec, which for now meets my needs, but I suspect that once dynamic queries with JOINs or such come into play, Spec might no longer be enough.

AugustNagro commented 1 month ago

However, it turns out that you can't embed Frags into interpolated sql queries. Is this by design?

It's definitely something we can implement, but hopefully the Spec abstraction will cover your dynamic query use case.

I've since discovered Spec, which for now meets my needs, but I suspect that once dynamic queries with JOINs or such come into play, Spec might no longer be enough.

When joins are required, create a database view, and then build a Spec against the view.

On Tue, Aug 6, 2024, 4:33 AM Adam Warski @.***> wrote:

In my first attempts to constructing dynamic queries, I had something like:

def findFiltered(filter: Frag) = sql"SELECT * FROM table WHERE $filter"

findFiltered(sql"age = 12")

However, it turns out that you can't embed Frags into interpolated sql queries. Is this by design?

I've since discovered Spec, which for now meets my needs, but I suspect that once dynamic queries with JOINs or such come into play, Spec might no longer be enough.

— Reply to this email directly, view it on GitHub https://github.com/AugustNagro/magnum/issues/34, or unsubscribe https://github.com/notifications/unsubscribe-auth/ABQOKNSQQRA7B67LGCHFGXDZQCYBHAVCNFSM6AAAAABMCELABCVHI2DSMVQWIX3LMV43ASLTON2WKOZSGQ2TANRVGY4DGNQ . You are receiving this because you are subscribed to this thread.Message ID: @.***>

adamw commented 1 month ago

Hm well views seem to be quite a "heavyweight" solution, as that's a database object that you have to first create. So simply "writing a query and getting results" becomes much harder. Views are definitely a solution, but I think it would be great if magnum would be less opinionated here and allow for constructing ad-hoc complex queries as well.