ZF2 Pdo, TableGateway and Sql

classic Classic list List threaded Threaded
6 messages Options
Reply | Threaded
Open this post in threaded view
|

ZF2 Pdo, TableGateway and Sql

Paladyn
This post has NOT been accepted by the mailing list yet.
I`ll try to simple show what I`m doing and I`ll be glad for any directions to go from where I am now.

There are two tables -> news and content (one news can have many contents - one for each language).

In ZF1 I was making sql pdo query from news table with join content table. Then fetchAll (or fetchPairs in other modules using content) and pass it to view.

Now in ZF2 I wonder what should I do:
- make two Table files (extanding ableGateway) one for each table and connect them in some way in main model file ?
- make like in ZF1 sql pdo query ?

In ZF2 I miss fetchAll() function.

Is Sql/Result getResource() function only way to get all rows from sql pdo query?

(Sorry for not very strait showing of my problem. My brain got confused after few days in ZF2 :)
luk
Reply | Threaded
Open this post in threaded view
|

Re: ZF2 Pdo, TableGateway and Sql

luk
Paladyn wrote
I`ll try to simple show what I`m doing and I`ll be glad for any directions to go from where I am now.

There are two tables -> news and content (one news can have many contents - one for each language).

In ZF1 I was making sql pdo query from news table with join content table. Then fetchAll (or fetchPairs in other modules using content) and pass it to view.

Now in ZF2 I wonder what should I do:
- make two Table files (extanding ableGateway) one for each table and connect them in some way in main model file ?
- make like in ZF1 sql pdo query ?

In ZF2 I miss fetchAll() function.

Is Sql/Result getResource() function only way to get all rows from sql pdo query?

(Sorry for not very strait showing of my problem. My brain got confused after few days in ZF2 :)
You can create your own fetchAll function that returns ResultSet object.

For getting news and contents I would create a class called NewsMapper thats uses News TableGateway and method getWithContents (or getNewsWithContents) then build a query with join that returns a ResultSet object.
Cheers, -- Luke Mierzwa
Reply | Threaded
Open this post in threaded view
|

Re: ZF2 Pdo, TableGateway and Sql

Paladyn
This post has NOT been accepted by the mailing list yet.
I`ve build NewsMapper as you wrote. It is good and easy to implement idea. But now I`m stuck at Pdo\Result Object. I`m not sure how to convert this one to ResultSet or just convert my sql query.

This is my code (based on Zend\Db\Sql tutorial):

$sql = new Sql($this->adapter);
$select = $sql->select();
$select->from(array('t' => $this->table));
$select->join(array('c' => 'ontent'),
             't.id = c.controller_id',
             array('title'));
$select->where(array('c.controller' => $this->controller));
               
$stmt = $sql->prepareStatementForSqlObject($select);
$results = $stmt ->execute();

I wonder if there is some kind of function "prepareStatementForResultSetObject".

I`m grateful for help.
luk
Reply | Threaded
Open this post in threaded view
|

Re: ZF2 Pdo, TableGateway and Sql

luk
Paladyn wrote
I`ve build NewsMapper as you wrote. It is good and easy to implement idea. But now I`m stuck at Pdo\Result Object. I`m not sure how to convert this one to ResultSet or just convert my sql query.

This is my code (based on Zend\Db\Sql tutorial):

$sql = new Sql($this->adapter);
$select = $sql->select();
$select->from(array('t' => $this->table));
$select->join(array('c' => 'ontent'),
             't.id = c.controller_id',
             array('title'));
$select->where(array('c.controller' => $this->controller));
               
$stmt = $sql->prepareStatementForSqlObject($select);
$results = $stmt ->execute();

I wonder if there is some kind of function "prepareStatementForResultSetObject".

I`m grateful for help.
I've followed Zend Framework 2 User Guide by akrabat and I could find there how to setup ResultSet object.
If you got your Table class extending from AbstractTableGateway you can set ResultSet it in your __construct method, like:

namespace News\Model;

use Zend\Db\TableGateway\AbstractTableGateway;
use Zend\Db\Adapter\Adapter;
use Zend\Db\ResultSet\ResultSet;

class NewsTable extends AbstractTableGateway
{

    protected $table = 'news';

    public function __construct(Adapter $adapter)
    {
        $this->adapter = $adapter;
        $this->resultSetPrototype = new ResultSet();
        $this->resultSetPrototype->setArrayObjectPrototype(new News()); // make sure you have your News Entity in the same namespace
        $this->initialize();
    }

    public function getNewsWithContents()
    {
        $resultSet = $this->select();
        // you would need to pass where inside the select function not sure how join will work though, haven't got time to check that
        return $resultSet;
    }
}

And this sample is based on Table class not on Mapper, normally you would inject that Table class inside your Mapper.
Cheers, -- Luke Mierzwa
Reply | Threaded
Open this post in threaded view
|

Re: ZF2 Pdo, TableGateway and Sql

Paladyn
This post has NOT been accepted by the mailing list yet.
This is what I have started from. I have made object prototype (with values from both news and content table) but then got stuck on trying to get values from both tables to ResultSet Object. Then I`ve moved to well known (from ZF1) sql class and got stuck on formating values. Of course I can build object manualy by foreach values from $results->getResource() and setting up array of objects from this but it doesn`t seem to be right way.
Reply | Threaded
Open this post in threaded view
|

Re: ZF2 Pdo, TableGateway and Sql

Paladyn
This post has NOT been accepted by the mailing list yet.
I`ve got it!

First: Thanks for help.

Second: If anyone will need it.

public function __construct(Adapter $adapter)
{
    $this->adapter = $adapter;
    $this->resultSetPrototype = new ResultSet();
    $this->resultSetPrototype->setArrayObjectPrototype(new News());
    $this->initialize();
}

public function fetchAll()
{
   $sql = new Sql($this->adapter);
   $select = $sql->select();
   $select->from(array('t' => 'news'));
   $select->join(array('c' => 'content'),
        't.id = c.controller_id',
        array('title'));
   $select->where(array('c.controller' => $this->controller));

   $statement = $sql->prepareStatementForSqlObject($select);
   $results = $statement->execute();
               
   $resultSet = $this->resultSetPrototype->initialize($results);  // here you put Sql Object into ResultSet Object

   return $resultSet;
}