Slick 3:如何删除和接受具有某些关系的集合 [英] Slick 3: how to drop and take on collections with some relations
问题描述
我正在与 Play 合作!Scala 2.4 和 Slick 3.我有以下多对多关系:
I'm working with Play! Scala 2.4 and Slick 3. I have a many to many relations as following:
class Artists(tag: Tag) extends Table[Artist](tag, "artists") {
def id = column[Long]("artistid", O.PrimaryKey, O.AutoInc)
def name = column[String]("name")
def * = (id.?, name) <> ((Artist.apply _).tupled, Artist.unapply)
}
关系表:
class ArtistsGenres(tag: Tag) extends Table[ArtistGenreRelation](tag, "artistsgenres") {
def artistId = column[Long]("artistid")
def genreId = column[Int]("genreid")
def * = (artistId, genreId) <> ((ArtistGenreRelation.apply _).tupled, ArtistGenreRelation.unapply)
def aFK = foreignKey("artistid", artistId, artists)(_.id, onDelete = ForeignKeyAction.Cascade)
def bFK = foreignKey("genreid", genreId, genres)(_.id, onDelete = ForeignKeyAction.Cascade)
}
和第三个表:
class Genres(tag: Tag) extends Table[Genre](tag, "genres") {
def id = column[Int]("genreid", O.PrimaryKey, O.AutoInc)
def name = column[String]("name")
def * = (id.?, name) <> ((Genre.apply _).tupled, Genre.unapply)
}
直到现在,我只想按流派名称获取所有艺术家如下(以及他们的流派):
Until now I just wanted to get all the artists by their genre names as following (and their genres as well):
def findAllByGenre(genreName: String, offset: Int, numberToReturn: Int): Future[Seq[ArtistWithGenre]] = {
val query = for {
genre <- genres if genre.name === genreName
artistGenre <- artistsGenres if artistGenre.genreId === genre.id
artist <- artists joinLeft
(artistsGenres join genres on (_.genreId === _.id)) on (_.id === _._1.artistId)
if artist._1.id === artistGenre.artistId
} yield artist
db.run(query.result) map { seqArtistAndOptionalGenre =>
ArtistsAndOptionalGenresToArtistsWithGenres(seqArtistAndOptionalGenre)
}
}
ArtistsAndOptionalGenresToArtistsWithGenres 方法对艺术家的响应进行分组.这就像一个魅力.现在我想限制我从数据库中获取的艺术家数量.
The method ArtistsAndOptionalGenresToArtistsWithGenres groups the response by artists. This worked like a charm. Now I want to limit the number of artists I get from the database.
但我没有设法正确使用光滑的函数 take
和 drop
:实际上,当我的查询返回艺术家和关系列表时,如果我添加 take
在 .result
之前我没有收到我想要的艺术家数量(取决于艺术家拥有的关系数量).
But I don't manage to use correctly the slick functions take
and drop
: indeed as my query returns a list of artists and relations, If I add a take
before the .result
I don't receive the number of artists I want to get (depending of the number of relations the artists have).
我可以在我按艺术家对我的结果进行分组之后放下并接受,但我在这里看到一个问题:SGBDR 不会优化请求,即我将获得所有艺术家(可能很多),继续groupBy 和 after 取一点,而不是限制 groupBy 之前返回的艺术家数量.
I could drop and take after that I have grouped my result by artist, but I see a problem here: the SGBDR won't optimize the request, i.e. I will get all the artists (it can be a lot), proceed the groupBy and after take a bit instead of limit the number of artist returned before the groupBy.
推荐答案
我找到了以下解决方案(有 2 个查询但有 1 个 DB 调用):
I found the following solution (with 2 queries but 1 DB call):
def findAllByGenre(genreName: String, offset: Int, numberToReturn: Int): Future[Seq[ArtistWithWeightedGenres]] = {
val query = for {
genre <- genres.filter(_.name === genreName)
artistGenre <- artistsGenres.filter(_.genreId === genre.id)
artist <- artists.filter(_.id === artistGenre.artistId)
} yield artist
val artistsIdFromDB = query.drop(offset).take(numberToReturn) map (_.id)
val query2 = for {
artistWithGenres <- artists.filter(_.id in artistsIdFromDB) joinLeft
(artistsGenres join genres on (_.genreId === _.id)) on (_.id === _._1.artistId)
} yield artistWithGenres
db.run(query2.result) map { seqArtistAndOptionalGenre =>
ArtistsAndOptionalGenresToArtistsWithGenres(seqArtistAndOptionalGenre)
} map(_.toVector)
}
如果有人有更好的解决方案...
If anyone has a better solution...
这篇关于Slick 3:如何删除和接受具有某些关系的集合的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!