I would like to find overlapping data with a Squeryl query. I can do so by using the method found here with normal SQL, but can't figure out how to do so using Squeryl.
Basically I need to convert this line that finds Non-Distinct rows to Squeryl
SELECT *
FROM myTable L1
JOIN(
SELECT myField1,myField2
FROM myTable
GROUP BY myField1,myField2
HAVING COUNT(*) >= 2
) L2
ON L1.myField1 = L2.myField1 AND L1.myField2 = L2.myField2;
EDIT : More importantly I need to be able to do this dynamically. I have a bit of a complex dynamic query that I call that may rely on different options being passed. If an Option is defined then it should call this, otherwise inhibit if null. But groupBy does not support an inhibitBy method. To see a full explanation of my current method look here
def getAllJoined(
hasFallback:Option[String] = None
showDuplicates: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) =>
where(
main.fallBack.isNotNull.inhibitWhen(!hasFallback.isDefined)
)
//What to do here to only find duplicates when showDuplicates.isDefined? AKA Non-Distinct
select(main,attr1,attr2,attr3,attr4,attr5,attr6,attr7)
on(
(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
