CODEWITHSUNDEEP
mysqlsymfonydoctrine-ormdoctrinedql
wawa
wawa

Reputation: 5084

Get similar entities in Symfony using Doctrine

I have a Entity in Symfony for Videos. Lets say the db looks like this:

id: int
name: varchar
uploader: int (User Entity)
video_mp4: varchar
param1: int
param2: int
param3: int

I'd like to load similar videos. Similar means:

Actually I'd like to order them by the most matchings (meaning a video uploaded by the same user and with same param1 + param3 should be more important then one where just param2 is matching) something like ORDER BY mostMatches DESC any ideas how to achive that For this I'd use this query (yes I know that "OR" isn't good for the DB, sugestions welcome ;))

I created in my Entity class a function that looks like this: ($q is a QueryBuilder)

public function getRelated($q, $limit = 6) {
    $uploader = $this->getUploader()->getId();
    $param1 = $this->getParam1();
    $param2 = $this->getParam2();
    $param3 = $this->getParam3();

    $q->select('e')
    ->from('MyBackendBundle:Video','e')
    ->where('e.id != ?0')
    ->andWhere('e.uploader = ?1 OR e.param1 = ?2 OR e.param2 = ?3 OR e.param3 = ?4');

    if ($limit > 0) {
        $q->setMaxResults($limit);
    }

    $q->setParameters(array(
        $this->id,
        $uploader,
        $param1,
        $param2,
        $param3
    ));

    return $q->getQuery()->getResult();
}

Two questions:

  1. How can I get the QueryBuilder in an Entity, I don't like to pass it from my Controller.
  2. Is there a better way to load similar entries?
  3. How can I order them by the most similarities (uploader, param1, param2, param3), sure I could do this with PHP but what if result 7 would be a 100% match but I load only 6?

Upvotes: 0

Views: 1073

Answers (1)

Michael Sivolobov
Michael Sivolobov

Reputation: 13340

You can just sum all these matchings:

SELECT
    *,
    (CAST(e.uploader = ?1 AS UNSIGNED)
    + CAST(e.param1 = ?2 AS UNSIGNED)
    + CAST(e.param2 = ?3 AS UNSIGNED)
    + CAST(e.param3 = ?4 AS UNSIGNED)
    ) AS matches_count
FROM video
ORDER BY matches_count DESC

Upvotes: 1

Related Questions