用Doctrine查询表时缺少行（Symfony2） [英] Missing rows when querying table with Doctrine (Symfony2)

问题描述

我需要查询一个简单的表，只有一个内部连接，我已经做了很多次了。但是在这种情况下，有些奇怪的是：有很多行丢失。

我有一个名为Policy的实体。它链接到我的Oracle数据库上的表。此表中有81k +行。当使用Doctrine查询构建器查询此实体时，我只得到5k的结果。我将这个查询尽可能简单地进行测试：

  $ qb = $ em-> createQueryBuilder（）; 
 $ qb-> select（'p'） - > from（'ErwMonitoringExtranetBundle：Policy'，'p'）; 
 $ query = $ qb-> getQuery（）; 
 $ policiesFull = $ query-> getResult（）; 
  

$ policiesFull变量只包含5k个元素。表中没有重复项。

由Doctrine生成的SQL查询如下所示：

  SELECT 
 r0_.node_group_name AS NODE_GROUP_NAME0，
 r0_.policy_name AS POLICY_NAME1，
 r0_.policy_description AS POLICY_DESCRIPTION2，
 r0_.policy_group_name AS POLICY_GROUP_NAME3，
 r0_.policy_type_name AS POLICY_TYPE_NAME4，
 r0_.policy_name_on_agent AS POLICY_NAME_ON_AGENT5，
 r0_.date_last_maj AS DATE_LAST_MAJ6，
 r0_.om_name AS OM_NAME7，
 r0_.id_node AS ID_NODE8 
 FROM 
 ewintranet.ref_monitored_ci; 
  

在Oracle上运行相同的确切查询返回完整的表内容。

通过教义查询计算结果返回正确的行数：

  $ qb = $ em-> createQueryBuilder（）; 
 $ qb-> select（'count（p）'） - > from（'ErwMonitoringExtranetBundle：Policy'，'p'）; 
 $ query = $ qb-> getQuery（）; 
 echo $ query-> getSingleScalarResult（）; 
  

这将返回81k。

知道为什么所有这些行在使用getResult（）？之后消失？

解决方案

好的，我发现是什么导致我的问题。我的实体声明中的主ID是错误的。

oracle表中有一个组合的主键，而在我的实体中，该ID仅在一列上。 GetResult在此列上进行了DISTINCT。

I'm encountering a strange issue with Doctrine.

I need to query a simple table with only 1 inner join, which I something I have already done many times. But in this case something's odd: there are a lot of rows missing.

I have an entity called Policy. It is linked to a table on my Oracle database. There are 81k+ rows in this table. When querying this entity with the Doctrine query builder, I only get 5k results. I made this query as simple as possible for testing :

$qb = $em->createQueryBuilder();
$qb->select('p')->from('ErwMonitoringExtranetBundle:Policy', 'p');
$query = $qb->getQuery();
$policiesFull = $query->getResult();

The $policiesFull variable only contains 5k elements. There are no duplicates in the table.

The SQL query that is generated by Doctrine looks like this :

SELECT
  r0_.node_group_name      AS NODE_GROUP_NAME0,
  r0_.policy_name          AS POLICY_NAME1,
  r0_.policy_description   AS POLICY_DESCRIPTION2,
  r0_.policy_group_name    AS POLICY_GROUP_NAME3,
  r0_.policy_type_name     AS POLICY_TYPE_NAME4,
  r0_.policy_name_on_agent AS POLICY_NAME_ON_AGENT5,
  r0_.date_last_maj        AS DATE_LAST_MAJ6,
  r0_.om_name              AS OM_NAME7,
  r0_.id_node              AS ID_NODE8
FROM
  ewintranet.ref_monitored_ci;

Running the same exact query on Oracle returns the full table content.

Counting results through a doctrine query returns the correct number of rows :

$qb = $em->createQueryBuilder();
$qb->select('count(p)')->from('ErwMonitoringExtranetBundle:Policy', 'p');
$query = $qb->getQuery();
echo $query->getSingleScalarResult();

This returns 81k.

Does anybody know why all these rows disappear after using getResult() ?

解决方案

Okay, I found out what was causing my issue. The primary ID was wrong in my Entity declaration.

The oracle table had a composed primary key while in my entity the ID was only on one column. GetResult was making a DISTINCT on this column.

这篇关于用Doctrine查询表时缺少行（Symfony2）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！