使用 Doctrine (Symfony2) 查询表时缺少行

2022-01-16 00:00:00 php symfony doctrine oracle

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.

相关文章