CODEWITHSUNDEEP
mysqlscalaslick
th3falc0n
th3falc0n

Reputation: 1427

Implementing a complex left join in Scala Slick

I was searching for a specific SQL that I found here on stackoverflow (Get count of rows in table A that have a reference to table B)

However I seem to be unable to implement this SQL solution in Slick.

SELECT device.*, c.cnt
 FROM device 
 LEFT JOIN (SELECT device_id, COUNT(*) AS cnt 
             FROM unit 
             GROUP BY device_id) c 
 ON device.id = c.device_id

My current approach fails with

slick.SlickException: Unexpected node Select _2 -- SQL prefix: select x2.x3, x2.x4, x2.x5, x2.x6,

when trying to run/compile the query

val devicesWithUnitCountResult = dbConfig.db.run(TableQueries.devicesWithUnitCount.result)

Current code:

  def unitCountPerDevice = for {
    (id, length) <- TableQuery[TDUnit].groupBy(k => k.deviceID).map(k => (k._1, k._2.map(_.id).length))
  } yield (id, length)

  def devicesWithUnitCount = for {
    (device, unitcount) <- TableQuery[TDDevice].joinLeft(unitCountPerDevice).on(_.id === _._1).map(k => {
      val u = k._2.getOrElse((0, 0))._2
      (k._1, u)
    })
  } yield (device, unitcount)

Can somebody help me to find out where I am failing here?

Upvotes: 0

Views: 475

Answers (1)

Bhavya Latha Bandaru
Bhavya Latha Bandaru

Reputation: 1108

Try it this way 

def unitCountPerDevice = TableQuery[TDUnit].groupBy(k => k.deviceID).map(k => (k._1, k._2.map(_.id).length))

def devicesWithUnitCount = TableQuery[TDDevice].joinLeft(unitCountPerDevice).on(_.id === _._1).map(d =>(d._1,d._2.map(_._2)))

Upvotes: 2

Related Questions