Squeryl dynamic join clauses

Question

What I'm looking for seems fairly common but I can't seem to figure it out through the Squeryl api. I need to have a conditional piece to my on statement for a join.

def getAllJoined(
  hasFallback:Option[String] = None
  ):List[(Type1,Type2)] = transaction{
  join(mainTable,
    table2,
    table3,
    table3,
    table4.leftOuter,
    table4.leftOuter,
    table5,
    table6)((main, attr1, attr2, attr3, attr4, attr5, attr6, attr7) =>
    select(main,attr1,attr2,attr3,attr4,attr5,attr6,attr7)
    on(
      //if(hasFallback.isDefined) (main.fallBack).isNotNull else 1 === 1.inhibitWhen(true)
      (main.attr1Col === attr1.id) ,
      (main.attr2Col === attr2.id) ,
      (main.attr3Col === attr3.id) ,
      (main.attr4Col === attr4.map(_.id)) ,
      (main.attr5Col === attr5.map(_.id)) ,
      (main.attr6Col === attr6.id) ,
      (main.attr7Col === attr7.id)
      )
  ).toList

I'm not sure how I could get this. I've thought about things like tacking on another on, or doing what I have commented here.

The bottom line is basically if I have this hasFallback flag on, I need to only return objects that have this column not null (defined). Otherwise if the hasFallback flag isn't there, ignore if it's defined or not.

EDIT: It looks like the on method can only take seven parameters. Is there a way around this seven parameter limit?

Also, type mismatch; found : org.squeryl.dsl.boilerplate.JoinQueryYield6[myTuple] required: org.squeryl.dsl.boilerplate.JoinQueryYield7[?] So apparently I'm being forced to have 7 some reason no matter what? No more or no less.

Directly from the squeryl site

If a join has N arguments, the ‘on’ function must take N-1 arguments, the i’th ‘on’ condition corresponds to the i’th table expression :

join(T, A1, A2,... AN)((a1,a2,...,aN) =>
  ...
  on(...condition for a1...,...condition for a2...,......condition for aN..., )
)

SOLUTION: For anyone else that landed here with the same problem, here is the solution using a where clause.

...table6)((main, attr1, attr2, attr3, attr4, attr5, attr6, attr7) =>
where(
    main.fallBack.isNotNull.inhibitWhen(!hasFallback.isDefined)
    )
    select(main,attr1,attr2,attr3,attr4,attr5,attr6,attr7)...

score 1 · Accepted Answer · answered Jun 21 '13 at 05:10

1

If you are simply looking to add a conditional to your where clause, you can do something like this:

on(
   t1.secondary === t2.id and
   (if(hasFallback.isDefined) t1.fallback.isNotNull else 1 === 1.inhibitWhen(true))
)

The inhibitWhen clause should always prevent the output of 1 === 1 from showing up in the actual SQL, but it allows your if/else to return a LogicalBoolean.

answered Jun 21 '13 at 05:10

jcern

7,798
4
39
47

I would test this but apparently `on` can only take 7 parameters... is there a way around this? – damian Jun 21 '13 at 12:19
Hmm, I am not sure. Is the error is that you can't join more than 7 tables, or that any two tables can only be joined by no more than 7 `LogicalBoolean` conditions? Also, is the error originating from the DB? If the latter, I would think you can probably move some of those conditions to the `WHERE` clause. – jcern Jun 21 '13 at 12:55
It was a squeryl issue. I ended up adding the condition you gave into the where clause and it seems to be working. Thanks! – damian Jun 21 '13 at 13:09
Ahh, I see. the `ON` clause takes as many parameters as tables provided, each corresponding to a join between the two tables in the `JOIN` list - otherwise, it wouldn't know which tables to apply that to (the order is important too). I added the `and` to the my query since each `ON` argument can have `AND` and `OR` conditions which combine to return a `LogicalBoolean` for the condition. I'd suggest adding that statement to either the `WHERE` clause or as an `AND` on the first condition: `(main.attr1Col === attr1.id)`. – jcern Jun 21 '13 at 13:10

VasiliNovikov · Answer 2 · 2013-06-20T21:23:20.563

There is a talk related to a similar question on github and a google group:

https://github.com/max-l/Squeryl/pull/168

https://github.com/max-l/Squeryl/pull/144

https://groups.google.com/forum/#!topic/squeryl/BBAXbtRq9v4

If you're using squeryl-0.9.5-6, then you should find a way out by your own. I chose to do (a similar task) the following way:

    /** thin wrappwer with no runtime cost */
    implicit class RichLogicalBoolean(val e: LogicalBoolean) extends AnyVal {
        def and(condition: Boolean, other: LogicalBoolean) = if (condition) other and e else e
    }

after this, I can write a code like:

t1.secondary === t2.id and
    (yourCondition, t1.fallback.isNotNull)

Squeryl dynamic join clauses

2 Answers2

Linked