jpapostresql多对多关系按关系数选择和排序

z2acfund 于 2021-07-14 发布在 Java

关注(0)|答案(1)|浏览(320)

我在文章和主题之间有一种多对多的关系。我需要获得与某些主题相关的所有文章，并根据给定主题集和给定文章主题之间的交集对它们进行排序。应该首先返回与给定主题集有更多共同主题的文章。

@Entity
@Table(name = "articles")
data class Article(
    @Id val url: String,
) {
    @ManyToMany(cascade = [CascadeType.ALL])
    @JoinTable(
        name = "articles_topics",
        joinColumns = [JoinColumn(name = "article_id")],
        inverseJoinColumns = [JoinColumn(name = "topic_id")],
    )
    val topics: MutableSet<Topic> = HashSet()

    fun addTopic(topic: Topic) {
        topics.add(topic)
        topic.articles.add(this)
    }
}

@Entity
@Table(name = "topics")
data class Topic(
    @Id val label: String,
) {
    @ManyToMany(mappedBy = "topics")
    val articles: MutableSet<Article> = HashSet()
}

到目前为止，我可以检索到与给定主题列表相关的文章。但我不知道如何实现订购部分。其中主题与给定主题集最为相似的文章排在第一位。

@Repository
interface ArticleRepository : JpaRepository<Article, String> {

    @Query("SELECT DISTINCT a FROM Article a JOIN a.topics t WHERE t IN :topics")
    fun findAllByTopics(@Param("topics") topics: Set<Topic>): List<Article>
}

一些我认为应该是的伪sql代码：

ORDER BY array_length(array_intersection(a.topics, :topics))

在kotlin代码中是：

val sorted = articles.sortedBy { it.topics.intersect(topics).size }

sql spring postgresql jpa many-to-many

来源：https://stackoverflow.com/questions/66336497/jpa-postresql-many-to-many-relationship-select-and-order-by-number-of-relationsh

1条答案

按热度按时间

u1ehiz5o1#

我终于明白了。毕竟并不难。

@Query("SELECT DISTINCT(a), COUNT(t) FROM Article a INNER JOIN a.topics t " +
        "WHERE t IN :topics GROUP BY a.url ORDER BY COUNT(t) DESC")
    fun findAllByTopics(@Param("topics") topics: Set<Topic>): List<Article>

万一你想测试这个：

@Transactional
@SpringBootTest
@ContextConfiguration
@ExtendWith(SpringExtension::class)
class ArticleRepositoryTest {

    @Autowired
    lateinit var repository: ArticleRepository

    @Autowired
    lateinit var topicRepository: TopicRepository

    @Test
    fun `When finding articles by topics returns list of articles ordered by count of topics in given set`() {
        setUpArticlesWithTopics()
        val topics = topicRepository.findAllById(
            setOf("System Design", "Networking", "Programming", "Computer Science")
        ).toSet()

        val articles = repository.findAllByTopics(topics)

        articles.size shouldBeEqualTo 4
        articles[0].url shouldBeEqualTo "https://stanete.com/system-design-101"
        articles[1].url shouldBeEqualTo "https://stanete.com/system-design-103"
        articles[2].url shouldBeEqualTo "https://stanete.com/system-design-104"
        articles[3].url shouldBeEqualTo "https://stanete.com/system-design-102"
    }

    private fun setUpArticlesWithTopics(): List<Article> = repository.saveAll(listOf(
        Article(url = "https://stanete.com/system-design-101").apply {
            addTopic(Topic(label = "System Design"))
            addTopic(Topic(label = "Networking"))
            addTopic(Topic(label = "Programming"))
            addTopic(Topic(label = "Computer Science"))
        },
        Article(url = "https://stanete.com/system-design-102").apply {
            // Article with a lot of topics that other articles don't have.
            addTopic(Topic(label = "Programming"))
            addTopic(Topic(label = "Engineering"))
            addTopic(Topic(label = "Architecture"))
            addTopic(Topic(label = "Computer Systems"))
            addTopic(Topic(label = "Software"))
        },
        Article(url = "https://stanete.com/system-design-103").apply {
            addTopic(Topic(label = "System Design"))
            addTopic(Topic(label = "Networking"))
            addTopic(Topic(label = "Engineering"))
            addTopic(Topic(label = "Computer Science"))
        },
        Article(url = "https://stanete.com/system-design-104").apply {
            addTopic(Topic(label = "System Design"))
            addTopic(Topic(label = "Networking"))
            addTopic(Topic(label = "Engineering"))
        }
    ))
}

赞(0）回复(0）举报 2021-07-14

我来回答

jpapostresql多对多关系按关系数选择和排序

1条答案

相关问题

热门标签

最新问答