Zend-db how to create a query in zf3 for several tables

Questions Components & MVC
1

hi, i’m new user in zend framework, i’m using Zend Framework version 3.0.3-dev, for postgresql. i have problem with request, so i would like to have your help if it’s possible. sorry for bad English, i have not great experience en English

thise is my fastfood/config/autoload/global.php file :

<?php /** * Global Configuration Override * * You can use this file for overriding configuration values from modules, etc. * You would place values in here that are agnostic to the environment and not * sensitive to security. * * @NOTE: In practice, this file will typically be INCLUDED in your source * control, so do not include passwords or other sensitive information in this * file. */

return array(
‘db’ => array(
‘driver’ => ‘Pdo’,
‘dsn’ => “pgsql:host=localhost;dbname=db_fastfoods”,
‘username’ => ‘milano’,
‘password’ => ‘cheva7’,
),

);


my Modele.php php <?php namespace Telimani;

use Zend\Db\Adapter\Adapter;
use Zend\Db\Adapter\AdapterInterface;
use Zend\Db\ResultSet\ResultSet;
use Zend\Db\TableGateway\TableGateway;
use Zend\ModuleManager\Feature\ConfigProviderInterface;

class Module implements ConfigProviderInterface
{
// getConfig() method:
public function getConfig()
{
return include DIR . ‘/…/config/module.config.php’;
}
// getServiceConfig method:
public function getServiceConfig()
{
return [
‘factories’ => [
//module for commands
Model\CommandsTable::class => function($container) {
$adapter = $container->get(Model\CommandsTableGateway::class);
return new Model\CommandsTable($adapter);
},
Model\CommandsTableGateway::class => function ($container) {
$dbAdapter = $container->get(AdapterInterface::class);
$resultSetPrototype = new ResultSet();
$resultSetPrototype->setArrayObjectPrototype(new Model\Commands());
return new TableGateway(‘commands’, $dbAdapter, null, $resultSetPrototype);
},
],
];
}

   // getControllerConfig method:
   public function getControllerConfig()
   {
       return [
           'factories' => [
                  Controller\CommandsController::class => function($container) {
                   return new Controller\CommandsController(
                       $container->get(Model\CommandsTable::class)
                   );
               },
               //end
           ],
       ];
   }

}

my CommandsTable.php

<?php

namespace Telimani\Model;

use RuntimeException;
use Zend\Db\Adapter\Adapter;
use Zend\Db\Adapter\AdapterInterface;
use Zend\Db\TableGateway\TableGatewayInterface;
use Zend\Db\ResultSet\ResultSet;
//use Zend\Db\Sql\Select;
//use Zend\Db\Sql\Sql;
use Zend\Db\TableGateway\TableGateway;
use Zend\Paginator\Adapter\DbSelect;
use Zend\Paginator\Paginator;

class CommandsTable
{
private $tableGateway;
private $sql;
private $adapter;

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

/* public function __construct(AdapterInterface $adapter)
{
$this->adapter = get(AdapterInterface::class);
}*/

public function fetchAll()
{
  /*$resultSet = $this->adapter->query('SELECT * FROM `commands` WHERE `cmd_id` => 2', array(2));
  return $resultSet;*/

    /*$sql = new Sql($this->tableGateway->getAdapter());
    $select = $sql->select();
    $Select ->from('commands');
  $result = $this->tableGateway->select($select);
  return $result;*/

  $resultSet = $this->adapter->query("SELECT * FROM commands join customers using(custom_id) order by cmd_date");
  return  $resultSet->execute();

  //return  $this->tableGateway->select();
   
}

}

My CommandsController.php

<?php namespace Telimani\Controller;

use Zend\Mvc\Controller\AbstractActionController;
use Telimani\Form\CommandsForm;
use Telimani\Model\Commands;
use Telimani\Model\CommandsTable;
use Zend\View\Model\ViewModel;

class CommandsController extends AbstractActionController
{
// Add this property:
private $table;

   // Add this constructor:
   public function __construct(CommandsTable $table)
   {
       $this->table = $table;
   }

   public function indexAction()
   {
        return new ViewModel([
           'commands' => $this->table->fetchAll(),
       ]);
   }

}


so the idea is create a request over several tables, that can joint and where condition…
Thanks for your help!
2

Couple ideas:

TableGateway has select() method, which takes in closure, not just select statement. When it receives a closure like this:

   $this->tableGateway->select(function(Select $select) {
      $select->from(
             'c' => 'customers', 
             'c.custom_id = command.custom_id' // assuming this is how your join statement looks.
       );
   });

you have opportunity to modify selection before it goes to adapter. Add your additional WHERE, JOIN, etc there.

You are currently constructing your own Select object, then injecting it to TableGateway. Never do that. Try to always let TableGateway be the one at least beginning to construct the Select object based on its configurations, like table name you specified in constructor. When you are not satisfied with default Select object TableGateway, augment it instead by capturing it in the callbacks.

Also consider CommandTable class extend TableGateway. Makes easier to manipulate select functions inside, as you now have better control of select -> selectWith path. You can override those functions to adjust select object however you want before it goes to adapter for execution.

3

Thanks so much for your all your help, in the near future.