如何使用tablegateway联接表 [英] how to join tables using tablegateway

问题描述

使用tableadapter时如何在zend3中联接表?问题不在于一般如何联接表，而在于如何在zend中执行此操作以及将代码放置在何处.

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.

假设我有一个* table类，例如:

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.

我的问题是，如果我需要联接两个表，那么如何构建该表类.这些表将是项目1-> n单位(关键projectID).第二个问题是如何正确使用别名，因为两个表中都有一些具有不同数据的字段名，例如每个表都有一个列快捷方式.

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.

新信息 为了查看数据的来源，我重命名了Exchangearray的变量:

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)

我有两列名称相同，分别是快捷方式和ci-number，这些数据字段与tableadapter表中的相同字段混合在一起.

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不是galileo的快捷键，而是单位的快捷键.伽利略的快捷方式应为GAL.看来名称相同的列已由第二个表(单元)填充，但我不会从表单元中获得任何字段.

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.

以显示我根据Jobaer的建议所做的添加:

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

我编辑了ProjectTable类:

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

我也按照建议编辑了我的Module.php，这是一个片段

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

我得到了一个有趣的输出，因此仍然缺少某些内容.我希望两个连接表中都没有al列.

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

显示下一个版本

这是我的方法fetchAll()/ProjectTable类

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

这是我的观点:

<?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:

添加单位资料

class UnitTable
{
    private $tableGateway='t_unit';

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

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

班级单位:

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

因为我还只有sampledata，这是我的两个表单元和项目的屏幕截图

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

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

推荐答案

如果您知道如何处理模型中的两个表，这将非常简单.假设您有ProjectTable和UnitTable模型以及两个TableGateway服务.这些将分别处理数据库中的两个表.因此，如果您想将它们加入您的ProjectTable模型，那就可以了

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

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

现在创建两个用于处理两个表的TableGateway服务，并将它们传递给ProjectTable的构造函数，如下所示:

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

这篇关于如何使用tablegateway联接表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！