Geek asks and answers

ScalaQuery to many for many

Does anyone have an idea on how to archive many of the many in Scala Query?

I want to associate a blog post with a series of tags.
This is my database design: enter image description here

I managed to execute my own code, but when checking Scala Querys the generated SQL I am not happy with my solution.
I was playing with a functional approach, and it caused a lot of SQL queries, resulting in many rounds.
I can figure out how to reduce the number of queries by about half.

A manually created query that retrieves all my data, well formatted in one query,

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

Generated queries from Scala Query giving the same data.

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)

I am worried that this could be a big expense on all these trips.

-, Scala - ?

+3
2


group_concat :

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

, , :

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; .

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

, , .
, .

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- :) YES!

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`
+1

More articles:


All Articles