如何在SQL查询中使用数据映射器 [英] How to use a data mapper with sql queries

问题描述

我无法理解datamapper设计模式。我有两个查询（一个查询专辑，一个查询歌手）。我想列出一张专辑和艺术家（乐队成员）的清单。两者之间存在一对多的关系。

I am having trouble understanding the datamapper design pattern. I have two queries (one to get albums and one to get artist). I want to produce a list of albums and artists (band members). There is a one to many relationship between the two.

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/ 吗？我可以设置每个实体代表一个查询吗（我想在查询中使用联接而不是为每个表创建一个实体）？

When I do something similar to below I end up running a separate query for each album. I don't want to do this. How do I go about setting something like this up http://akrabat.com/php/objects-in-the-model-layer-part-2/ ? Can I set up so each entity represents a query (I would like to use joins in my queries as opposed to creating an entity for each table)?

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

使用下面提供的解决方案

Using Solution Provided Below

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,
     ));
 }

非常感谢

Matt

Many Thanks
Matt

推荐答案

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

There are many ways to approach this, this is one of them. Let's say you have a db schema similar to this:

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

Now you can have AlbumMapper and ArtistMapper responsible for fetching those objects from the db for you:

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，所以使用此mapper总是返回相册与他们的艺术家。现在，示例实现可以像这样，其中我使用了一些索引技巧将歌手附加到专辑：

I've put that AlbumMapper requires ArtistMapper so with this mapper Albums are always returned with their artists. Now an example implementation can be like this where I use a little indexing trick to attach artist to albums:

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;
    }

}

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

In this case your Album will be along the lines of:

class Album {

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

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

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

}

最后，您应该具有用其Artists初始化的Album对象的集合。

At the end of all this you should have a collection of Album objects initialized with their Artists.

这篇关于如何在SQL查询中使用数据映射器的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！