I have the following table definition (simplified):
class Houses(tag: Tag) extends Table[HouseRow](tag, "HOUSE") {
def houseId = column[Long]("HOUSE_ID", O.NotNull, O.PrimaryKey, O.AutoInc)
def houseName = column[String]("HOUSE_NAME", O.NotNull)
def houseType = column[String]("HOUSE_TYPE", O.NotNull)
def uniqueHouseName = index("UQ_HOUSE_NAME_HOUSE_TYPE", (houseName, houseType), true)
def * = (houseId, houseName, houseType) <> (HouseRow.tupled, HouseRow.unapply)
}
val houses = TableQuery[Houses]
I'd like to select houses that match on a set of the uniqueHouseName index as follows.
case class HouseKey(houseName: String, houseType: String)
val houseKeys: Seq(HouseKey("name1", "type1"), HouseKey("name2", "type2"))
A naive inSetBind
filter will match on for eg. HouseRow(ID, "name1", "type2")
which is incorrect.
In MySql I would do something like:
SELECT * FROM HOUSE h
WHERE(h.HOUSE_TYPE, d.HOUSE_NAME) IN
(
SELECT 'type1' as HOUSE_TYPE, 'name1' as HOUSE_NAME
UNION
SELECT 'type2', 'name2'
);
Like @cvogt version, but doesn't blow up on empty list:
val filteredHouses =
houses.filter(h =>
houseKeys.map(hk => h.houseName === hk.houseName &&
h.houseType === hk.houseType)
.reduceOption(_ || _).getOrElse(false: Rep[Boolean])
)
Tested in slick 3.1.0
Adapting tuxdna's answer to allow arbitrary seqs. This query can however not be precompiled to SQL at the moment and has a runtime overhead.
val filteredHouses =
houses.filter(h =>
houseKeys.map(hk => h.houseName === hk.houseName && h.houseType === hk.houseType)
.reduce(_ || _)
)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With