gpt4 book ai didi

sql - ScalaQuery 多对多

转载 作者:行者123 更新时间:2023-12-01 12:54:39 26 4
gpt4 key购买 nike

有人知道如何在 Scala 查询中归档多对多吗?

我想将一篇博文连接到一系列标签。
这是我的数据库设计: enter image description here

我的自定义代码已经成功,但是在检查 Scala 查询生成的 SQL 时,我对我的解决方案不满意。
我尝试了一种函数式方法,它生成了许多 SQL 查询,导致大量往返。
我可以弄清楚如何将查询数量减少大约一半。

一个手工制作的查询,可以在一个查询中获取我所有格式良好的数据,

select 
p.id, p.content, p.posted_date, GROUP_CONCAT(t.name)
from
post p,
tag t,
tag_post tp
where
tp.post_id = p.id and tp.tag_id = t.id
group by
p.id

从 Scala Query 生成的查询提供相同的数据。

SELECT `t1`.`id`,`t1`.`content`,`t1`.`posted_date` FROM `post` `t1`
SELECT `t1`.`tag_id` FROM `tag_post` `t1` WHERE (`t1`.`post_id`=1)
SELECT `t1`.`id`,`t1`.`name` FROM `tag` `t1` WHERE (`t1`.`id`=1)
SELECT `t1`.`id`,`t1`.`name` FROM `tag` `t1` WHERE (`t1`.`id`=2)
SELECT `t1`.`id`,`t1`.`name` FROM `tag` `t1` WHERE (`t1`.`id`=3)
SELECT `t1`.`tag_id` FROM `tag_post` `t1` WHERE (`t1`.`post_id`=2)
SELECT `t1`.`id`,`t1`.`name` FROM `tag` `t1` WHERE (`t1`.`id`=1)
SELECT `t1`.`id`,`t1`.`name` FROM `tag` `t1` WHERE (`t1`.`id`=1)
SELECT `t1`.`id`,`t1`.`name` FROM `tag` `t1` WHERE (`t1`.`id`=2)
SELECT `t1`.`id`,`t1`.`name` FROM `tag` `t1` WHERE (`t1`.`id`=3)
SELECT `t1`.`tag_id` FROM `tag_post` `t1` WHERE (`t1`.`post_id`=3)
SELECT `t1`.`id`,`t1`.`name` FROM `tag` `t1` WHERE (`t1`.`id`=1)
SELECT `t1`.`id`,`t1`.`name` FROM `tag` `t1` WHERE (`t1`.`id`=2)
SELECT `t1`.`id`,`t1`.`name` FROM `tag` `t1` WHERE (`t1`.`id`=3)
SELECT `t1`.`tag_id` FROM `tag_post` `t1` WHERE (`t1`.`post_id`=4)
SELECT `t1`.`id`,`t1`.`name` FROM `tag` `t1` WHERE (`t1`.`id`=1)
SELECT `t1`.`id`,`t1`.`name` FROM `tag` `t1` WHERE (`t1`.`id`=2)
SELECT `t1`.`id`,`t1`.`name` FROM `tag` `t1` WHERE (`t1`.`id`=3)
SELECT `t1`.`tag_id` FROM `tag_post` `t1` WHERE (`t1`.`post_id`=5)
SELECT `t1`.`id`,`t1`.`name` FROM `tag` `t1` WHERE (`t1`.`id`=3)
SELECT `t1`.`id`,`t1`.`name` FROM `tag` `t1` WHERE (`t1`.`id`=1)
SELECT `t1`.`id`,`t1`.`name` FROM `tag` `t1` WHERE (`t1`.`id`=2)
SELECT `t1`.`tag_id` FROM `tag_post` `t1` WHERE (`t1`.`post_id`=6)
SELECT `t1`.`id`,`t1`.`name` FROM `tag` `t1` WHERE (`t1`.`id`=1)
SELECT `t1`.`id`,`t1`.`name` FROM `tag` `t1` WHERE (`t1`.`id`=2)
SELECT `t1`.`tag_id` FROM `tag_post` `t1` WHERE (`t1`.`post_id`=7)
SELECT `t1`.`id`,`t1`.`name` FROM `tag` `t1` WHERE (`t1`.`id`=3)
SELECT `t1`.`id`,`t1`.`name` FROM `tag` `t1` WHERE (`t1`.`id`=1)
SELECT `t1`.`id`,`t1`.`name` FROM `tag` `t1` WHERE (`t1`.`id`=2)
SELECT `t1`.`tag_id` FROM `tag_post` `t1` WHERE (`t1`.`post_id`=8)
SELECT `t1`.`id`,`t1`.`name` FROM `tag` `t1` WHERE (`t1`.`id`=3)
SELECT `t1`.`tag_id` FROM `tag_post` `t1` WHERE (`t1`.`post_id`=9)
SELECT `t1`.`id`,`t1`.`name` FROM `tag` `t1` WHERE (`t1`.`id`=1)
SELECT `t1`.`tag_id` FROM `tag_post` `t1` WHERE (`t1`.`post_id`=10)
SELECT `t1`.`id`,`t1`.`name` FROM `tag` `t1` WHERE (`t1`.`id`=2)
SELECT `t1`.`id`,`t1`.`name` FROM `tag` `t1` WHERE (`t1`.`id`=3)
SELECT `t1`.`tag_id` FROM `tag_post` `t1` WHERE (`t1`.`post_id`=11)
SELECT `t1`.`id`,`t1`.`name` FROM `tag` `t1` WHERE (`t1`.`id`=1)
SELECT `t1`.`id`,`t1`.`name` FROM `tag` `t1` WHERE (`t1`.`id`=2)
SELECT `t1`.`tag_id` FROM `tag_post` `t1` WHERE (`t1`.`post_id`=12)
SELECT `t1`.`id`,`t1`.`name` FROM `tag` `t1` WHERE (`t1`.`id`=1)
SELECT `t1`.`id`,`t1`.`name` FROM `tag` `t1` WHERE (`t1`.`id`=2)
SELECT `t1`.`id`,`t1`.`name` FROM `tag` `t1` WHERE (`t1`.`id`=3)
SELECT `t1`.`tag_id` FROM `tag_post` `t1` WHERE (`t1`.`post_id`=13)
SELECT `t1`.`id`,`t1`.`name` FROM `tag` `t1` WHERE (`t1`.`id`=3)
SELECT `t1`.`id`,`t1`.`name` FROM `tag` `t1` WHERE (`t1`.`id`=2)
SELECT `t1`.`tag_id` FROM `tag_post` `t1` WHERE (`t1`.`post_id`=14)
SELECT `t1`.`id`,`t1`.`name` FROM `tag` `t1` WHERE (`t1`.`id`=1)
SELECT `t1`.`id`,`t1`.`name` FROM `tag` `t1` WHERE (`t1`.`id`=2)
SELECT `t1`.`tag_id` FROM `tag_post` `t1` WHERE (`t1`.`post_id`=15)
SELECT `t1`.`id`,`t1`.`name` FROM `tag` `t1` WHERE (`t1`.`id`=3)
SELECT `t1`.`tag_id` FROM `tag_post` `t1` WHERE (`t1`.`post_id`=16)
SELECT `t1`.`tag_id` FROM `tag_post` `t1` WHERE (`t1`.`post_id`=17)
SELECT `t1`.`id`,`t1`.`name` FROM `tag` `t1` WHERE (`t1`.`id`=1)
SELECT `t1`.`id`,`t1`.`name` FROM `tag` `t1` WHERE (`t1`.`id`=2)
SELECT `t1`.`tag_id` FROM `tag_post` `t1` WHERE (`t1`.`post_id`=18)
SELECT `t1`.`id`,`t1`.`name` FROM `tag` `t1` WHERE (`t1`.`id`=3)
SELECT `t1`.`tag_id` FROM `tag_post` `t1` WHERE (`t1`.`post_id`=19)
SELECT `t1`.`id`,`t1`.`name` FROM `tag` `t1` WHERE (`t1`.`id`=1)
SELECT `t1`.`id`,`t1`.`name` FROM `tag` `t1` WHERE (`t1`.`id`=3)
SELECT `t1`.`tag_id` FROM `tag_post` `t1` WHERE (`t1`.`post_id`=20)

