JPA PostreSQL many to many relationship select and order by number of relationships

I have a many to many relationship between Article and Topic. I need to get all articles related to some topics and order them by the intersection between the given set of topics and the topics of a given article. The articles that have more topics in common with the given set of topics should be returned first.

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

So far I can retrieve the articles related to a given list of topics. But I don’t know how to achieve the ordering part. Where the articles with the most topics in common with the given set of topics go first.

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

Some pseudo SQL code of what I’m thinking would be:

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

In kotlin code it would be:

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

Answer

I finally got it. It wasn’t too difficult after all.

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

Just in case you want to test this:

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