sql - JPA PostreSQL 多对多关系选择并按关系数量排序

Question

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

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

标签： sqlspringpostgresqljpamany-to-many

Answer 1

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

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