All geek questions in one place

How to configure ResultSetMapping for association / foreign key [was Why associations returned by inline queries are always zero]

See editing at the end for an updated question about ResultSetMapping

I have two entities defined (Item and ItemType), one of which has ManyToOne associated with the other. I have quite a lot of my own queries due to the complexity of generating a search for the correct elements. These queries always return all columns of the first object (SELECT. * ... elements).

I found that my associations are always null for the first element, and I'm not sure what I'm doing wrong. Any help would be appreciated.

Entities:

namespace AppBundle\Entity;

use Psr\Log\LoggerInterface;
use Doctrine\ORM\Mapping as ORM;

/**
 * @ORM\Table(name="items")
 * @ORM\Entity(repositoryClass="AppBundle\Entity\ItemRepository")
 */
class Item {

    /**
     * @ORM\Column(type="integer")
     * @ORM\Id
     * @ORM\GeneratedValue(strategy="AUTO")
     */
    private $id;

    /**
     * @ORM\Column(name="account_id", type="integer")
     */
    private $accountId;

    /**
     * @ORM\ManyToOne(targetEntity="ItemType")
     * @ORM\JoinColumn(name="item_type_id", referencedColumnName="id")
     */
    private $itemType;

    // ..snip.. //

}

ItemType

namespace AppBundle\Entity;

use Doctrine\ORM\Mapping as ORM;

/**
 * @ORM\Table(name="item_types")
 * @ORM\Entity(repositoryClass="AppBundle\Entity\ItemTypeRepository")
 */
class ItemType {

    /**
     * @ORM\Column(type="integer")
     * @ORM\Id
     * @ORM\GeneratedValue(strategy="AUTO")
     */
    private $id;

    /**
     * @ORM\Column(name="account_id", type="integer")
     */
    private $accountId;

    /**
     * @ORM\Column(type="string", length=128)
     */
    private $name;

    // ..snip.. //

}

getItem ItemRepository. , SELECT items.* FROM items ... getEntityManager()->createNativeQuery($sql, $rsm);

namespace AppBundle\Entity;

use Psr\Log\LoggerInterface;
use Doctrine\ORM\Query\ResultSetMapping;

/**
 * ItemRepository
 *
 * This class was generated by the Doctrine ORM. Add your own custom
 * repository methods below.
 */
class ItemRepository extends \Doctrine\ORM\EntityRepository
{
    /**
     * @var \Psr\Log\LoggerInterface $logger
     */
    protected $logger;

    /**
     * @var ItemTypeRepository
     */
    protected $itemTypes;

    /**
     * @var ItemValueRepository
     */
    protected $itemValues;

    /**
     * @var FieldRepository
     */
    protected $fields;

    /**
     * Called by service bindings in services.yml instead of __construct, which is needed by
     * Doctrine.
     */
    public function initService(LoggerInterface $logger,
                                ItemTypeRepository $itemTypes,
                                ItemValueRepository $itemValues,
                                FieldRepository $fields)
    {
        $this->logger = $logger;
        $this->itemTypes = $itemTypes;
        $this->itemValues = $itemValues;
        $this->fields = $fields;
    }

