Missing rows when querying table with Doctrine (Sy

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() ?

回答1:

This is what I would do:

Check the result using createQuery or createNativeQuery
Run the query from simple php script outside of symfony

If with all 3 methods you get the same issue then it might be problem with the size of data limitation. I would start with max_execution_time and memory_limit settings in php.ini

I found also some oracle limitations that might be set at: oci8.statement_cache_size in php.ini or in file oraaccess.xml in Oracle directory. It is also significant if you use APC for query caching.

Anyway what does app_dev.php say?

回答2:

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.