教条2复杂多对多的搜索查询。

时间:2023-01-09 06:48:43

I am creating an application that has an Entity that has multiple many-to-many relationships with other objects. Let me layout the Entities:

我正在创建一个应用程序,它有一个具有多个多对多关系的实体和其他对象。让我来布置实体:

  • Entry = the main object
  • 输入=主对象。
  • Region = has many-to-many with Entry via "_entry_region" reference table
  • 区域=具有通过“_entry_region”引用表条目的多对多。
  • Type = has many-to-many with Entry via "_entry_type" reference table
  • 类型=有多对多,通过“_entry_type”引用表进入。
  • Tag = has many-to-many with Entry via "_entry_tag" reference table
  • 通过“_entry_tag”引用表来输入多对多。

Now in the frontend the user can setup some filters for the entries that need to be loaded. In human language the query needs to be like this.

现在,在前端,用户可以为需要加载的条目设置一些过滤器。在人类语言中,查询需要是这样的。

Get the entries WHERE  region is (1 or 2 or 3) AND type is ( 3 or 4 or 5 ) AND tag is ( 4 OR 6 or 1)

i currently strugle with this piece of code:

我现在对这段代码很不满意:

$query = $this->em->createQuery('SELECT m.id FROM Entity\Entry e WHERE :region_id MEMBER OF e.regions);
$query->setParameter('region_id',  1);      
$ids = $query->getResult();

This gives me the ID's of the entry's of the corresponding region. but it is not possible to add an array of the region id's in the setParameter(). I also can't find in the docs how to do this on multiple related entities like my human based query:

这就得到了对应区域的输入的ID。但是不可能在setParameter()中添加区域id的数组。我也无法在文档中找到如何在多个相关实体上执行这些操作,比如基于人类的查询:

Get the entries WHERE  region is (1 or 2 or 3) AND type is ( 3 or 4 or 5 ) AND tag is ( 

1 个解决方案

#1


4  

You might also consider taking a look at the D2 query builder. Bit more verbose but it's much easier to build more complicated queries once you get the syntax down.

您还可以考虑查看D2查询生成器。稍微有点啰嗦,但是一旦你把语法搞清了,就更容易构建更复杂的查询了。

Something like:

喜欢的东西:

    $qb = $this->em->createQueryBuilder();

    $qb->addSelect('entry');
    $qb->addSelect('region');
    $qb->addSelect('type');
    $qb->addSelect('tag');

    $qb->from('MyBundle:Entry',   'entry');
    $qb->leftJoin('entry.regions','region');
    $qb->leftJoin('entry.types',  'type');
    $qb->leftJoin('entry.tags',   'tag');


    $qb->andWhere($qb->expr()->in('region.id',$regions));
    $qb->andWhere($qb->expr()->in('type.id',  $types));
    $qb->andWhere($qb->expr()->in('tag.id',   $tags));

#1


4  

You might also consider taking a look at the D2 query builder. Bit more verbose but it's much easier to build more complicated queries once you get the syntax down.

您还可以考虑查看D2查询生成器。稍微有点啰嗦,但是一旦你把语法搞清了,就更容易构建更复杂的查询了。

Something like:

喜欢的东西:

    $qb = $this->em->createQueryBuilder();

    $qb->addSelect('entry');
    $qb->addSelect('region');
    $qb->addSelect('type');
    $qb->addSelect('tag');

    $qb->from('MyBundle:Entry',   'entry');
    $qb->leftJoin('entry.regions','region');
    $qb->leftJoin('entry.types',  'type');
    $qb->leftJoin('entry.tags',   'tag');


    $qb->andWhere($qb->expr()->in('region.id',$regions));
    $qb->andWhere($qb->expr()->in('type.id',  $types));
    $qb->andWhere($qb->expr()->in('tag.id',   $tags));