findAll doesn't return all the records #4980

New Issue

admin · 2026-01-22T14:55:05+01:00

admin commented

2026-01-22 14:55:05 +01:00

Originally created by @assoum891 on GitHub (Jan 18, 2016).

Originally assigned to: @Ocramius on GitHub.

when i do findAll on my entity repository i just get the first record of the database although i have two records in the database, but when i override the findAll method into my repo and i create the query with all the 'leftjoins' of my relations i get all the records...

Originally created by @assoum891 on GitHub (Jan 18, 2016). Originally assigned to: @Ocramius on GitHub. when i do findAll on my entity repository i just get the first record of the database although i have two records in the database, but when i override the findAll method into my repo and i create the query with all the 'leftjoins' of my relations i get all the records...

admin added the Invalid label 2026-01-22 14:55:05 +01:00

admin closed this issue

2026-01-22 14:55:05 +01:00

admin commented

2026-01-22 14:55:05 +01:00

@Jean85 commented on GitHub (Jan 18, 2016):

Can you give us a reproducible case, or at least some of your code?

@Jean85 commented on GitHub (Jan 18, 2016): Can you give us a reproducible case, or at least some of your code?

admin commented

2026-01-22 14:55:06 +01:00

@assoum891 commented on GitHub (Jan 19, 2016):

Actually i have an entity called Offer that has oneToMany relations with an entity called Account, when i call findAll() on my repository class it just returns the first record of the database but when i override the method and do this

public function findAll()
    {
       return $this->createQueryBuilder('o')
            ->select('o, sa, sda, a')
            ->leftJoin('o.sponsorAccount', 'sa')
            ->leftJoin('o.sponsoredAccount', 'sda')
            ->leftJoin('o.account', 'a')
            ->getQuery()
            ->getResult();
    }

i get all the records.

@assoum891 commented on GitHub (Jan 19, 2016): Actually i have an entity called Offer that has oneToMany relations with an entity called Account, when i call findAll() on my repository class it just returns the first record of the database but when i override the method and do this <pre> public function findAll() { return $this->createQueryBuilder('o') ->select('o, sa, sda, a') ->leftJoin('o.sponsorAccount', 'sa') ->leftJoin('o.sponsoredAccount', 'sda') ->leftJoin('o.account', 'a') ->getQuery() ->getResult(); } </pre> i get all the records.

admin commented

2026-01-22 14:55:07 +01:00

@Jean85 commented on GitHub (Jan 19, 2016):

What if you remove the others joins? (sponsorAccount, sponsoredAccount)
Are you sure about your relations? The Doctrine schema is valid?

@Jean85 commented on GitHub (Jan 19, 2016): What if you remove the others joins? (sponsorAccount, sponsoredAccount) Are you sure about your relations? The Doctrine schema is valid?

admin commented

2026-01-22 14:55:07 +01:00

@assoum891 commented on GitHub (Jan 19, 2016):

I don't think there is something wrong with my schema,

     /**
     * @var Account
     * @ORM\ManyToOne(targetEntity="AccountNamespace...")
     * @ORM\JoinColumn(name="account_id", referencedColumnName="id", nullable=false)
     */
    protected $account;
    /**
     * @var Account
     * @ORM\ManyToOne(targetEntity="AccountNamespace...")
     * @ORM\JoinColumn(name="sponsored_account_id", referencedColumnName="id", nullable=true)
     */
    protected $sponsoredAccount;
    /**
     * @var Account
     * @ORM\ManyToOne(targetEntity="AccountNamespace...")
     * @ORM\JoinColumn(name="sponsor_account_id", referencedColumnName="id", nullable=true)
     */
    protected $sponsorAccount;

I've just tried removing the joins of sponsorAccount and sponsoredAccount, still one result in the list view...

