monadic join issue

issue.scala

private def sensorsForRoomsForRange(roomLinks: Seq[SensorRoomLink], from: ZonedDateTime, to: ZonedDateTime): Query[(tables.SensorRoomLinks, tables.SensorDataTable), (SensorRoomLink, UtilizationData), Seq] = {
    import IdTable._
    val resOne = (for {
      (allUtilizationData, groupedByLinkQuery) <- TableQuery[SensorDataTable] join TableQuery[SensorRoomLinks] on (_.sensorId === _.sensorUid) groupBy(_._2)
      groupedByLink <- groupedByLinkQuery
      rl = groupedByLink._2
      d = groupedByLink._1
    } yield rl -> d)
    resOne
  }

runtime exception

[info] - should give the utilization for a sensor by hour *** FAILED ***
[info]   slick.SlickTreeException: Unreachable reference to s2 after resolving monadic joins
[info] | Join Inner : Vector[((t16<{activeEndDateUtc: Option[MappedJdbcType[java.time.ZonedDateTime -> java.sql.Timestamp']], sensorUid: String', activeStartDateUtc: MappedJdbcType[java.time.ZonedDateTime -> java.sql.Timestamp'], sensorRoomLinkId: Long', roomId: Long'}>, Vector[t17<{s18: String', s19: Long', s20: Int', s21: MappedJdbcType[java.time.ZonedDateTime -> java.sql.Timestamp'], s22: MappedJdbcType[java.time.ZonedDateTime -> java.sql.Timestamp'], s23: Option[MappedJdbcType[java.time.ZonedDateTime -> java.sql.Timestamp']], s24: String', s25: MappedJdbcType[java.time.ZonedDateTime -> java.sql.Timestamp'], s26: Long', s27: Long'}>]), t17<{s18: String', s19: Long', s20: Int', s21: MappedJdbcType[java.time.ZonedDateTime -> java.sql.Timestamp'], s22: MappedJdbcType[java.time.ZonedDateTime -> java.sql.Timestamp'], s23: Option[MappedJdbcType[java.time.ZonedDateTime -> java.sql.Timestamp']], s24: String', s25: MappedJdbcType[java.time.ZonedDateTime -> java.sql.Timestamp'], s26: Long', s27: Long'}>)]
[info] |   left s2: < GroupBy > t16 : Vector[(t16<{activeEndDateUtc: Option[MappedJdbcType[java.time.ZonedDateTime -> java.sql.Timestamp']], sensorUid: String', activeStartDateUtc: MappedJdbcType[java.time.ZonedDateTime -> java.sql.Timestamp'], sensorRoomLinkId: Long', roomId: Long'}>, Vector[t17<{s18: String', s19: Long', s20: Int', s21: MappedJdbcType[java.time.ZonedDateTime -> java.sql.Timestamp'], s22: MappedJdbcType[java.time.ZonedDateTime -> java.sql.Timestamp'], s23: Option[MappedJdbcType[java.time.ZonedDateTime -> java.sql.Timestamp']], s24: String', s25: MappedJdbcType[java.time.ZonedDateTime -> java.sql.Timestamp'], s26: Long', s27: Long'}>])]
[info] |     from s28: Bind : Vector[t17<{s18: String', s19: Long', s20: Int', s21: MappedJdbcType[java.time.ZonedDateTime -> java.sql.Timestamp'], s22: MappedJdbcType[java.time.ZonedDateTime -> java.sql.Timestamp'], s23: Option[MappedJdbcType[java.time.ZonedDateTime -> java.sql.Timestamp']], s24: String', s25: MappedJdbcType[java.time.ZonedDateTime -> java.sql.Timestamp'], s26: Long', s27: Long'}>]
[info] |       from s29: Join Inner : Vector[(t32<{s33: String', s34: Long', s35: Int', s36: MappedJdbcType[java.time.ZonedDateTime -> java.sql.Timestamp'], s37: MappedJdbcType[java.time.ZonedDateTime -> java.sql.Timestamp']}>, t38<{s39: Option[MappedJdbcType[java.time.ZonedDateTime -> java.sql.Timestamp']], s40: String', s41: MappedJdbcType[java.time.ZonedDateTime -> java.sql.Timestamp'], s42: Long', s43: Long'}>)]
[info] |         left s30: Bind : Vector[t32<{s33: String', s34: Long', s35: Int', s36: MappedJdbcType[java.time.ZonedDateTime -> java.sql.Timestamp'], s37: MappedJdbcType[java.time.ZonedDateTime -> java.sql.Timestamp']}>]
[info] |           from s44: Table SensorData : Vector[@t45<{sensorUid: String', sensorDataId: Long', value: Int', readDateEnd: MappedJdbcType[java.time.ZonedDateTime -> java.sql.Timestamp'], readDateStart: MappedJdbcType[java.time.ZonedDateTime -> java.sql.Timestamp']}>]
[info] |           select: Pure t32 : Vector[t32<{s33: String', s34: Long', s35: Int', s36: MappedJdbcType[java.time.ZonedDateTime -> java.sql.Timestamp'], s37: MappedJdbcType[java.time.ZonedDateTime -> java.sql.Timestamp']}>]
[info] |             value: StructNode : {s33: String', s34: Long', s35: Int', s36: MappedJdbcType[java.time.ZonedDateTime -> java.sql.Timestamp'], s37: MappedJdbcType[java.time.ZonedDateTime -> java.sql.Timestamp']}
[info] |               s33: Path s44.sensorUid : String'
[info] |               s34: Path s44.sensorDataId : Long'
[info] |               s35: Path s44.value : Int'
[info] |               s36: Path s44.readDateEnd : MappedJdbcType[java.time.ZonedDateTime -> java.sql.Timestamp']
[info] |               s37: Path s44.readDateStart : MappedJdbcType[java.time.ZonedDateTime -> java.sql.Timestamp']
[info] |         right s31: Bind : Vector[t38<{s39: Option[MappedJdbcType[java.time.ZonedDateTime -> java.sql.Timestamp']], s40: String', s41: MappedJdbcType[java.time.ZonedDateTime -> java.sql.Timestamp'], s42: Long', s43: Long'}>]
[info] |           from s46: Table SensorRoomLinks : Vector[@t47<{activeEndDateUtc: Option[MappedJdbcType[java.time.ZonedDateTime -> java.sql.Timestamp']], sensorUid: String', activeStartDateUtc: MappedJdbcType[java.time.ZonedDateTime -> java.sql.Timestamp'], sensorRoomLinkId: Long', roomId: Long'}>]
[info] |           select: Pure t38 : Vector[t38<{s39: Option[MappedJdbcType[java.time.ZonedDateTime -> java.sql.Timestamp']], s40: String', s41: MappedJdbcType[java.time.ZonedDateTime -> java.sql.Timestamp'], s42: Long', s43: Long'}>]
[info] |             value: StructNode : {s39: Option[MappedJdbcType[java.time.ZonedDateTime -> java.sql.Timestamp']], s40: String', s41: MappedJdbcType[java.time.ZonedDateTime -> java.sql.Timestamp'], s42: Long', s43: Long'}
[info] |               s39: Path s46.activeEndDateUtc : Option[MappedJdbcType[java.time.ZonedDateTime -> java.sql.Timestamp']]
[info] |               s40: Path s46.sensorUid : String'
[info] |               s41: Path s46.activeStartDateUtc : MappedJdbcType[java.time.ZonedDateTime -> java.sql.Timestamp']
[info] |               s42: Path s46.sensorRoomLinkId : Long'
[info] |               s43: Path s46.roomId : Long'
[info] |         on: Apply Function = : Boolean
[info] |           0: Path s30.s33 : String'
[info] |           1: Path s31.s40 : String'
[info] |       select: Pure t17 : Vector[t17<{s18: String', s19: Long', s20: Int', s21: MappedJdbcType[java.time.ZonedDateTime -> java.sql.Timestamp'], s22: MappedJdbcType[java.time.ZonedDateTime -> java.sql.Timestamp'], s23: Option[MappedJdbcType[java.time.ZonedDateTime -> java.sql.Timestamp']], s24: String', s25: MappedJdbcType[java.time.ZonedDateTime -> java.sql.Timestamp'], s26: Long', s27: Long'}>]
[info] |         value: StructNode : {s18: String', s19: Long', s20: Int', s21: MappedJdbcType[java.time.ZonedDateTime -> java.sql.Timestamp'], s22: MappedJdbcType[java.time.ZonedDateTime -> java.sql.Timestamp'], s23: Option[MappedJdbcType[java.time.ZonedDateTime -> java.sql.Timestamp']], s24: String', s25: MappedJdbcType[java.time.ZonedDateTime -> java.sql.Timestamp'], s26: Long', s27: Long'}
[info] |           s18: Path s29._1.s33 : String'
[info] |           s19: Path s29._1.s34 : Long'
[info] |           s20: Path s29._1.s35 : Int'
[info] |           s21: Path s29._1.s36 : MappedJdbcType[java.time.ZonedDateTime -> java.sql.Timestamp']
[info] |           s22: Path s29._1.s37 : MappedJdbcType[java.time.ZonedDateTime -> java.sql.Timestamp']
[info] |           s23: Path s29._2.s39 : Option[MappedJdbcType[java.time.ZonedDateTime -> java.sql.Timestamp']]
[info] |           s24: Path s29._2.s40 : String'
[info] |           s25: Path s29._2.s41 : MappedJdbcType[java.time.ZonedDateTime -> java.sql.Timestamp']
[info] |           s26: Path s29._2.s42 : Long'
[info] |           s27: Path s29._2.s43 : Long'
[info] |     by: StructNode : {activeEndDateUtc: Option[MappedJdbcType[java.time.ZonedDateTime -> java.sql.Timestamp']], sensorUid: String', activeStartDateUtc: MappedJdbcType[java.time.ZonedDateTime -> java.sql.Timestamp'], sensorRoomLinkId: Long', roomId: Long'}
[info] |       activeEndDateUtc: Path s28.s23 : Option[MappedJdbcType[java.time.ZonedDateTime -> java.sql.Timestamp']]
[info] |       sensorUid: Path s28.s24 : String'
[info] |       activeStartDateUtc: Path s28.s25 : MappedJdbcType[java.time.ZonedDateTime -> java.sql.Timestamp']
[info] |       sensorRoomLinkId: Path s28.s26 : Long'
[info] |       roomId: Path s28.s27 : Long'
[info] |   right s15: < Path > s2._2 : Vector[t17<{s18: String', s19: Long', s20: Int', s21: MappedJdbcType[java.time.ZonedDateTime -> java.sql.Timestamp'], s22: MappedJdbcType[java.time.ZonedDateTime -> java.sql.Timestamp'], s23: Option[MappedJdbcType[java.time.ZonedDateTime -> java.sql.Timestamp']], s24: String', s25: MappedJdbcType[java.time.ZonedDateTime -> java.sql.Timestamp'], s26: Long', s27: Long'}>]
[info] |   on: LiteralNode true (volatileHint=false) : Boolean
[info]   at slick.compiler.VerifySymbols$$anonfun$apply$1.slick$compiler$VerifySymbols$$anonfun$$verifyScoping$1(VerifySymbols.scala:17)
[info]   at slick.compiler.VerifySymbols$$anonfun$apply$1.slick$compiler$VerifySymbols$$anonfun$$verifyScoping$1(VerifySymbols.scala:35)
[info]   at slick.compiler.VerifySymbols$$anonfun$apply$1.slick$compiler$VerifySymbols$$anonfun$$verifyScoping$1(VerifySymbols.scala:19)
[info]   at slick.compiler.VerifySymbols$$anonfun$apply$1.apply(VerifySymbols.scala:40)
[info]   at slick.compiler.VerifySymbols$$anonfun$apply$1.apply(VerifySymbols.scala:12)
[info]   at slick.compiler.CompilerState.map(QueryCompiler.scala:230)
[info]   at slick.compiler.VerifySymbols.apply(VerifySymbols.scala:12)
[info]   at slick.compiler.QueryCompiler$$anonfun$runPhase$1.apply(QueryCompiler.scala:79)
[info]   at slick.compiler.QueryCompiler$$anonfun$runPhase$1.apply(QueryCompiler.scala:78)
[info]   at scala.util.DynamicVariable.withValue(DynamicVariable.scala:58)

tryingToReplace.scala

sql"""SELECT dbo.SensorRoomLinks.roomId, SUM(dbo.SensorData.[value]) as utilized
          FROM dbo.SensorData
          JOIN dbo.SensorRoomLinks
          ON dbo.SensorData.sensorUid = dbo.SensorRoomLinks.sensorUid
          WHERE dbo.SensorRoomLinks.roomId in (#${roomIds})
          AND (dbo.SensorData.sensorUid in (#$sensorIds))
          AND NOT (readDateEnd <= ${from} OR readDateStart >= ${to})
          GROUP BY dbo.SensorRoomLinks.roomId
      """.as[(Long, Long)].map(_.toMap).map(r => r.map(t => (t._1, t._2 > 0)))