ScalaQuery多对多

Question

有没有人知道如何在Scala中进行多对多查询？

我想要将一篇博客文章连接到一系列标签。

这是我的数据库设计：

​

我已经成功地使用了我的自定义代码，但是在检查Scala Querys生成的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查询生成的查询给出了相同的数据。

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多对多查询？

Answer 1

编辑

您可以像这样模拟group_concat：

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

如果你在query作用域中创建这个方法，它应该像下面这样简单：

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

由于我将这些辅助函数存储在抽象数据库包装器中，并在特定的数据库管理系统(如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")

Answer 2

我终于完成了这个方法。

不幸的是，这是特定于供应商的。

def allPosts = database.withSession { implicit db: Session =>
    val group_concat_string_tmp = SimpleFunction[String]("GROUP_CONCAT")
    def group_concat_string(c: Column[String]) = group_concat_string_tmp(Seq(c))
    def group_concat_long(c: Column[Long]) = group_concat_string_tmp(Seq(c))
    val query = for{
       tp <- TagPostTable 
       tag <- TagTable if tp.tag_id is tag.id
       post <- PostTable if tp.post_id is post.id
       _ <- Query groupBy post.id 
     } yield post.id ~ post.content ~ post.postedDate ~ group_concat_long(tag.id) ~ group_concat_string(tag.name)
     println(query.selectStatement)
     def parseToTagList(ids: String, names: String) : List[Tag] = {
       (ids.split(',') map (_.toLong) , names.split(',')).zipped map (Tag(_,_)) toList
     }
     query.list map (queryResult => Post(queryResult._1, queryResult._2, queryResult._3, Option(parseToTagList(queryResult._4, queryResult._5) )))
  }

并且生成的SQL查询是单一的:)是的！

SELECT `t1`.`id`,`t1`.`content`,`t1`.`posted_date`,GROUP_CONCAT(`t2`.`id`),GROUP_CONCAT(`t2`.`name`) 
FROM `tag_post` `t3`,`post` `t1`,`tag` `t2` 
WHERE (`t3`.`tag_id`=`t2`.`id`) AND (`t3`.`post_id`=`t1`.`id`) 
GROUP BY `t1`.`id`