How to use a data mapper with sql queries

Question

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 Queries

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

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

Many Thanks
Matt

you'll have to fundamentally change your code, and do a join query to fetch the album + artist information in one single query. — Marc B
– Marc B, Commented Dec 3, 2014 at 16:52
Won't my foreach on album duplicate then? I want each album to echo once and if there is an artist to echo those under the album. This is why I have multiple quires (I don't want to duplicate information). There are multiple artist (band members) per album. — Matt
– Matt, Commented Dec 3, 2014 at 17:38

Weltschmerz · Accepted Answer · 2014-12-03 18:17:01Z

2

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

enter image description here

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

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

}

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

edited Dec 3, 2014 at 18:17

answered Dec 3, 2014 at 17:52

Weltschmerz

2,1861 gold badge15 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

12 Comments

Matt Over a year ago

Does the SQL go in the interface AlbumMapper? or the ConcreteAlbumMapper?

Matt Over a year ago

Would my controller change? Would I create a new instance of Album now instead of my searchQuery? $album = new Album(); //$artist = new SearchQuery($dbAdapter);

Weltschmerz Over a year ago

Don't know what's in your controller. The idea here is to fetch the artists based on album ids using WHERE album_id IN ($albumIds). That way you would have 2 db queries and not as many queries as there are albums in the result. Your db queries for album would go in the ConcreteAlbumMapper, similarly db queries concerning artist would live in ConcreteArtistMapper (some ArtistMapper implementation).

Matt Over a year ago

Got it. I'll keep tinkering. I'm having a little trouble grasping the code needed to implement this logic if you have any sources you recommend I read up on (I've searched around, but having trouble finding a concrete example)

Matt Over a year ago

I have two issues with the above set up. On the ConcreteArtistMapper I get an error (from text editor telling me that it is not abstract). The controller is not correct either (the argument passed is not correct)

|

Collectives™ on Stack Overflow

How to use a data mapper with sql queries

1 Answer 1

12 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

12 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related