如何在ZF2中执行手动编写的查询或存储过程

I'm following the tutorial at http://framework.zend.com/manual/2.0/en/user-guide/database-and-models.html, and I have set up my Module::getServiceConfig() and my AlbumTable class as they've shown.

我正在http://framework.zend.com/manual/2.0/en/user-guide/database-and-models.html上关注教程,我已经设置了我的Module :: getServiceConfig()和我的AlbumTable正如他们所展示的那样。

Now say I need to execute a stored procedure to get my result set...not a standard SELECT. I still want the result set to be a collection of Album models. How can this be done?

现在说我需要执行存储过程来获取我的结果集...而不是标准的SELECT。我仍然希望结果集是Album模型的集合。如何才能做到这一点?

Here's an example of what I've tried:

这是我尝试过的一个例子:

AlbumTable.php:

public function findByArtist($artist) {
    $adapter = $this->tableGateway->getAdapter();
    $result = $adapter->query(
        "EXEC spGetAlbums @artist = :artist",
        array(':artist' => $artist)
    );

    var_dump($result->current());
}

The problem with this is that the $result returned is a collection of ArrayObjects...not a collection of Album models. So how can I execute the stored procedure but still get the results according to what I've set up in Module::getServiceConfig()?

这个问题是返回的$ result是ArrayObjects的集合......不是Album模型的集合。那么如何执行存储过程但仍然根据我在Module :: getServiceConfig()中设置的结果得到结果?

Please don't bother asking what my stored procedure is doing...that's irrelevant to this question. Point is, it returns results that are compatible with the Album model.
_{^ Not meant to be attitude, just trying to save everyone the time of suggesting that I don't need to use a stored procedure.}

请不要问我的存储过程在做什么......这与这个问题无关。点是,它返回与Album模型兼容的结果。 ^并不意味着态度,只是试图节省每个人建议我不需要使用存储过程的时间。

1 个解决方案

#1

As @Sam suggested in the question comments, there doesn't appear to be a way to execute a native SQL query on a TableGateway object, so you would have to manually hydrate the data object.

正如@Sam在问题评论中建议的那样,似乎没有办法在TableGateway对象上执行本机SQL查询,因此您必须手动水合数据对象。

After looking at Zend's Docs on HydratingResultSet, here's the solution I came up with:

在查看Zend的HydratingResultSet文档后,我提出了解决方案:

namespace Album\Table;

use Zend\Db\TableGateway\TableGateway;
use Zend\Db\ResultSet\HydratingResultSet;
use Zend\Stdlib\Hydrator;

class Album {

    protected $tableGateway;

    public function __construct(TableGateway $tableGateway) {
        $this->tableGateway = $tableGateway;
    }

    public function findByArtist($artist) {
        $adapter = $this->tableGateway->getAdapter();
        $result = $adapter->query(
            "EXEC spGetAlbums @artist = :artist",
            array(':artist' => $artist)
        );

        $dataSource = $result->getDataSource();
        if ($dataSource instanceof \Iterator) {
            $result = new HydratingResultSet(
                new Hydrator\ArraySerializable(),
                new \Album\Model\Album()
            );
            $result->initialize($dataSource);
        }

        return $result;
    }
}

UPDATE:

I didn't really get what was going on with the ReflectionHydrator. It was populating my object via reflection, and it was not calling my exchangeArray() method. I realized this when I went to convert a smalldatetime value from the database into a \DateTime object in my exchangeArray() method...it wasn't getting called.

我并没有真正了解ReflectionHydrator的情况。它通过反射填充我的对象,它没有调用我的exchangeArray()方法。当我将数据库中的smalldatetime值转换为我的exchangeArray()方法中的\ DateTime对象时,我意识到这一点......它没有被调用。

The fix for this was to use an ArraySerializable hydrator instead. So I've updated the code above to demonstrate this.

解决这个问题的方法是使用ArraySerializable水合器代替。所以我已经更新了上面的代码来演示这个。

If you wanted the hydrator to use your setters (like setArtist() and setTitle()), you could use the ClassMethods hydrator.

如果你想让保湿器使用你的setter(比如setArtist()和setTitle()),你可以使用ClassMethods保湿器。

If your properties are public (or if you're using a magic __set() method), you could use the ObjectProperty hydrator and it will just assign the values to your properties.

如果您的属性是公共的(或者如果您使用的是魔法__set()方法),则可以使用ObjectProperty水合器,它只会将值分配给您的属性。

#1