Embedding a fragment into a query?

[adamw]

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]

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 <#34>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/ABQOKNSQQRA7B67LGCHFGXDZQCYBHAVCNFSM6AAAAABMCELABCVHI2DSMVQWIX3LMV43ASLTON2WKOZSGQ2TANRVGY4DGNQ> . You are receiving this because you are subscribed to this thread.Message ID: ***@***.***>

[adamw]

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.

[AugustNagro]

Good point @adamw . Giving this some more thought, I've opened two WIP merge requests:

1. Support Frag parameters in the sql interpolator: WIP: Embed frags in sql interpolator #51

  test("embed Frag into Frag"):
    def findPersonCnt(filter: Frag)(using DbCon): Int =
      sql"SELECT count(*) FROM person WHERE $filter".query[Int].run().head
    val isAdminFrag = sql"is_admin = true"
    connect(ds()):
      val johnCnt = findPersonCnt(sql"first_name = 'John' AND $isAdminFrag")
      assertEquals(johnCnt, 2)

Pros

• Simple implementation
• Very general purpose and covers all statement types

Cons:

• Can be abused making the SQL layer a complicated mess. I've seen before in other slick/doobie projects and it was a main rational for the Spec class.

2. Enhance Spec to support a user-supplied 'prefix' Frag that ends before the WHERE clause. (WIP MR: WIP: Support prefixes in Spec #52 )

  test("spec with prefix"):
    val age = 3
    val frag = Spec[User](sql"SELECT * FROM user")
      .where(sql"age > $age")
      .build
    assertEquals(frag.sqlString, "SELECT * FROM user WHERE (age > ?)")
    assertEquals(frag.params, Vector(age))

Pros:

• Allows one to use Specs without database views if joins are required

Cons:

• Still needs some design thought and only covers dynamic queries (which is however the most common case).

Right now I think both would be valuable. LMK your thoughts.

[adamw]

Yeah I think both proposals are supplementary. Combining SQLs from fragments is kind of a last-resort thing - discouraged, dangerous, but good to have that "ultimate flexibility" when possible. So I think I would like to have that option. But I agree that using Specs should be the way to go as long as possible - after all, this gives us type safety. Probably documenting such a "gradual degradation path" of safety (Spec - Spec with prefix - Embedding fragments) will still cause some people to misuse the features, but what can you do ... ;)