php - 如何在 sql 查询中使用数据映射器

Question

我无法理解数据映射器设计模式。我有两个查询(一个是获取专辑，一个是获取艺术家)。我想制作一张专辑和艺术家(乐队成员)的列表。两者之间是一对多的关系。

SQL 查询

public function getArtist()
{
    $adapter = $this->getAdapter();

    $sql = new Sql($adapter);
    $select = $sql->select();
    $select->from('genre');  
    $select->where(array(
            'album_id' => $album,));
    $selectString = $sql->getSqlStringForSqlObject($select);
    $resultSet = $adapter->query($selectString, $adapter::QUERY_MODE_EXECUTE);
    return $resultSet;  
}  

public function getAlbum()
{
    $adapter = $this->getAdapter();

    $sql = new Sql($adapter);
    $select = $sql->select();
    $select->from('album');  
    $selectString = $sql->getSqlStringForSqlObject($select);
    $resultSet = $adapter->query($selectString, $adapter::QUERY_MODE_EXECUTE);
    return $resultSet;  
}  

当我执行类似于下面的操作时，我最终会为每个专辑运行一个单独的查询。我不想这样做。我该如何设置这样的东西 http://akrabat.com/php/objects-in-the-model-layer-part-2/ ？我可以设置每个实体代表一个查询吗(我想在我的查询中使用连接而不是为每个表创建一个实体)？

 <?php foreach ($albums->getAlbum() as $album) : ?>
 <tr>
     <td><?php echo $this->escapeHtml($album->id);?></td>
     <td><?php echo $this->escapeHtml($album->title);?></td>
     <td><?php echo $this->escapeHtml($album->artist);?></td>
     <td>
           <?php foreach ($albums->getArtist($album->id) as $member) : ?>

使用下面提供的解决方案

ConcreteAlbumMapper.php

use Zend\Db\Adapter\AdapterInterface;
use Album\Model\AlbumMapper;

namespace Album\Model;

class ConcreteAlbumMapper implements AlbumMapper {


    public function __construct(AdapterInterface $dbAdapter)
     {
        $this->adapter =$dbAdapter;
     }

    public function fetchAlbums(ArtistMapper $artistMapper) {
           $adapter = $this->getAdapter();
            $sql = new Sql($adapter);
            $select = $sql->select();
            $select->from('album');  
            $selectString = $sql->getSqlStringForSqlObject($select);
            $albumRows = $adapter->query($selectString, $adapter::QUERY_MODE_EXECUTE);

        $albums = array();

        foreach ($albumRows as $albumRow) {
            $albums[$albumRow['id']] = new Album($albumRow);
        }

        $artists = $artistMapper->fetchByAlbumIds(array_keys($albums));

        //marrying album and artists
        foreach ($artists as $artist) {
            /**
             * not crazy about the getAlbumId() part, would be better to say
             * $artist->attachYourselfToAnAlbumFromThisIndexedCollection($albums);
             * but going with this for simplicity
             */
            $albums[$artist->getAlbumId()]->addArtist($artist);
        }

        return $albums;
    }

}

ConcreteMemberMapper.php

<?php

namespace Album\Model;

use Zend\Db\Adapter\AdapterInterface;
use Zend\Db\Sql\Sql;
use Album\Model\Member;

class ConcreteMemberMapper { 



   public function __construct(AdapterInterface $dbAdapter)
     {
        $this->adapter =$dbAdapter;
     } 

     public function getAdapter()
    {
   if (!$this->adapter) {
      $sm = $this->getServiceLocator();
      $this->adapter = $sm->get('Zend\Db\Adapter\Adapter');
    }
    return $this->adapter;
    }

    public function fetchByAlbumIds($ids) {
        $adapter = $this->getAdapter();

        $sql = new Sql($adapter);
        $select = $sql->select();
        $select->from('members');
        $select->where(array(
                'album_id' => $ids));
        $selectString = $sql->getSqlStringForSqlObject($select);
        $results = $adapter->query($selectString, $adapter::QUERY_MODE_EXECUTE);
        return $results;
        //return \Zend\Stdlib\ArrayUtils::iteratorToArray($results);
    }

    public function getAlbumId()
    {
        return $this->albumId;
    }

}

Album.php

<?php
namespace Album\Model;

 use Zend\InputFilter\InputFilter;
 use Zend\InputFilter\InputFilterAwareInterface;
 use Zend\InputFilter\InputFilterInterface;

 class Album //implements InputFilterAwareInterface
 {

    public $id;
    public $title;
    public $artist;
    public $members = array();

    public function __construct($data) {
         $this->id     = (!empty($data['id'])) ? $data['id'] : null;
         $this->artist = (!empty($data['artist'])) ? $data['artist'] : null;
         $this->title  = (!empty($data['title'])) ? $data['title'] : null;
         $this->members  = (!empty($data['members'])) ? $data['members'] : null;
    }

    public function addMember(Member $member) {
        $this->members[] = $member;
    }

AlbumController.php

 public function indexAction()
 {
     $dbAdapter = $this->getServiceLocator()->get('Zend\Db\Adapter\Adapter');  
     //$album = new Album(); 
     $albumMapper = new ConcreteAlbumMapper($dbAdapter);
     $memberMapper = new ConcreteMemberMapper($dbAdapter);
     $bar=$albumMapper->fetchAlbums($memberMapper);
     //$artistMapper = new 
     //var_dump($bar);
    return new ViewModel(array(
        'albums' => $bar,
     ));
 }

非常感谢
马特

Answer 1

有很多方法可以解决这个问题，这是其中之一。假设您有一个与此类似的数据库架构:

现在您可以让 AlbumMapper 和 ArtistMapper 负责为您从数据库中获取这些对象:

interface AlbumMapper {

    /**
     * Fetches the albums from the db based on criteria
     * @param type $albumCriteria
     * @param ArtistMapper $artistMapper
     * 
     * Note: the ArtistMapper here can be also made optional depends on your app
     */
    public function fetchBySomeCriteria($albumCriteria, ArtistMapper $artistMapper);
}

interface ArtistMapper {

    /**
     * @param array $ids
     * @return Artist[]
     */
    public function fetchByAlbumIds(array $ids);
}

我已经把 AlbumMapper 需要 ArtistMapper，所以有了这个映射器，专辑总是和他们的艺术家一起返回。现在一个示例实现可以是这样的，我使用一些索引技巧将艺术家附加到专辑:

class ConcreteAlbumMapper implements AlbumMapper {

    public function fetchBySomeCriteria($albumCriteria, ArtistMapper $artistMapper) {
        //sql for fetching rows from album table based on album criteria

        $albums = array();

        foreach ($albumRows as $albumRow) {
            $albums[$albumRow['id']] = new Album($albumRow);
        }

        $artists = $artistMapper->fetchByAlbumIds(array_keys($albums));

        //marrying album and artists
        foreach ($artists as $artist) {
            /**
             * not crazy about the getAlbumId() part, would be better to say
             * $artist->attachYourselfToAnAlbumFromThisIndexedCollection($albums);
             * but going with this for simplicity
             */
            $albums[$artist->getAlbumId()]->addArtist($artist);
        }

        return $albums;
    }

}

在这种情况下，您的相册将是:

class Album {

    private $id;
    private $title;
    private $artists = array();

    public function __construct($data) {
        //initialize fields
    }

    public function addArtist(Artist $artist) {
        $this->artists[] = $artist;
    }

}

在所有这一切结束时，您应该有一个用艺术家初始化的 Album 对象集合。