如何使用tablegateway加入表格

问题描述：

如何在使用tableadapter时在zend3中加入表格？问题不在于如何在一般情况下连接表，而在于如何在zend中执行此操作，以及在何处放置代码。如何使用tablegateway加入表格

比方说，我HABE例如*表类：

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

我想加入两个表，我怎么能做到这一点，北京时间这里这样做的正确的地方？我试图实现以下功能：

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

}

我没有得到一个错误，我得到了反而混合了数据。之后，我尝试别名也没有工作。

我的问题是，如何构建这个表类，如果我需要连接两个表。这些表格将是项目1 - > n单元（关键项目ID）。第二个问题是如何正确使用别名，因为我在这两个表中都有一些字段名与不同的数据，例如每个表都有一个列快捷方式。

编辑：新的信息要查看的数据从何而来，我重新命名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; 
}

现在我得到一个有趣的输出（我加我DataArray中的内容也）

我有两列被命名为相同的，那将是快捷和CI-数，这些数据域与来自TableAdapter的表一样的人混在一起。

U1不是伽利略的快捷方式，它是单位的捷径。伽利略的捷径应该是GAL。看起来这些名称相同的列由第二个表格（单元）填充，但我不会从表格单元获取任何字段。

编辑：显示我从jobaer做出来的sugestions的加法：

我编辑ProjectTable类：

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样的建议，来了一个片段

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

我的控制器动作并没有改变：

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

在我看来，我想抓两个表的列：

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列。

EDIT2：显示下一版本

这里是我的方法使用fetchall（）/班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();

这里是我的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; ?>

}

，在这里新的截图：

EDIT3：加药装置的东西

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

Bcause我只有的sampleData的是，截图我的两个表单位和项目

EDIT4：Factorypart 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和模型和两个TableGateway服务。那些将分别处理数据库中的两个表。所以，如果你想加入他们在您的ProjectTable模型，然后将

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的构造函数如下

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

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

我是一个更进一步，但它不能正常工作。输出显示了我可以抓住它们的unit_table的列，但它没有找到两个表中名称相同的两个旁边的project_table的列。哪些是快捷方式和CI_Number。那么我怎样才能得到所有的专栏？ –

你的'ProjectTableGateway'没问题，但没有显示你的'UnitTableGateway'。我评论说，在哪里提供每个表的列。但我没有告诉如何使用别名。这里是单元表$ sqlSelect-> columns（array（'alias_name'=>'column_name'，...））;这是用于项目表$ sqlSelect-> join（'t_project'，'t_unit.ProjectID = t_project.ProjectID'，array（'alias_name'=>'column_name'，...））; – unclexo

我试过这个，只有一列，只是为了弄清楚。 $ sqlSelect-> join（'t_project'，'t_unit.ProjectID = t_project.ProjectID'，array（'P'=>'Projectname'））;我得到一个错误注意：未定义的属性：导入\ Model \ Unit :: $ P在第37行的C：\ xampp \ htdocs \ ohb \ module \ Import \ view \ import \ import \ index.phtml和我的vardump do not显示该列。 –

答

的，我认为你是缺少点。您不访问操纵表格网关的表格。你应该做的是使用表网关，这样你就不必再处理表和SQL了。因此模式的名称Table Gateway。

看看如何ZF manual describes this。

完成此操作后，很容易在表格网关的单个方法后面连接两个表格。该方法返回一个完全从数据库概念中删除的模型。

你可以再解释一下还是举个加入的例子？因为我已经阅读了所有我能找到的东西，而且我没有理解。当然，我可以在我的控制器中访问我的适配器，并从这里运行一个sql，但我真的很想了解这个tablegateway概念。 –

thx给你也jobain得到了解决方案 –

如何使用tablegateway加入表格

相关推荐