ScalaQuery many to many

Question

Do anybody have an Idea of how to archive many to many in Scala Query?

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

I have succeeded with my custom code but when inspecting Scala Querys generated SQL I'm not happy with my solution.
I played with a functional approach and it generated many SQL queries resulting in a lot of round trips.
I can figure out how to reduce the number of queries by approximately half.

A hand crafted Query that fetches all my data nicely 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

The 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'm worried that it may be to much overhead with all this round trips.

Are there anyone who have succeed in making a nice Scala Query many to many implementation?

virtualeyes · Answer 1 · 2012-05-06T12:29:51.460

2

EDIT
You can simulate group_concat like so:

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

If you create this method in query scope, it should be as simple as:

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

Since I store these helper functions in an abstract database wrapper and implement in specific DBMS like MySQL, it gets a bit more complex, as the SimpleFunction type signature requires this abstract method definition:

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

Which means that the implementation requires one to pass in a Seq(alias.c), which is a bit counter intuitive, we're just passing in a single column. At any rate, happily it works, GROUP_CONCAT is quite handy in MySQL

ORIGINAL
God knows what's wrong without posting your code, but try this:

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)

You're going to need to create a function to replicate MySQL's GROUP_CONCAT. See SimpleFunction source; the unary method of this object allows you to pass in a named column to the underlying DBMS function.

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

edited May 06 '12 at 12:29

answered May 06 '12 at 09:03

virtualeyes

11,147
6
56
91

Thanks, that was almost exact my query at first. Yes you nailed the problem as you only yield a post but not the connected tags, all Scala Query queries was poulating this result with the tags. I don't have to use the group concat if there are a way to get it as a list in scala query directly. I'm replying on an Ipad the moment I will look into if I can extend SQ later today. – May 06 '12 at 09:22
Aha I see you have updated your answer now it looks very promising. I guess I have to parse the result manually to create my object. As I don't normaly yeild explicit columns but objects defined in the "object" extends Table with it's companion case class. – May 06 '12 at 09:27
@Farmor got group_concat working, see edit. The above should replicate desired by-hand-SQL. ScalaQuery continues to impress... – virtualeyes May 06 '12 at 12:31
Thanks for your help, we finally got it to work :) on a side note the by-hand-SQL is not desired at all because it is vendor specific the desired solution would be a vendor generic many to many. – May 06 '12 at 13:38
@Farmor well, group_concat only exists in mysql, not terribly generic if that is the end goal – virtualeyes May 06 '12 at 13:46
True but the end goal is a List of Tags there is no value in itself to use group_concat. – May 06 '12 at 13:51

score 1 · Answer 2 · 2012-05-06T13:41:51.910

I am finally finished with the method.
Unfortunately it's vendor specific.

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) )))
  }

And the generated SQL query is singular :) 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`

ScalaQuery many to many

2 Answers2