Search code examples
phpsymfonydoctrine-ormdoctrine-query

Doctrine query builder find by interests

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!

Solution

  • 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