how to join tables using tablegateway

How to join tables in zend3 when using tableadapter? The question is not about how to join tables in general, it is about how to do this in zend and where to place the code.

Let's say I habe a *table class for example:

namespace Import\Model;
use RuntimeException;
use Zend\Db\TableGateway\TableGatewayInterface;

class ProjectTable
{
    private $tableGateway='t_project';

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

public function fetchAll()
{
    return $this->tableGateway->select();
}

I would like to join two tables, how can I do that, ist here the right place to do so? I tried to implement the following function:

public function Project_Unit(Unit $unit = null){

    $select = $this->tableGateway->getSql()->select()
    ->join('t_unit', 't_project.ProjectID = t_unit.ProjectID',array('UnitID','CI_Number', 'Unitname','Shortcut','Suppliername'));       //, left
    return $this->tableGateway->selectWith($select);

}

I didn't get an error, I got instead mixed up data. After that I tried with aliases did not work either.

My question is, how to build this tableclass, if I need to join two tables. The tables will be project 1 -> n unit (key projectID). The second question would be how to use aliases correctly, because I have some fieldnames in both table with different data, for example each table has a column shortcut.

EDIT: New information To see where the data comes from, I renamed the variables of my Exchangearray:

public function exchangeArray(array $data)
{
    $this->PProjectID= !empty($data['ProjectID']) ? $data['ProjectID'] : null;
    $this->PCI_Number= !empty($data['CI_Number']) ? $data['CI_Number'] : null;
    $this->PDescription= !empty($data['Description']) ? $data['Description'] : null;
    $this->Projectname= !empty($data['Projectname']) ? $data['Projectname'] : null;
    $this->PShortcut= !empty($data['Shortcut']) ? $data['Shortcut'] : null;
    $this->PComponent_Class= !empty($data['Component_Class']) ? $data['Component_Class'] : null;
}

Now I get an interesting output (I added the content of my dataarray also)

I have two columns which are named the same, that will be shortcut and ci-number, these datafields are mixed up with the same ones from the tableadapter table.

U1 is not the shortcut of galileo it is the shortcut of the unit. The Shortcut of Galileo should be GAL. It seems like the columns which are named the same are filled by the second table (unit) but I won't get any fields from the table unit.

EDIT: to show the addition I made out of the sugestions from jobaer:

I edited my ProjectTable class:

class ProjectTable
{
    //private $tableGateway='t_project';
    private $projectTableGateway;
    private $unitTableGateway;


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

public function __construct(
        TableGatewayInterface $projectTableGateway,
        TableGatewayInterface $unitTableGateway)
{
    $this->projectTableGateway = $projectTableGateway;
    $this->unitTableGateway = $unitTableGateway;
}


public function fetchAll()
{

    $sqlSelect = $this->unitTableGateway->getSql()->select();

    /**
     * columns for the "project_table" exactly it is unit_table
     */
    $sqlSelect->columns(array('CI_Number', 'ProjectID','Unitname','Shortcut','Suppliername'));

    /**
     * this can take two more arguments:
     * an array of columns for "unit_table"
     * and a join type, such as "inner"
     */
$sqlSelect->join('t_project', 't_unit.ProjectID = t_project.ProjectID');    


    /**
     * set condition based on columns
     */
    //$sqlSelect->where(array('unit_table.project_id' => $id));

    $resultSet = $this->unitTableGateway->selectWith($sqlSelect);

    return $resultSet;



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

I also edited my Module.php like suggested, here comes a snippet

    //                      Model\ProjectTable::class => function($container) {
//                          $tableGateway = $container->get(Model\ProjectTableGateway::class);
//                          return new Model\ProjectTable($tableGateway);
//                      },
                        Model\ProjectTable::class => function($container) {
                            $projectTableGateway = $container->get(Model\ProjectTableGateway::class);
                            $unitTableGateway = $container->get(Model\UnitTableGateway::class);
                            return new Model\ProjectTable($projectTableGateway, $unitTableGateway);
                        },

                        Model\ProjectTableGateway::class => function ($container) {
                            $dbAdapter = $container->get(AdapterInterface::class);
                            $resultSetPrototype = new ResultSet();
                            $resultSetPrototype->setArrayObjectPrototype(new Model\Project());
                            return new TableGateway('t_project', $dbAdapter, null, $resultSetPrototype);
                        }

My controller action didn't changed:

return new ViewModel([
                        'projects' => $this->projectTable->fetchAll(),
                            ]);

In my view I tried to grab the columns of both tables:

foreach ($projects as $project) : 
    //  $unit=$units->fetchAllP($project->ProjectID);
var_dump(get_object_vars($project));?>
     <tr>
     <td><?= $this->escapeHtml($project->Unitname) ?></td>
     <td><?= $this->escapeHtml($project->Projectname) ?></td>
     <td><?= $this->escapeHtml($project->Shortcut) ?></td>
     <td><?= $this->escapeHtml($project->CI_Number) ?></td>
     <td><?= $this->escapeHtml($project->Description) ?></td>
        <td><?= $this->escapeHtml($project->Component_Class) ?></td>


        <td>
            <a href="<?= $this->url('project', ['action' => 'edit', 'id' => $project->ProjectID]) ?>">Edit</a>
            <a href="<?= $this->url('project', ['action' => 'delete', 'id' => $project->ProjectID]) ?>">Delete</a>
        </td>

<?php endforeach; ?>

I got an interesting output, so something is still missing. I expected to have al columns out of both joined tables.

EDIT2: to show next version

here is my method fetchAll()/class ProjectTable

public function fetchAll()
    {

        $sqlSelect = $this->unitTableGateway->getSql()->select();
        $sqlSelect->columns(array('UnitID','CI_Number', 'ProjectID','Unitname','Shortcut','Suppliername'));
        $sqlSelect->join('t_project', 't_unit.ProjectID = t_project.ProjectID', array('Project' =>'Projectname','CI' =>'CI_Number','PDescription' =>'Description','PShortcut' =>'Shortcut','PComponent' =>'Component_Class','PProjectID' =>'ProjectID'));
        //$sqlSelect->where(array('unit_table.project_id' => $id));
        $resultSet = $this->unitTableGateway->selectWith($sqlSelect);

        //return $resultSet;
        return $resultSet->toArray();

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

Here is my viewscript:

<?php 
//var_dump(get_object_vars($projects));
foreach ($projects as $project) : 
//var_dump(get_object_vars($project));

?>
    <tr>
    <td><?= $project['Project']?></td>
    <td><?= $project['CI']?></td>
    <td><?= $project['Unitname']?></td>
  <?php     
 endforeach; ?>

}

and here a new screenshot:

EDIT3: adding Unit Stuff

class UnitTable
{
    private $tableGateway='t_unit';

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

    public function fetchAll()
    {
        return $this->tableGateway->select();
    }

class Unit also:

class Unit implements InputFilterAwareInterface
{
    public $UnitID;
    public $CI_Number;
    public $ProjectID;
    public $Unitname;
    public $Shortcut;
    public $Suppliername;

    private $inputFilter;

    public function exchangeArray(array $data)
    {
        $this->UnitID= !empty($data['UnitID']) ? $data['UnitID'] : null;
        $this->CI_Number= !empty($data['CI_Number']) ? $data['CI_Number'] : null;
        $this->ProjectID= !empty($data['ProjectID']) ? $data['ProjectID'] : null;
        $this->Unitname= !empty($data['Unitname']) ? $data['Unitname'] : null;
        $this->Shortcut= !empty($data['Shortcut']) ? $data['Shortcut'] : null;
        $this->Suppliername= !empty($data['Suppliername']) ? $data['Suppliername'] : null;
    }

Bcause I only have sampledata yet, a screenshot of my two tables unit and project

EDIT4: Factorypart of module.php

public function getServiceConfig()
    {
        return [
                'factories' => [
                        Model\ImportTable::class => function($container) {
                            $tableGateway = $container->get(Model\ImportTableGateway::class);
                            return new Model\ImportTable($tableGateway);
                        },
                        Model\ImportTableGateway::class => function ($container) {
                            $dbAdapter = $container->get(AdapterInterface::class);
                            $resultSetPrototype = new ResultSet();
                            $resultSetPrototype->setArrayObjectPrototype(new Model\Import());
                            return new TableGateway('t_dcl', $dbAdapter, null, $resultSetPrototype);
                        },
                        Model\DclimportTable::class => function($container) {
                            $tableGateway = $container->get(Model\DclimportTableGateway::class);
                            return new Model\DclimportTable($tableGateway);
                        },
                        Model\DclimportTableGateway::class => function ($container) {
                            $dbAdapter = $container->get(AdapterInterface::class);
                            $resultSetPrototype = new ResultSet();
                            $resultSetPrototype->setArrayObjectPrototype(new Model\Dclimport());
                            return new TableGateway('t_dcl_import', $dbAdapter, null, $resultSetPrototype);
                        },
                        Model\FollowupTable::class => function($container) {
                            $tableGateway = $container->get(Model\FollowupTableGateway::class);
                            return new Model\FollowupTable($tableGateway);
                        },
                        Model\FollowupTableGateway::class => function ($container) {
                            $dbAdapter = $container->get(AdapterInterface::class);
                            $resultSetPrototype = new ResultSet();
                            $resultSetPrototype->setArrayObjectPrototype(new Model\Followup());
                            return new TableGateway('t_dcl_wv', $dbAdapter, null, $resultSetPrototype);
                        },
                        Model\UnitTable::class => function($container) {
                            $tableGateway = $container->get(Model\UnitTableGateway::class);
                            return new Model\UnitTable($tableGateway);
                        },
                        Model\UnitTableGateway::class => function ($container) {
                            $dbAdapter = $container->get(AdapterInterface::class);
                            $resultSetPrototype = new ResultSet();
                            $resultSetPrototype->setArrayObjectPrototype(new Model\Unit());
                            return new TableGateway('t_unit', $dbAdapter, null, $resultSetPrototype);
                        },
//                      Model\ProjectTable::class => function($container) {
//                          $tableGateway = $container->get(Model\ProjectTableGateway::class);
//                          return new Model\ProjectTable($tableGateway);
//                      },

                        Model\ProjectTableGateway::class => function ($container) {
                            $dbAdapter = $container->get(AdapterInterface::class);
                            $resultSetPrototype = new ResultSet();
                            $resultSetPrototype->setArrayObjectPrototype(new Model\Project());
                            return new TableGateway('t_project', $dbAdapter, null, $resultSetPrototype);
                        },
                        Model\ProjectTable::class => function($container) {
                            $projectTableGateway = $container->get(Model\ProjectTableGateway::class);
                            $unitTableGateway = $container->get(Model\UnitTableGateway::class);

                            return new Model\ProjectTable($projectTableGateway, $unitTableGateway);
                        }
                        ],
                        ];
    }

标签： zend-db zend-framework3

2条回答

来，给爷笑一个

2楼-- · 2019-03-04 10:51

This is very simple if you know how to handle two tables within a model. Assuming you have ProjectTable and UnitTable models and two TableGateway services. Those will handle two tables respectively in the database. So if you want to join them in your ProjectTable model that would then be

ProjectTable.php

class ProjectTable
{
    private $projectTableGateway;
    private $unitTableGateway;

    public function __construct(
        TableGatewayInterface $projectTableGateway, 
        TableGatewayInterface $unitTableGateway)
    {
        $this->projectTableGateway = $projectTableGateway;
        $this->unitTableGateway = $unitTableGateway;
    }

    public function projectUnit($id)
    {

        /** 
         * as you are joing with "project_table"
         * this will handle "unit_table" 
         */ 
        $sqlSelect = $this->unitTableGateway->getSql()->select();

        /**
         * columns for the "unit_table".
         * if want to use aliases use as 
         * array('alias_name' => 'column_name')
         */
        $sqlSelect->columns(array('column_one', 'column_two'));

        /**
         * this can take two more arguments: 
         * an array of columns for "project_table"
         * and a join type, such as "inner"
         */
        $sqlSelect->join('project_table', 'unit_table.project_id = project_table.id');

        /**
         * set condition based on columns
         */
        $sqlSelect->where(array('unit_table.project_id' => $id));

        $resultSet = $this->unitTableGateway->selectWith($sqlSelect);

        return $resultSet; 
    }
}

Now create two TableGateway services for handling two tables and pass them to the ProjectTable's constructor as the following

Model\ProjectTable::class => function($container) {
    $projectTableGateway = $container->get(Model\ProjectTableGateway::class);          
    $unitTableGateway = $container->get(Model\UnitTableGateway::class);

    return new Model\ProjectTable($projectTableGateway, $unitTableGateway);          
}

0人赞添加讨论(0) 举报

虎瘦雄心在

3楼-- · 2019-03-04 11:02

I think you are missing the point. You don't access tables that manipulate table gateways. What you ought to be doing is using table gateways, so that you don't have to deal with tables and SQL anymore. Hence the name of the pattern Table Gateway.

Look at how ZF manual describes this.

After you've done this, it is easy to join two tables behind single method of a table gateway. This method returns a model that is completely removed from the notion of a database.

0人赞添加讨论(0) 举报

how to join tables using tablegateway

采纳回答

编辑标签

举报内容

检举类型

检举原因

检举说明(必填)

打开微信“扫一扫”，打开网页后点击屏幕右上角分享按钮

付费偷看金额在0.1-10元之间