我担心所有这些往返行程可能会产生太多开销。

有没有人成功地实现了一个很好的 Scala 查询多对多实现?

最佳答案

编辑
您可以像这样模拟 group_concat:

val groupConcat = SimpleFunction[String]("GROUP_CONCAT")

如果您在查询范围内创建此方法,它应该像这样简单:

yield (alias.a, alias.b, groupConcat(alias.c))

由于我将这些辅助函数存储在抽象数据库包装器中并在特定的 DBMS(如 MySQL)中实现,因此它变得有点复杂,因为 SimpleFunction 类型签名需要此抽象方法定义:

val groupConcat: ( Seq[Column[_]] => OperatorColumn[String] )

这意味着实现需要传入一个 Seq(alias.c),这有点违反直觉,我们只是传入一个列。无论如何,很高兴它能工作,GROUP_CONCAT 在 MySQL 中非常方便

原创
没有发布您的代码,上帝知道出了什么问题,但试试这个:

val q = (for {
tp <- TagPost
p <- Post if tp.post_id is p.id
t <- Tag if tp.tag_id is t.id
_ <- Query groupBy p.id
} yield (p.id, p.content, p.posted_date, group_concat(t.name)))
println(q.selectStatement)

您将需要创建一个函数来复制 MySQL 的 GROUP_CONCAT。参见 SimpleFunction source ;此对象的一元方法允许您将命名列传递给基础 DBMS 函数。

val group_concat =
SimpleFunction.unary[NamedColumn[String], String]("GROUP_CONCAT")

关于sql - ScalaQuery 多对多,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/10465263/

26 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com