What's a good way to combine pagination and non query grouping in Slick 3.0?

Question

For simplification let's say I have three tables:

val postTable = TableQuery[Posts]
val postTagTable = TableQuery[PostTags]
val tagTable = TableQuery[Tags]

One post can have multiple tags and postTagTable just contains the relation.

Now I could query the posts and tags like this:

val query = for {
    post <- postTable
    postTag <- postTagTable if post.id === postTag.postId
    tag <- tagTable if postTag.tagId === tag.id
} yield (post, tag)

val postTags = db.run(query.result).map {
    case result: Seq[(Post,Tag)] => 
        result.groupBy(_._1).map {
            case (post, postTagSeq) => (post, postTagSeq.map(_._2))
        }
}

Which would give me a Future[Seq[(Post, Seq(Tag))]].

So far so good.

But what if I want to add pagination for the posts? Since one Post can have multiple Tags with the above query, I don't know how many rows to take from the query, in order to get, let's say, 10 Posts.

Does anyone know a good way of getting the same result with a specific number of Posts in a single query?

I am actually not even sure how I would approach this in native SQL without nested queries, so if someone has a suggestion in that direction I would also be glad to hear it.

Thanks!

EDIT

Just so you know, what kind of query I am currently doing:

val pageQuery = postTable drop(page * pageSize) take(pageSize)

val query = for {
    pagePost <- pageQuery
    post <- postTable if pagePost.id === post.id
    postTag <- postTagTable if post.id === postTag.postId
    tag <- tagTable if postTag.tagId === tag.id
} yield (post, tag)

val postTags = db.run(query.result).map {
    case result: Seq[(Post,Tag)] => 
        result.groupBy(_._1).map {
            case (post, postTagSeq) => (post, postTagSeq.map(_._2))
        }
}

But this obviously results in a nested query. And this is what I would like to avoid.

EDIT 2

Another 2-query solution that would be possible:

val pageQuery = postTable drop(page * pageSize) map(_.id) take(pageSize)

db.run(pageQuery.result) flatMap {
    case ids: Seq[Int] => 
        val query = for {
            post <- postTable if post.id inSetBind ids
            postTag <- postTagTable if post.id === postTag.postId
            tag <- tagTable if postTag.tagId === tag.id
        } yield (post, tag)

        val postTags = db.run(query.result).map {
                case result: Seq[(Post,Tag)] => 
                     result.groupBy(_._1).map {
                         case (post, postTagSeq) => (post, postTagSeq.map(_._2))
                     }
        }
 }

But this would take two trips to the database and uses the in operator, so it's probably not as good as the join query.

Any suggestions?

Doesn't Slick's `groupBy` help in this case? If you do `groupBy` by Post on the query and then `take`? — User, Aug 14 '15 at 22:32
If I do groupBy on the query I need to use map to aggregate everything I didn't group on. So if I would group on Posts (on the query), I couldn't get the Tags as List but only e.g. count them. — thwiegan, Aug 15 '15 at 02:37

Igor Mielientiev · Answer 1 · 2015-08-16T09:35:47.590

You can do like this:

  def findPagination(from: Int, to: Int): Future[Seq[(Post, Seq[Tag])]] = {
    val query:DBIO[Seq[(Album,Seq[Genre])]] = postRepository.findAll(from, to).flatMap{posts=>
      DBIO.sequence(
        posts.map{ post=>
          tagRepository.findByPostId(post.id).map(tags=>(post,tags))
        }
      )
    }
    db.run(query)
  }

Inside PostRepository

def findAll(from: Int, limit: Int): DBIO[Seq[Post]] = postTable.drop(from).take(limit).result

Inside TagRepository

  def findByPostId(id: Int): DBIO[Seq[Tag]] = {
    val query = for {
      tag <- tagTable
      pstTag <- postTagTable if pstTag.postId === id && tag.id === pstTag.tagId
    } yield tag
    query.result
  }

EDIT

I thing you can't do it without subselect in a single query. Your current solution is the best one. Also you can optimize your query by removing unnecessary "join"

val query = for {
    pagePost <- pageQuery
    postTag <- postTagTable if pagePost.id === postTag.postId
    tag <- tagTable if postTag.tagId === tag.id
} yield (pagePost, tag)

And you'll get approximately next SQL (Slick 3.0.1):

SELECT x2.`postname`,
       x2.`id`,
       x3.`tagname`,
       x3.`id`
FROM
  (SELECT x4.`postname` AS `postname`, x4.`id` AS `id`
   FROM `POST` x4 LIMIT 10, 1) x2,
     `POST_TAG` x5,
     `TAG` x3
WHERE (x2.`id` = x5.`postId`)
  AND (x5.`tagId` = x3.`id`)

Maybe in your case, also it's more efficient to pre-compile this query http://slick.typesafe.com/doc/3.0.0/queries.html#compiled-queries

But even though it only hits the database once, that would result in n+1 statements (where n is pageSize), wouldn't it? — thwiegan, Aug 15 '15 at 09:27
Yes, you get n+1 hits to database. 1 hit for getting "paginated" posts, and n for getting tags for each post — Igor Mielientiev, Aug 15 '15 at 19:54
Okay so you are avoiding the nested query, but doing a couple of roundtrips to the database is even worse imo — thwiegan, Aug 15 '15 at 20:02

score 0 · Answer 2 · answered Mar 15 '16 at 08:21

I have same issue. May be interesting yet:

        val query = for {
      ((p, pt), t) <- posts.filter({x => x.userid === userId}).sortBy({x=>x.createdate.desc}).take(count).
                      joinLeft (postsTags).on((x, y)=>x.postid === y.postid).
                      joinLeft (tags).on(_._2.map(_.tagid) === _.tagid)
    } yield (p.?, t.map(_.?))
    //val query = posts filter({x => x.userid === userId}) sortBy({x=>x.createdate.desc}) take(count)
    try db.run(query result)
    catch{
      case ex:Exception => {
        log error("ex:", ex)
        Future[Seq[(Option[PostsRow], Option[Option[TagsRow]])]] {
          Seq((None, None))
        }
      }
    }

then result of this query:

result onComplete {
case Success(x) => {
  x groupBy { x => x._1 } map {x=>(x._1, x._2.map(_._2))}

}
case Failure(err) => {
  log error s"$err"
}

}

it returns Sequence like this: Seq[(Post, Seq[Tag]), (Post, Seq[Tag])........]

What's a good way to combine pagination and non query grouping in Slick 3.0?

2 Answers2