    /**
     * Get items for an account via itemId
     *
     * @param integer $accountId a user account id
     * @param $itemId unique ID for an Item
     * @return Item_model
     */
    public function getItem($accountId, $itemId, $restrictedUserOwnerItemType, $restrictedUserOwnerItemId)
    {
        $this->logger->debug(__METHOD__.'::params::'.json_encode(['accountId' => $accountId, 'itemId' => $itemId,
                             'restrictedUserOwnerItemType' => $restrictedUserOwnerItemType, 'restrictedUserOwnerItemId' => $restrictedUserOwnerItemId]));
        if(!$accountId || !$itemId || !is_numeric($restrictedUserOwnerItemType) || !is_numeric($restrictedUserOwnerItemId)) 
            throw new \InvalidArgumentException('getItem requires accountId, itemId, restrictedUserOwnerItemType and restrictedUserOwnerItemId');

        /*
        $query = $this->itemsModel->builder();
        $result = $query->where('account_id', '=', $accountId)
                        ->where('id', '=', $itemId)
                        ->first();
        */

        $sql = "SELECT items.*, ".
        "item_types.id AS item_type_id, ".
        "item_types.account_id AS item_type_account_id, ".
        "item_types.name AS item_type_name, ".
        "item_types.plural_name AS item_type_name, ".
        "item_types.label AS item_type_label, ".
        "item_types.plural_label AS item_type_plural_label, ".
        "item_types.are_users AS item_type_are_users, ".
        "item_types.own_users AS item_type_own_users ".
        "FROM items ".
        "JOIN item_types ON item_types.id = items.item_type_id ";

        $isRestrictedUser = $restrictedUserOwnerItemType != 0 || $restrictedUserOwnerItemId != 0;
        if($isRestrictedUser)
        {
            // Limit to items that are visible to restricted users
            $sql .= <<<SQL


      WHERE item_types.visible_to_restricted_users = 1 

SQL;

            // Limit to items that contain a relationship field pointed at the same owner item type,
            // with the same item ID. For instance, limit items to those that have a Clients relationship
            // field with "Acme Co." client selected as the client.
            $sql .= <<<SQL

AND items.id IN ( /* Where Item Belongs to Same Owner */
      SELECT item_id 
      FROM item_values 
      JOIN fields ON fields.id = item_values.field_id
      JOIN items ON items.id = item_values.item_id AND item_values.ver = items.ver
      JOIN item_types ON item_types.id = items.item_type_id
      WHERE item_values.value = ?
        AND fields.field_type = "Relationship"
        AND fields.field_item_type_id = ?)

SQL;
            $params[] = $restrictedUserOwnerItemId;     // Example: 3  -- CLIENT ID
            $params[] = $restrictedUserOwnerItemType;   // Example: 10 -- CLIENTS

            $sql .= "AND ";
        } else {
            $sql .= "WHERE ";
        }

        $sql .= "items.account_id = ? AND items.id = ? ";
        $params[] = $accountId;
        $params[] = $itemId;

        // Get raw records
        $rsm = $this->standardResultSetMapping();
        // $this->logger->debug($sql);
        // $this->logger->debug(print_r($params, true));
        echo $sql;
        $query = $this->getEntityManager()->createNativeQuery($sql, $rsm);
        $query->setParameters($params);

        // Wake up the entities
        $result = array();
        foreach($query->getResult() as $row) {
            $row->initServiceEntity($this->logger, $this, $this->itemValues, $this->fields);
            $result[] = $row;
        }

        if(!$result || count($result) == 0)
            throw new \InvalidArgumentException("Item could not be located for Item #".$itemId.". You may not have permission to view this item or it may not exist.");
        else
        {
            return $result[0];
        }
    }

    private function standardResultSetMapping()
    {
        $rsm = new ResultSetMapping();
        //                    Class,                    Table
        $rsm->addEntityResult('\AppBundle\Entity\Item', 'items');
        $rsm->addEntityResult('\AppBundle\Entity\ItemType', 'item_types');
        //                   Table,   Column,           Property
        $rsm->addFieldResult('items', 'id',             'id');
        $rsm->addFieldResult('items', 'account_id',     'accountId');
        //$rsm->addFieldResult('items', 'item_type_id',   'itemTypeId');
        $rsm->addFieldResult('items', 'field_count',    'fieldCount');
        $rsm->addFieldResult('items', 'ver',            'ver');
        $rsm->addFieldResult('items', 'title',          'title');
        $rsm->addMetaResult('items', 'item_type_id',    'item_type_id', true);

        $rsm->addFieldResult('item_types', 'item_type_id',          'id');
        $rsm->addFieldResult('item_types', 'item_type_name',        'name');
        $rsm->addFieldResult('item_types', 'item_type_plural_name', 'pluralName');
        $rsm->addFieldResult('item_types', 'item_type_label',       'label');
        $rsm->addFieldResult('item_types', 'item_type_plural_label','pluralLabel');
        $rsm->addFieldResult('item_types', 'item_type_are_users',   'areUsers');
        $rsm->addFieldResult('item_types', 'item_type_own_users',   'ownUsers');

        return $rsm;
    }

}

