Geek asks and answers

Need help creating sql query with connection and where from many to many relationships

I have two objects that are interconnected by many relationships. These two objects are: Videoand Category. A video can have many categories and vice versa. What I want to do is that if I have a category to get all the videos that have this category. In addition, I have a Section object that has a OneToMany relationship with Video, i.e. the section contains a lot of videos. Now I am trying to do the following:

public function findAllBySectionAndCategory($section, $category) {
    $query = $this->getEntityManager()
        ->createQuery(
            'SELECT v FROM OSCMySportBundle:Video v WHERE v.section = :section AND :category IN v.categories'
        )
        ->setParameter('section', $section)
        ->setParameter('category', $category);
    return $query->getResult();
}

When I try to verify this request, I get the following error:

[Syntax Error] line 0, col 83: Error: Expected Doctrine\ORM\Query\Lexer::T_OPEN_PARENTHESIS, got 'v'

This probably means that it is looking for an array, but found v. I know this is probably a simple question, but I cannot understand.

Edit 1:

I managed to modify it as follows so that I can make a JOIN:

'SELECT v FROM OSCMySportBundle:Video v JOIN v.categories category WHERE category.id = :category '

, :

'SELECT v FROM (SELECT a FROM OSCMySportBundle:Video a WHERE a.section = :section) JOIN v.categories category WHERE category.id = :category '

:

[Semantical Error] line 0, col 14 near '(SELECT a FROM': Error: Class '(' is not defined.

, ?

0
1

Doctrine IN (1, 2, 3, 4, ...) IN. , , .

, , MEMBER OF Doctrine:

public function findAllBySectionAndCategory($section, $category) {
    $query = $this->getEntityManager()
        ->createQuery(
            'SELECT v FROM OSCMySportBundle:Video v WHERE v.section = :section AND :category MEMBER OF v.categories'
        )
        ->setParameter('section', $section)
        ->setParameter('category', $category);
    return $query->getResult();
}

Doctrine $category.

Doctrine docs:

$query = $em->createQuery('SELECT u.id FROM CmsUser u WHERE :groupId MEMBER OF u.groups');
$query->setParameter('groupId', $group);
$ids = $query->getResult();
+1

More articles:


All Articles