如何将AND添加到联接SLICK [英] How to add AND to the join SLICK
问题描述
我在SLICK中编写查询时遇到问题 这是我对MySql数据库的请求:
I have the problem writing query in SLICK Here is my request to MySql database:
SELECT * FROM readings AS r
JOIN parameters AS p
LEFT JOIN sensorvalues AS sv ON sv.parameter_id=p.id AND sv.reading_id=r.id
如何使用SLICK编写它?确实缺少有关文档中联接的信息.
How can I write it using SLICK ? It is really lack of info on joins in docs.
更新1 我尝试了所有组合,甚至像这样
UPDATE 1 I tried all the combinations even one like this
val q = for{
Join(p,sv) <- Parameters leftJoin SensorValues on (_.id is sv.parameter_id)
r <- Readings if sv.reading_id is r.id
} yield(r,p,sv)
在这种情况下,编译器给我一个错误wrong number of parameters; expected = 2
In this case compiler gives me an error wrong number of parameters; expected = 2
所以我将sv.parameter_id替换为_.parameter_id,现在它讨论了Join 它说:
So I replaced sv.parameter_id to _.parameter_id and now it argues about Join It said that:
constructor cannot be instantiated to expected type; found : models.Join required: (models.Parameters.type, models.SensorValues.type)
我使用的是SLICK的最新版本,不建议使用.我手动导入
I'm using last version of SLICK and it's deprecated. I manually imported
import scala.slick.lifted.Join
,现在看起来像是常规的innerJoin,因为它使用WHERE而不是放在ON后面并放在ON之后.
import scala.slick.lifted.Join
and now it looks like it's regular innerJoin because it uses WHERE instead of putting and after ON.
SQL生成的购买查询:
SQL generated buy query:
select x2.id, x2.platform_id, x2.date, x3.x4, x3.x5, x3.x6, x7.x8, x7.x9, x7.x10, x7.x11 from (select x12.id as x4, x12.name as x5, x12.units as x6 from parameters x12) x3 left outer join (select x13.id as x8, x13.reading_id as x9, x13.parameter_id as x10, x13.value as x11 from sensorValues x13) x7 on x3.x4 = x7.x10, readings x2 where true and (x7.x9 = x2.id)
即使在打开"部分增加了条件,即使这样也很好
Even this one would be good with added condition to the ON section
val readings = for {
all <-Readings join Parameters leftJoin SensorValues on (_._2.id is _.parameter_id) if(all._1._1.id === all._2.reading_id)
} yield (all._1._1,all._1._2,all._2)
但这永远不会发生.
SELECT
x2.x3,
x2.x4,
x2.x5,
x2.x6,
x2.x7,
x2.x8,
x9.x10,
x9.x11,
x9.x12,
x9.x13
FROM
(
SELECT
x14.x15 AS x3,
x14.x16 AS x4,
x14.x17 AS x5,
x18.x19 AS x6,
x18.x20 AS x7,
x18.x21 AS x8
FROM
(
SELECT
x22.`id` AS x15,
x22.`platform_id` AS x16,
x22.`date` AS x17
FROM
`readings` x22
)x14
INNER JOIN(
SELECT
x23.`id` AS x19,
x23.`name` AS x20,
x23.`units` AS x21
FROM
`parameters` x23
)x18
)x2
LEFT OUTER JOIN(
SELECT
x24.`id` AS x10,
x24.`reading_id` AS x11,
x24.`parameter_id` AS x12,
x24.`value` AS x13
FROM
`sensorValues` x24
)x9 ON x2.x6 = x9.x12
WHERE
x2.x3 = x9.x11
代替WHERE
我需要AND
.我应该用什么来提及这种情况,或者在此方面没有任何功能?
instead WHERE
I need AND
. What Should I use to mention this condition or there is no functionality in slick for that?
推荐答案
编辑
OP正在2个表对象的连接中查找按条件链接的.
EDIT
The OP is looking for chained on conditions in a join of 2 table objects.
应编译:
val q = for{
r <- Readings
Join(p,s) <- Params leftJoin Sensors on (
(a,b)=> (a.id is b.parameter_id) && (b.reading_id is r.id)
)
} yield(r,p,s)
原始
尝试这样的事情:
ORIGINAL
Try something like this:
val q = for{
Join(p,s) <- Params leftJoin Sensors on (_.id is s.parameter_id)
r <- Readings if s.reading_id is r.id
} yield(r,p,s)
val result =
q.list.map{ case(r,p,s)=>
SomeCaseClass(r,p,s)
}
这篇关于如何将AND添加到联接SLICK的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!