Item , null itemType:

Item {#548 β–Ό
  -id: 23
  -accountId: 1
  -itemType: null
  -fieldCount: 4
  -ver: 1451940837
  -title: "New Item"
  #fields: []
  #itemValues: []
  #cacheValues: []
  #logger: Logger {#268 β–Ά}
  #itemsRepository: ItemRepository {#349 β–Ά}
  #itemValuesRepository: ItemValueRepository {#416 β–Ά}
  #fieldsRepository: FieldRepository {#338 β–Ά}
  #loaded: true
  #changeCount: 0
}

item_types

id  account_id  name  plural_name label plural_label  are_users own_users
31  1           task  tasks       Task  Tasks         1         0


id  account_id  item_type_id  field_count ver         title
23  1           31            4           1451940837  New Item

, , ResultSetMapping. . , ( ItemType - ):

object(AppBundle\Entity\Item)[560]
  private 'id' => int 23
  private 'accountId' => int 1
  private 'itemType' => null
  private 'fieldCount' => int 4
  private 'ver' => int 1451940837
  private 'title' => string 'New Item' (length=8)
  protected 'fields' => 
    array (size=0)
      empty
  protected 'itemValues' => 
    array (size=0)
      empty
  protected 'cacheValues' => 
    array (size=0)
      empty
  protected 'logger' => null
  protected 'itemsRepository' => null
  protected 'itemValuesRepository' => null
  protected 'fieldsRepository' => null
  protected 'loaded' => boolean false
  protected 'changeCount' => int 0
object(AppBundle\Entity\ItemType)[507]
  private 'id' => int 31
  private 'accountId' => int 1
  private 'name' => string 'task' (length=4)
  private 'pluralName' => string 'tasks' (length=5)
  private 'label' => string 'Task' (length=4)
  private 'pluralLabel' => string 'Tasks' (length=5)
  private 'areUsers' => boolean true
  private 'ownUsers' => boolean false

, :

ResultSetMapping , Entity ?

+4
2

Doctrine Native SQL , , . , addJoinedEntityResult() addEntityResult() ItemType.

Entity:

, .

, , , , - Item, ItemType, , , , "Result Entity Result" :

, , () .

,

 $rsm->addEntityResult('\AppBundle\Entity\ItemType', 'item_types');

:

$rsm->addJoinedEntityResult(
    '\AppBundle\Entity\ItemType',
    'item_types',
    'items',
    'itemType'
);

addJoinedEntityResult($class, $alias, $parentAlias, $relation), 3- 4- , , ItemType.

, , , ResultSetMappingBuilder. SQL, , - , .

standardResultSetMapping() :

$rsm = new ResultSetMappingBuilder($this->_em);
$rsm->addRootEntityFromClassMetadata('AppBundle\Entity\Item', 'items');
$rsm->addJoinedEntityFromClassMetadata('AppBundle\Entity\ItemType', 'item_types', 'items', 'itemType',
    ['id' => 'item_type_id', 
     'account_id' => 'item_type_id', 
     'name' => 'item_type_name',
     'plural_name' => 'item_type_plural_name',
     'label' => 'item_type_label',
     'plural_label' => 'item_type_plural_label',
     'are_users' => 'item_type_are_users',
     'own_users' => 'item_type_own_users']
);

, , . , .

+3

, (items. *) doctrine - .

, . i.e.:

SELECT items.*, item_types.* ...
+1

Source: https://habr.com/ru/post/1622960/

More articles:


All Articles