Doctrine query builder find by interests

Question

today i have an interest task - realize search on DB by some groups and sort it by most relevant criteria

1. We have 2 entities (User and Interests) on Symfony with ManyToMany relation

2. We need create query for find users which have most similar interests ex:

   user1 have interests [1,2,3,4,5]

   user2 have interests [1,2,4,5,7]

   user3 have interests [3,5]

we try find user with interests [2,6,7] and result must be:

[user2, user1, user0]

user2 - 2 similar interests

user1 - 1 similar interests

user3 - 0 

Code example:

class User
{
    // ...

    /**
     * Many Users have Many Interests.
     * @ManyToMany(targetEntity="Interest")
     * @JoinTable(name="users_interests",
     *      joinColumns={@JoinColumn(name="user_id", referencedColumnName="id")},
     *      inverseJoinColumns={@JoinColumn(name="interest_id", referencedColumnName="id")}
     *      )
     */
    private $interests;
...

I have no idea how to organize it nicely, can somebody help me? Thanks!

Answer 1

One way of doing it

1. Find user with at least 1 interest of your list (Simple Dql Query)

2. Count number of common interest in php.
   For example using count(array_intersect($userInterestsIds, $wantedIds))

3. You have all the wanted info, display it as you want

Answer 2

I suggest you to use simple sql (not DQL) in order to extract only the id of the user that match the criteria. A simple query could be:

select distinct user_id, count(*) from users_interests
where interest_id in (1,3,5) --- your interest ids
group by 1
order by 2 DESC;

Hope this help