@assoum891 commented on GitHub (Jan 19, 2016): I don't think there is something wrong with my schema, <pre> /** * @var Account * @ORM\ManyToOne(targetEntity="AccountNamespace...") * @ORM\JoinColumn(name="account_id", referencedColumnName="id", nullable=false) */ protected $account; /** * @var Account * @ORM\ManyToOne(targetEntity="AccountNamespace...") * @ORM\JoinColumn(name="sponsored_account_id", referencedColumnName="id", nullable=true) */ protected $sponsoredAccount; /** * @var Account * @ORM\ManyToOne(targetEntity="AccountNamespace...") * @ORM\JoinColumn(name="sponsor_account_id", referencedColumnName="id", nullable=true) */ protected $sponsorAccount; </pre> I've just tried removing the joins of sponsorAccount and sponsoredAccount, still one result in the list view...

admin commented

2026-01-22 14:55:07 +01:00

@Jean85 commented on GitHub (Jan 19, 2016):

Ok, you have a ManyToOne, not a OneToMany as you stated in your previous comment. That's what was throwing me off.

Can you try this implementation? What happens with this?

public function findAll()
{
   return $this->createQueryBuilder('o')
        ->join('o.account', 'a')
        ->getQuery()
        ->getResult();
}

@Jean85 commented on GitHub (Jan 19, 2016): Ok, you have a ManyToOne, not a OneToMany as you stated in your previous comment. That's what was throwing me off. Can you try this implementation? What happens with this? ``` public function findAll() { return $this->createQueryBuilder('o') ->join('o.account', 'a') ->getQuery() ->getResult(); } ```

admin commented

2026-01-22 14:55:07 +01:00

@assoum891 commented on GitHub (Jan 19, 2016):

oh sorry i meant ManyToOne, and yes i tried that, i still don't get all the records in my list

@assoum891 commented on GitHub (Jan 19, 2016): oh sorry i meant ManyToOne, and yes i tried that, i still don't get all the records in my list

admin commented

2026-01-22 14:55:07 +01:00

@Jean85 commented on GitHub (Jan 19, 2016):

So that's your problem: you have a discrepancy between your schema and your database. The findAll() assumes that your join is not nullable (as from your definition) and does a JOIN instead of a LEFT JOIN as you're doing.

If you check your DB, you probably have 1 Offer with a NOT NULL on account_id, and the others with null.

You can close this issue.

@Jean85 commented on GitHub (Jan 19, 2016): So that's your problem: you have a discrepancy between your schema and your database. The `findAll()` assumes that your join is not nullable (as from your definition) and does a JOIN instead of a LEFT JOIN as you're doing. If you check your DB, you probably have 1 Offer with a NOT NULL on account_id, and the others with null. You can close this issue.

admin commented

2026-01-22 14:55:08 +01:00

@assoum891 commented on GitHub (Jan 19, 2016):

I dont understand.. actually the account is not supposed to be null, it's a mandatory information and all the records have not null account_id values, it's the sponsored_account_id and sponsor_account_id columns that can be null..and even the first record returned when i do just findAll() have a null sponsor_account_id

@assoum891 commented on GitHub (Jan 19, 2016): I dont understand.. actually the account is not supposed to be null, it's a mandatory information and all the records have not null account_id values, it's the sponsored_account_id and sponsor_account_id columns that can be null..and even the first record returned when i do just findAll() have a null sponsor_account_id

admin commented

2026-01-22 14:55:09 +01:00

@Jean85 commented on GitHub (Jan 19, 2016):

I reduced your problem to this difference:
With this, you do a LEFT JOIN (like you did before), and you should obtain all the offers:

public function findAll()
{
   return $this->createQueryBuilder('o')
        ->leftJoin('o.account', 'a')
        ->getQuery()
        ->getResult();
}

With this (my suggestion), you do a INNER JOIN, like the native findAll() is doing, and you are retrieving just one row.

public function findAll()
{
   return $this->createQueryBuilder('o')
        ->join('o.account', 'a')
        ->getQuery()
        ->getResult();
}

The join is the only change, so the problem is in the relation's definition. If you're sure that all your rows in you offer table have a not null value on that field, you could check your inverse relation's definition.

