Doctrine Query with Multiple Joins only populating Main Entity

Question

So far I have tried the following but I keep only getting the main Entity information joined entities do not make it to the result:

Option 1(Using ResultSetMapping Builder):

$rsm = new ResultSetMappingBuilder(
    $this->_em,
    ResultSetMappingBuilder::COLUMN_RENAMING_INCREMENT
);
$rsm->addRootEntityFromClassMetadata(
    'CountryApp\StoreBundle\Entity\Product', 'p'
);
$rsm->addJoinedEntityFromClassMetadata(
    'CountryApp\StoreBundle\Entity\Category', 'c', 'p', 'category'
);
$rsm->addJoinedEntityFromClassMetadata(
    'CountryApp\StoreBundle\Entity\CustomerProductPrice', 'cpp', 'p', 'customerPrices'
);

$result = $this->_em
    ->createNativeQuery(
        '
    SELECT
        p.id,
        p.code,
        p.name,
        p.cost,
        p.rrp,
        p.status,
        p.notes,
        p.out_of_stock_since,
        p.available_in,
        c.id,
        c.name,
        c.code,
        cpp.id,
        cpp.price
    FROM product as p
    JOIN category as c ON c.id = p.category_id AND p.status != "DELETED"
    LEFT JOIN customer_product_price as cpp ON cpp.product_id = p.id AND cpp.customer_id = :customer
', $rsm
    )
    ->setParameter('customer', $customerId)
    ->getResult(Query::HYDRATE_ARRAY)
;

Option 2:(using QueryBuild and FetchMode)

$qb     = $this->createQueryBuilder('p');
$result = $qb
    ->select('p')
    ->addSelect('c')
    ->addSelect('cpp')
    ->join(
        'CountryApp\StoreBundle\Entity\Category',
        'c',
        Join::WITH,
        $qb->expr()
           ->eq('c', 'p.category')
    )
    ->leftJoin(
        'CountryApp\StoreBundle\Entity\CustomerProductPrice',
        'cpp',
        Join::WITH,
        $qb->expr()
           ->andX(
               $qb->expr()
                  ->eq('p', 'cpp.product'),
               $qb->expr()
                  ->eq('cpp.customer', ':customer')
           )
    )
    ->setParameter('customer', $customerId)
    ->getQuery()
    ->setFetchMode(
        'CountryApp\StoreBundle\Entity\Category', 'product', ClassMetadata::FETCH_EAGER
    )
    ->setFetchMode(
        'CountryApp\StoreBundle\Entity\CustomerProductPrice', 'product', ClassMetadata::FETCH_EAGER
    )
    ->getResult(Query::HYDRATE_ARRAY)
;

Please advise your thoughts as to what could make this work. I want to obtain the following structure:

[
  0 => [
    Product[
      ..
    ]
    Category[
      ..
    ]
    CustomerProductPrice[
      ..
    ]
  ],
  1 => [
    Product[
      ..
    ]
    Category[
      ..
    ]
    CustomerProductPrice[
      ..
    ]
  ],
..

.
]

Do you not get data back or ist just not in the expected format? Because your mapping suggests, you should get a Product and inside that object Category and CustomerProductPrice exist. So the joined entities are children not siblings of Product. This is expected behavior. But you could map the resulting list of Products to an array structure later on. — dbrumann, Mar 01 '19 at 07:56
I only get Product the children or Customer and CustomerProductPrice are not populated. — eiiCreative, Mar 01 '19 at 08:55
@eiiCreative could you explain a problem you are trying to solve? — vytsci, Mar 03 '19 at 10:27
@vytsci I am essentially trying to return an array of results in one SQL call to avoid jumping back and forth to the server. In cakephp I do this using contain which ensures my data is already in the result. It just seems I cannot find a way to do this in doctrine. I know I could change the annotations to make it Eager instead of lazy but that would affect every use of the entity instead of just this usecase. — eiiCreative, Mar 03 '19 at 20:26

score 0 · Accepted Answer · answered Mar 04 '19 at 09:01

While using Doctrine you define your relationships inside of your entity.

You can read more here https://symfony.com/doc/current/doctrine/associations.html always read the documentation and best practices. I dunno if you are using Symfony or not, but this is a great example and more understandable than Doctrine docs.

/**
 * @ORM\Entity()
 */
class Product
{
    // ...

    /**
     * @ORM\ManyToOne(targetEntity="App\Entity\Category", inversedBy="products")
     */
    private $category;

    public function getCategory(): ?Category
    {
        return $this->category;
    }

    public function setCategory(?Category $category): self
    {
        $this->category = $category;

        return $this;
    }
}

As you see here you define an entity that holds all associations and properties.

Normally association will be lazy loaded by default if you call $product->getCategory() you category will be lazy loaded. If you do not like lazy loading you can always eager fetch with

/**
 * @ManyToOne(targetEntity="Category", cascade={"all"}, fetch="EAGER")
 */

And you will receive an array of products where each product will have a property named category and will contain Category entity inside it.

This is the main difference between CakePHP because in CakePHP you get all associations separately and in Symfony, you get a tree of associations.

And your queries seems too complex and in most cases, you do not have to modify queries like that at all. But be careful with lazy loads if you lazy load data on huge lists you will end up with poor performance.

thanks @vytsci I ended up going with the fetch EAGER setting on Customer Entity Class since I only need the CustomerProductPrice when querying Customer and I query Customer a lot less than I query Product. Would be nice though to see if fetchMode can actually be changed adhoc on per query basis escpecially in the case of report queries. Unfortunately my tries so far did not work. — eiiCreative, Mar 04 '19 at 19:13
Well you can https://stackoverflow.com/questions/6939339/in-doctrine-2-can-the-fetch-mode-eager-lazy-etc-be-changed-at-runtime but official doctrine docs are not working, but somewhere in comments there are syntax written. Check this out. — vytsci, Mar 05 '19 at 08:38
Got it I have to explicitly name the fields I need I will update here shortly thanks @vytsci — eiiCreative, Mar 05 '19 at 08:42

Doctrine Query with Multiple Joins only populating Main Entity

1 Answers1