@Jean85 commented on GitHub (Jan 19, 2016): I reduced your problem to this difference: With this, you do a LEFT JOIN (like you did before), and you should obtain all the offers: ``` public function findAll() { return $this->createQueryBuilder('o') ->leftJoin('o.account', 'a') ->getQuery() ->getResult(); } ``` With this (my suggestion), you do a INNER JOIN, like the native `findAll()` is doing, and you are retrieving just one row. ``` public function findAll() { return $this->createQueryBuilder('o') ->join('o.account', 'a') ->getQuery() ->getResult(); } ``` The join is the only change, so the problem is in the relation's definition. If you're sure that all your rows in you offer table have a not null value on that field, you could check your inverse relation's definition.

admin commented

2026-01-22 14:55:09 +01:00

@Ocramius commented on GitHub (Jan 20, 2016):

I initially thought this was about the ORM's provided Doctrine\ORM\EntityRepository#findAll() API, but that's not the case.

Therefore closing as Invalid: this is not a bug.

@Ocramius commented on GitHub (Jan 20, 2016): I initially thought this was about the ORM's provided `Doctrine\ORM\EntityRepository#findAll()` API, but that's not the case. Therefore closing as `Invalid`: this is not a bug.

admin commented

2026-01-22 14:55:09 +01:00

@GreeKatrina commented on GitHub (Mar 20, 2017):

I ran into this issue as well.

In my case, the User's company_id was either 0 or an id of a Company that didn't exist in the db (I don't remember which). I don't actually "require" the company_id, it can be 0 if the User doesn't belong to a company.

Is there a way to say that the company_id cannot be null, but is not required (as in, do a left instead of an inner join)?
I'm sure many people will be using Doctrine for existing DBs that don't have clean data (ex: a User's company_id doesn't exist in the company table). Should they expect an empty result for queries that fit this criteria?

@GreeKatrina commented on GitHub (Mar 20, 2017): I ran into this issue [as well](http://stackoverflow.com/questions/30941836/doctrine-not-finding-data-on-google-app-engine?noredirect=1&lq=1). In my case, the User's company_id was either 0 or an id of a Company that didn't exist in the db (I don't remember which). I don't actually "require" the company_id, it can be 0 if the User doesn't belong to a company. 1. Is there a way to say that the company_id cannot be null, but is not required (as in, do a left instead of an inner join)? 2. I'm sure many people will be using Doctrine for existing DBs that don't have clean data (ex: a User's company_id doesn't exist in the company table). Should they expect an empty result for queries that fit this criteria?

admin commented

2026-01-22 14:55:09 +01:00

@Ocramius commented on GitHub (Mar 20, 2017):

I'm sure many people will be using Doctrine for existing DBs that don't have clean data (ex: a User's company_id doesn't exist in the company table). Should they expect an empty result for queries that fit this criteria?

Use an SQL filter. Also, check https://stackoverflow.com/questions/15502408/doctrine-2-use-default-0-values-instead-of-null-for-relation

@Ocramius commented on GitHub (Mar 20, 2017): > I'm sure many people will be using Doctrine for existing DBs that don't have clean data (ex: a User's company_id doesn't exist in the company table). Should they expect an empty result for queries that fit this criteria? Use an SQL filter. Also, check https://stackoverflow.com/questions/15502408/doctrine-2-use-default-0-values-instead-of-null-for-relation

admin commented

2026-01-22 14:55:09 +01:00

@GreeKatrina commented on GitHub (Mar 20, 2017):

In the BasicEntityPersister class and the getJoinSQLForJoinColumns() method, would it be possible to add a "required" check or something like this?

User
/**
 * @ManyToOne(targetEntity="Company", inversedBy="users", fetch="EAGER")
 * @JoinColumn(name="company_id", referencedColumnName="company_id", nullable=false, required=false)
 */
private $company;

BasicEntityPersister
/**
 * Generates the appropriate join SQL for the given join column.
 *
 * @param array $joinColumns The join columns definition of an association.
 *
 * @return string LEFT JOIN if one of the columns is nullable, INNER JOIN otherwise.
 */
protected function getJoinSQLForJoinColumns($joinColumns)
{
    // if one of the join columns is nullable, return left join
    foreach ($joinColumns as $joinColumn) {
         if ( ! isset($joinColumn['nullable']) || $joinColumn['nullable'] 
         || ( ! isset($joinColumn['required']) || ! $joinColumn['required'] )) {
             return 'LEFT JOIN';
         }
    }
    return 'INNER JOIN';
}

From the docs it defines nullable under the @Column annotation as:

nullable: Determines if NULL values allowed for this column.

But under @JoinColumn it takes on a second "required" meaning:

nullable: Determine whether the related entity is required, or if null is an allowed state for the relation. Defaults to true.

I think it makes more sense to keep nullable defined the same for @Column and @JoinColumn, and then add a second option (required) to @JoinColumn.

nullable: Determines if NULL values allowed for this column. Defaults to true.
required: Determines whether the related entity is required, or if null is an allowed state for the relation. Defaults to false.

It seems like this may already be possible after reading the docs on columnDefinition, but I'm not sure how to use it and how the overriding works:

Using this attribute on @JoinColumn is necessary if you need slightly different column definitions for joining columns, for example regarding NULL/NOT NULL defaults. However by default a “columnDefinition” attribute on @Column also sets the related @JoinColumn’s columnDefinition.

@GreeKatrina commented on GitHub (Mar 20, 2017): In the [BasicEntityPersister](https://github.com/doctrine/doctrine2/blob/master/lib/Doctrine/ORM/Persisters/Entity/BasicEntityPersister.php) class and the `getJoinSQLForJoinColumns()` method, would it be possible to add a "required" check or something like this? ```php User /** * @ManyToOne(targetEntity="Company", inversedBy="users", fetch="EAGER") * @JoinColumn(name="company_id", referencedColumnName="company_id", nullable=false, required=false) */ private $company; BasicEntityPersister /** * Generates the appropriate join SQL for the given join column. * * @param array $joinColumns The join columns definition of an association. * * @return string LEFT JOIN if one of the columns is nullable, INNER JOIN otherwise. */ protected function getJoinSQLForJoinColumns($joinColumns) { // if one of the join columns is nullable, return left join foreach ($joinColumns as $joinColumn) { if ( ! isset($joinColumn['nullable']) || $joinColumn['nullable'] || ( ! isset($joinColumn['required']) || ! $joinColumn['required'] )) { return 'LEFT JOIN'; } } return 'INNER JOIN'; } ``` From the docs it defines `nullable` under the [@Column](http://doctrine-orm.readthedocs.io/en/latest/reference/annotations-reference.html#annref-column) annotation as: > **nullable:** Determines if NULL values allowed for this column. But under [@JoinColumn](http://doctrine-orm.readthedocs.io/en/latest/reference/annotations-reference.html#joincolumn) it takes on a second "required" meaning: > **nullable:** Determine whether the related entity is required, or if null is an allowed state for the relation. Defaults to true. I think it makes more sense to keep `nullable` defined the same for `@Column` and `@JoinColumn`, and then add a second option (`required`) to `@JoinColumn`. > **nullable:** Determines if NULL values allowed for this column. Defaults to true. > **required:** Determines whether the related entity is required, or if null is an allowed state for the relation. Defaults to false. ------------------- It seems like this may already be possible after reading the docs on `columnDefinition`, but I'm not sure how to use it and how the overriding works: > Using this attribute on `@JoinColumn` is necessary if you need slightly different column definitions for joining columns, for example regarding NULL/NOT NULL defaults. However by default a “columnDefinition” attribute on `@Column` also sets the related `@JoinColumn`’s columnDefinition.

admin commented

2026-01-22 14:55:09 +01:00

@lcobucci commented on GitHub (Mar 20, 2017):

@GreeKatrina although an ORM can be used on applications that don't have clear data it shouldn't be designed with that intent.

A nullable association is an optional one and that's how doctrine manages that. required adds complexity for no good reason (since well designed schemas will always have consistent data).

What I'd do in your case is create a new nullable column (with a proper FK), migrate the data that can be migrated without causing an constraint error and then start using it consistently. Just drop the old column when you're sure it's no longer relevant.

@lcobucci commented on GitHub (Mar 20, 2017): @GreeKatrina although an ORM can be used on applications that don't have clear data it shouldn't be designed with that intent. A `nullable` association is an optional one and that's how doctrine manages that. `required` adds complexity for no good reason (since well designed schemas will always have consistent data). What I'd do in your case is create a new nullable column (with a proper FK), migrate the data that can be migrated without causing an constraint error and then start using it consistently. Just drop the old column when you're sure it's no longer relevant.

admin commented

2026-01-22 14:55:10 +01:00

@Ocramius commented on GitHub (Mar 21, 2017):

You can poke at it as much as you like, but such an atrocity is not landing
in the ORM anytime.
Use NULL where it is supposed to be used: it is a fair assumption and it
will not lead yo further complications.
You can make the ORM compatible with your older code via writable views and
triggers instead (proper BC and FC approach).

On 20 Mar 2017 19:58, "Katrina Theodosopoulos" notifications@github.com
wrote:

In the BasicEntityPersister
https://github.com/doctrine/doctrine2/blob/master/lib/Doctrine/ORM/Persisters/Entity/BasicEntityPersister.php
class and the getJoinSQLForJoinColumns() method, would it be possible to
add a "required" check or something like this?

User/** * @ManyToOne(targetEntity="Company", inversedBy="users", fetch="EAGER") * @JoinColumn(name="company_id", referencedColumnName="company_id", nullable=false, required=false) /private $company;BasicEntityPersister/* * Generates the appropriate join SQL for the given join column. * * @param array $joinColumns The join columns definition of an association. * * @return string LEFT JOIN if one of the columns is nullable, INNER JOIN otherwise. */protected function getJoinSQLForJoinColumns($joinColumns){ // if one of the join columns is nullable, return left join foreach ($joinColumns as $joinColumn) { if ( ! isset($joinColumn['nullable']) || $joinColumn['nullable'] || ( ! isset($joinColumn['required']) || ! $joinColumn['required'] )) { return 'LEFT JOIN'; } } return 'INNER JOIN';}

From the docs it defines nullable under the @Column
http://doctrine-orm.readthedocs.io/en/latest/reference/annotations-reference.html#annref-column
annotation as:

nullable: Determines if NULL values allowed for this column.

But under @JoinColumn
http://doctrine-orm.readthedocs.io/en/latest/reference/annotations-reference.html#joincolumn
it takes on a second "required" meaning:

nullable: Determine whether the related entity is required, or if null
is an allowed state for the relation. Defaults to true.

I think it makes more sense to keep nullable defined the same for @Column
and @JoinColumn, and then add a second option (required) to @JoinColumn.

nullable: Determines if NULL values allowed for this column. Defaults
to true.
required: Determines whether the related entity is required, or if null
is an allowed state for the relation. Defaults to false.

It seems like this may already be possible after reading the docs on
columnDefinition, but I'm not sure how to use it and how the overriding
works:

Using this attribute on @JoinColumn is necessary if you need slightly
different column definitions for joining columns, for example regarding
NULL/NOT NULL defaults. However by default a “columnDefinition” attribute
on @Column also sets the related @JoinColumn’s columnDefinition.

—
You are receiving this because you modified the open/close state.
Reply to this email directly, view it on GitHub
https://github.com/doctrine/doctrine2/issues/5617#issuecomment-287862954,
or mute the thread
https://github.com/notifications/unsubscribe-auth/AAJakGHuLlEhRO1ub0aO3p7ifEu2m3Ddks5rnsw9gaJpZM4HG8rd
.

@Ocramius commented on GitHub (Mar 21, 2017): You can poke at it as much as you like, but such an atrocity is not landing in the ORM anytime. Use NULL where it is supposed to be used: it is a fair assumption and it will not lead yo further complications. You can make the ORM compatible with your older code via writable views and triggers instead (proper BC and FC approach). On 20 Mar 2017 19:58, "Katrina Theodosopoulos" <notifications@github.com> wrote: > In the BasicEntityPersister > <https://github.com/doctrine/doctrine2/blob/master/lib/Doctrine/ORM/Persisters/Entity/BasicEntityPersister.php> > class and the getJoinSQLForJoinColumns() method, would it be possible to > add a "required" check or something like this? > > User/** * @ManyToOne(targetEntity="Company", inversedBy="users", fetch="EAGER") * @JoinColumn(name="company_id", referencedColumnName="company_id", nullable=false, required=false) */private $company;BasicEntityPersister/** * Generates the appropriate join SQL for the given join column. * * @param array $joinColumns The join columns definition of an association. * * @return string LEFT JOIN if one of the columns is nullable, INNER JOIN otherwise. */protected function getJoinSQLForJoinColumns($joinColumns){ // if one of the join columns is nullable, return left join foreach ($joinColumns as $joinColumn) { if ( ! isset($joinColumn['nullable']) || $joinColumn['nullable'] || ( ! isset($joinColumn['required']) || ! $joinColumn['required'] )) { return 'LEFT JOIN'; } } return 'INNER JOIN';} > > From the docs it defines nullable under the @Column > <http://doctrine-orm.readthedocs.io/en/latest/reference/annotations-reference.html#annref-column> > annotation as: > > *nullable:* Determines if NULL values allowed for this column. > > But under @JoinColumn > <http://doctrine-orm.readthedocs.io/en/latest/reference/annotations-reference.html#joincolumn> > it takes on a second "required" meaning: > > *nullable:* Determine whether the related entity is required, or if null > is an allowed state for the relation. Defaults to true. > > I think it makes more sense to keep nullable defined the same for @Column > and @JoinColumn, and then add a second option (required) to @JoinColumn. > > *nullable:* Determines if NULL values allowed for this column. Defaults > to true. > *required:* Determines whether the related entity is required, or if null > is an allowed state for the relation. Defaults to false. > > ------------------------------ > > It seems like this may already be possible after reading the docs on > columnDefinition, but I'm not sure how to use it and how the overriding > works: > > Using this attribute on @JoinColumn is necessary if you need slightly > different column definitions for joining columns, for example regarding > NULL/NOT NULL defaults. However by default a “columnDefinition” attribute > on @Column also sets the related @JoinColumn’s columnDefinition. > > — > You are receiving this because you modified the open/close state. > Reply to this email directly, view it on GitHub > <https://github.com/doctrine/doctrine2/issues/5617#issuecomment-287862954>, > or mute the thread > <https://github.com/notifications/unsubscribe-auth/AAJakGHuLlEhRO1ub0aO3p7ifEu2m3Ddks5rnsw9gaJpZM4HG8rd> > . >

admin commented

2026-01-22 14:55:10 +01:00

@GreeKatrina commented on GitHub (Mar 21, 2017):

@lcobucci @Ocramius Fair enough. Is it possible to set nullable to true for @JoinColumn, and then do something with columnDefinition that forces something like company_id IS NOT NULL? I wasn't sure if that's what it meant in the docs by "slightly different column definitions for joining columns, for example regarding NULL/NOT NULL defaults".

@GreeKatrina commented on GitHub (Mar 21, 2017): @lcobucci @Ocramius Fair enough. Is it possible to set nullable to true for `@JoinColumn`, and then do something with `columnDefinition` that forces something like `company_id IS NOT NULL`? I wasn't sure if that's what it meant in the docs by "slightly different column definitions for joining columns, for example regarding NULL/NOT NULL defaults".

admin commented

2026-01-22 14:55:11 +01:00

@Ocramius commented on GitHub (Mar 21, 2017):

@GreeKatrina that is possible, but it will still crash the ORM during load operations (due to failed association traversal - NULL means something very specific to the ORM).

@Ocramius commented on GitHub (Mar 21, 2017): @GreeKatrina that is possible, but it will still crash the ORM during load operations (due to failed association traversal - `NULL` means something very specific to the ORM).

Sign in to join this conversation.

Branches Tags

1 Participants

Notifications

Due Date

No due date set.

Dependencies

No dependencies set.

Reference: doctrine/archived-orm#4980