Magento Forums
Help
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

2 tables in one grid problem

‎03-15-2016 06:59 AM
‎03-15-2016 06:59 AM

2 tables in one grid problem

Hi, All,

I want to add the city information in address. I implemented as following:

1. Created table as following code:
class InstallSchema implements InstallSchemaInterface
{
public function install(SchemaSetupInterface $setup, ModuleContextInterface $context)
{
$installer = $setup;
$installer->startSetup();
if(!$installer->tableExists('directory_region_city')){
$table = $installer->getConnection()->newTable($installer->getTable('directory_region_city'))
->addColumn('city_id', Table::TYPE_INTEGER, null, ['unsigned' => true, 'nullable' => false, 'primary' => true], 'city id')
->addColumn('region_id', Table::TYPE_INTEGER, null, ['unsigned' => true, 'nullable' => false], 'region id')
->addColumn('code', Table::TYPE_INTEGER, null, ['unsigned' => true, 'nullable' => false], 'code')
->addColumn('default_name', Table::TYPE_INTEGER, null, ['unsigned' => true, 'nullable' => false], 'default name')
->setComment('region city table');

$installer->getConnection()->createTable($table);
}

if(!$installer->tableExists('directory_region_city_name')){
$table = $installer->getConnection()->newTable($installer->getTable('directory_city_name'))
->addColumn('locale', Table::TYPE_TEXT, 10, ['nullable' => false, 'primary' => true], 'locale')
->addColumn('city_id', Table::TYPE_INTEGER, null, ['unsigned' => true, 'nullable' => false, 'primary' => true], 'city id')
->addColumn('name', Table::TYPE_TEXT, 255, ['nullable' => false], 'default name')
->setComment('city name table');

$installer->getConnection()->createTable($table);
}

$installer->endSetup();
}
}

2. Added dataProvider in di.xml:
<?xml version="1.0"?>
<config xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="urn:magento:frameworkSmiley SurprisedbjectManager/etc/config.xsd">
<virtualType name="BlogGirdFilterPool" type="Magento\Framework\View\Element\UiComponent\DataProvider\FilterPool">
<arguments>
<argument name="appliers" xsi:type="array">
<item name="regular" xsi:type="object">Magento\Framework\View\Element\UiComponent\DataProvider\RegularFilter</item>
<item name="fulltext" xsi:type="object">Magento\Framework\View\Element\UiComponent\DataProvider\FulltextFilter</item>
</argument>
</arguments>
</virtualType>
<virtualType name="PostGridDataProvider" type="Magento\Framework\View\Element\UiComponent\DataProvider\DataProvider">
<arguments>
<argument name="collection" xsi:type="object" shared="false">AiMai\ChinaRegion\Model\Resource\Region\Collection</argument>
<argument name="filterPool" xsi:type="object" shared="false">BlogGirdFilterPool</argument>
</arguments>
</virtualType>
<virtualType name="AiMai\ChinaRegion\Model\ResourceModel\Region\Grid\Collection" type="Magento\Framework\View\Element\UiComponent\DataProvider\SearchResult">
<arguments>
<argument name="mainTable" xsi:type="string">directory_country_region</argument>
<argument name="resourceModel" xsi:type="string">Magento\Directory\Model\ResourceModel\Region</argument>
</arguments>
</virtualType>
<type name="Magento\Framework\View\Element\UiComponent\DataProvider\CollectionFactory">
<arguments>
<argument name="collections" xsi:type="array">
<item name="region_listing_data_source" xsi:type="string">AiMai\ChinaRegion\Model\ResourceModel\Region\Grid\Collection</item>
</argument>
</arguments>
</type>
</config>

3. Added layout xml as following:
<?xml version="1.0"?>

<page xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="../../../../../../../lib/internal/Magento/ Framework/View/Layout/etc/page_configuration.xsd">
<update handle="styles"/>
<body>
<referenceContainer name="content">
<uiComponent name="chinaregion_region_list"/>
</referenceContainer>
</body>
</page>

4. Added chinaregion_region_list.xml
<?xml version="1.0" encoding="UTF-8"?>
<listing xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="urn:magento:module:Magento_Ui:etc/ui_configuration.xsd">
<argument name="data" xsi:type="array">
<item name="js_config" xsi:type="array">
<item name="provider" xsi:type="string">chinaregion_region_list.region_listing_data_source</item>
<item name="deps" xsi:type="string">chinaregion_region_list.region_listing_data_source</item>
</item>
<item name="spinner" xsi:type="string">region_list_columns</item>
<item name="buttons" xsi:type="array">
<item name="add_new_region" xsi:type="array">
<item name="name" xsi:type="string">add_new_region</item>
<item name="label" xsi:type="string" translate="true">Add New Region</item>
<item name="class" xsi:type="string">primary</item>
<item name="url" xsi:type="string">*/*/newregion</item>
</item>
<item name="add_new_city" xsi:type="array">
<item name="name" xsi:type="string">add_new_city</item>
<item name="label" xsi:type="string" translate="true">Add New City</item>
<item name="class" xsi:type="string">primary</item>
<item name="url" xsi:type="string">*/*/newcity</item>
</item>
<item name="add_new_district" xsi:type="array">
<item name="name" xsi:type="string">add_new_district</item>
<item name="label" xsi:type="string" translate="true">Add New District</item>
<item name="class" xsi:type="string">primary</item>
<item name="url" xsi:type="string">*/*/newdistrict</item>
</item>
</item>
</argument>
<dataSource name="region_listing_data_source">
<argument name="dataProvider" xsi:type="configurableObject">
<argument name="class" xsi:type="string">PostGridDataProvider</argument>
<argument name="name" xsi:type="string">region_listing_data_source</argument>
<argument name="primaryFieldName" xsi:type="string">region_id</argument>
<argument name="requestFieldName" xsi:type="string">id</argument>
<argument name="data" xsi:type="array">
<item name="config" xsi:type="array">
<item name="update_url" xsi:type="url" path="mui/index/render"/>
</item>
</argument>
</argument>
<argument name="data" xsi:type="array">
<item name="js_config" xsi:type="array">
<item name="component" xsi:type="string">Magento_Ui/js/grid/provider</item>
</item>
</argument>
</dataSource>
<container name="listing_top">
<argument name="data" xsi:type="array">
<item name="config" xsi:type="array">
<item name="template" xsi:type="string">ui/grid/toolbar</item>
</item>
</argument>
<bookmark name="bookmarks">
<argument name="data" xsi:type="array">
<item name="config" xsi:type="array">
<item name="component" xsi:type="string">Magento_Ui/js/grid/controls/bookmarks/bookmarks</item>
<item name="displayArea" xsi:type="string">dataGridActions</item>
<item name="storageConfig" xsi:type="array">
<item name="saveUrl" xsi:type="url" path="mui/bookmark/save"/>
<item name="deleteUrl" xsi:type="url" path="mui/bookmark/delete"/>
<item name="namespace" xsi:type="string">chinaregion_region_listing</item>
</item>
</item>
</argument>
</bookmark>
<container name="columns_controls">
<argument name="data" xsi:type="array">
<item name="config" xsi:type="array">
<item name="columnsData" xsi:type="array">
<item name="provider" xsi:type="string">chinaregion_region_list.chinaregion_region_list.region_list_columns</item>
</item>
<item name="component" xsi:type="string">Magento_Ui/js/grid/controls/columns</item>
<item name="displayArea" xsi:type="string">dataGridActions</item>
</item>
</argument>
</container>
<filterSearch name="fulltext">
<argument name="data" xsi:type="array">
<item name="config" xsi:type="array">
<item name="component" xsi:type="string">Magento_Ui/js/grid/search/search</item>
<item name="displayArea" xsi:type="string">dataGridFilters</item>
<item name="provider" xsi:type="string">chinaregion_region_list.region_listing_data_source</item>
<item name="chipsProvider" xsi:type="string">chinaregion_region_list.chinaregion_region_list.listing_top.listing_filters_chips</item>
<item name="storageConfig" xsi:type="array">
<item name="provider" xsi:type="string">chinaregion_region_list.chinaregion_region_list.listing_top.bookmarks</item>
<item name="namespace" xsi:type="string">current.search</item>
</item>
</item>
</argument>
</filterSearch>
<filters name="listing_filters">
<argument name="data" xsi:type="array">
<item name="config" xsi:type="array">
<item name="columnsProvider" xsi:type="string">chinaregion_region_list.chinaregion_region_list.region_list_columns</item>
<item name="displayArea" xsi:type="string">dataGridFilters</item>
<item name="dataScope" xsi:type="string">filters</item>
<item name="storageConfig" xsi:type="array">
<item name="provider" xsi:type="string">chinaregion_region_list.chinaregion_region_list.listing_top.bookmarks</item>
<item name="namespace" xsi:type="string">current.filters</item>
</item>
<item name="childDefaults" xsi:type="array">
<item name="provider" xsi:type="string">chinaregion_region_list.chinaregion_region_list.listing_top.listing_filters</item>
<item name="imports" xsi:type="array">
<item name="visible" xsi:type="string">chinaregion_region_list.chinaregion_region_list.listing_top.bookmarks:current.columns.${ $.index }.visible</item>
</item>
</item>
</item>
</argument>
</filters>
<massaction name="listing_massaction">
<argument name="data" xsi:type="array">
<item name="config" xsi:type="array">
<item name="selectProvider" xsi:type="string">chinaregion_region_list.chinaregion_region_list.region_list_columns.ids</item>
<item name="indexField" xsi:type="string">region_id</item>
</item>
</argument>
<action name="delete">
<argument name="data" xsi:type="array">
<item name="config" xsi:type="array">
<item name="type" xsi:type="string">delete</item>
<item name="label" xsi:type="string" translate="true">Delete</item>
<item name="url" xsi:type="url" path="blog/post/massDelete"/>
<item name="confirm" xsi:type="array">
<item name="title" xsi:type="string" translate="true">Delete items</item>
<item name="message" xsi:type="string" translate="true">Are you sure you wan't to delete selected items?</item>
</item>
</item>
</argument>
</action>
</massaction>
<paging name="listing_paging">
<argument name="data" xsi:type="array">
<item name="config" xsi:type="array">
<item name="storageConfig" xsi:type="array">
<item name="provider" xsi:type="string">chinaregion_region_list.chinaregion_region_list.listing_top.bookmarks</item>
<item name="namespace" xsi:type="string">current.paging</item>
</item>
<item name="selectProvider" xsi:type="string">chinaregion_region_list.chinaregion_region_list.region_list_columns.ids</item>
<item name="displayArea" xsi:type="string">bottom</item>
<item name="options" xsi:type="array">
<item name="20" xsi:type="array">
<item name="value" xsi:type="number">20</item>
<item name="label" xsi:type="string" translate="true">20</item>
</item>
<item name="30" xsi:type="array">
<item name="value" xsi:type="number">30</item>
<item name="label" xsi:type="string" translate="true">30</item>
</item>
<item name="50" xsi:type="array">
<item name="value" xsi:type="number">50</item>
<item name="label" xsi:type="string" translate="true">50</item>
</item>
<item name="100" xsi:type="array">
<item name="value" xsi:type="number">100</item>
<item name="label" xsi:type="string" translate="true">100</item>
</item>
<item name="200" xsi:type="array">
<item name="value" xsi:type="number">200</item>
<item name="label" xsi:type="string" translate="true">200</item>
</item>
</item>
</item>
</argument>
</paging>
</container>
<columns name="region_list_columns">
<argument name="data" xsi:type="array">
<item name="config" xsi:type="array">
<item name="storageConfig" xsi:type="array">
<item name="provider" xsi:type="string">chinaregion_region_list.chinaregion_region_list.listing_top.bookmarks</item>
<item name="namespace" xsi:type="string">current</item>
</item>
<item name="childDefaults" xsi:type="array">
<item name="fieldAction" xsi:type="array">
<item name="provider" xsi:type="string">chinaregion_region_list.chinaregion_region_list.region_list_columns.actions</item>
<item name="target" xsi:type="string">applyAction</item>
<item name="params" xsi:type="array">
<item name="0" xsi:type="string">edit</item>
<item name="1" xsi:type="string">${ $.$data.rowIndex }</item>
</item>
</item>
<item name="controlVisibility" xsi:type="boolean">true</item>
<item name="storageConfig" xsi:type="array">
<item name="provider" xsi:type="string">chinaregion_region_list.chinaregion_region_list.listing_top.bookmarks</item>
<item name="root" xsi:type="string">columns.${ $.index }</item>
<item name="namespace" xsi:type="string">current.${ $.storageConfig.root}</item>
</item>
</item>
</item>
</argument>

<column name="region">
<argument name="data" xsi:type="array">
<item name="config" xsi:type="array">
<item name="filter" xsi:type="string">text</item>
<item name="sorting" xsi:type="string">asc</item>
<item name="label" xsi:type="string" translate="true">Province</item>
</item>
</argument>
</column>
<column name="city">
<argument name="data" xsi:type="array">
<item name="config" xsi:type="array">
<item name="filter" xsi:type="string">text</item>
<item name="sorting" xsi:type="string">asc</item>
<item name="label" xsi:type="string" translate="true">City</item>
</item>
</argument>
</column>
</columns>
</listing>

5. Added Collection.php at Model/ModelResource/Region
class Collection extends \Magento\Directory\Model\ResourceModel\Region\Collection
{
protected $_cityTable;
protected $_cityNameTable;
protected function _construct()
{
parent::_construct();

$this->_cityTable = $this->getTable('directory_region_city');
$this->_cityNameTable = $this->getTable('directory_region_city_name');
}

protected function _initSelect()
{
parent::_initSelect();

$this->getSelect()->joinLeft(
['city_name' => $this->_cityNameTable, 'city' => $this->_cityTable],
'directory_country_region.region_id = city.region_id AND city_name.locale = :region_locale',
['name']);

return $this;
}
}

 

Labels:
0 Kudos
2 REPLIES 2
‎03-15-2016 07:18 AM
‎03-15-2016 07:18 AM

Re: 2 tables in one grid problem

namespace AiMai\ChinaRegion\Model; class City extends \Magento\Framework\Model\AbstractModel { protected function _construct() { $this->_init('Magento\Directory\Model\ResourceModel\City'); } public function getName() { $name = $this->getData('name'); if ($name === null) { $name = $this->getData('default_name'); } return $name; } public function loadByCode($code, $regionId) { if ($code) { $this->_getResource()->loadByCode($this, $code, $regionId); } return $this; } public function loadByName($name, $regionId) { $this->_getResource()->loadByName($this, $name, $regionId); return $this; } } I hope it could show the province and city columns at the backend It trigger and exception: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'city' in 'order clause', query was: SELECT main_table.* FROM directory_country_region AS main_table ORDER BY city ASC LIMIT 20 I guess the content in tag in file chinaregion_region_list.xml is not correct.
0 Kudos
‎03-15-2016 07:52 AM
‎03-15-2016 07:52 AM

Re: 2 tables in one grid problem

6. Added City.php at Model/ModelResource class City extends \Magento\Framework\Model\ResourceModel\Db\AbstractDb { /** * Table with localized region names * * @var string */ protected $_cityNameTable; protected $_localeResolver; public function __construct( \Magento\Framework\Model\ResourceModel\Db\Context $context, \Magento\Framework\Locale\ResolverInterface $localeResolver, $connectionName = null ) { parent::__construct($context, $connectionName); $this->_localeResolver = $localeResolver; } protected function _construct() { $this->_init('directory_region_city', 'region_id'); $this->_cityNameTable = $this->getTable('directory_city_name'); } protected function _getLoadSelect($field, $value, $object) { $select = parent::_getLoadSelect($field, $value, $object); $connection = $this->getConnection(); $locale = $this->_localeResolver->getLocale(); $systemLocale = \Magento\Framework\AppInterface:Smiley Very HappyISTRO_LOCALE_CODE; $cityField = $connection->quoteIdentifier($this->getMainTable() . '.' . $this->getIdFieldName()); $condition = $connection->quoteInto('lrn.locale = ?', $locale); $select->joinLeft( ['lrn' => $this->_cityNameTable], "{$cityField} = lrn.region_id AND {$condition}", [] ); if ($locale != $systemLocale) { $nameExpr = $connection->getCheckSql('lrn.region_id is null', 'srn.name', 'lrn.name'); $condition = $connection->quoteInto('srn.locale = ?', $systemLocale); $select->joinLeft( ['srn' => $this->_cityNameTable], "{$cityField} = srn.region_id AND {$condition}", ['name' => $nameExpr] ); } else { $select->columns(['name'], 'lrn'); } return $select; } protected function _loadByRegion($object, $regionId, $value, $field) { $connection = $this->getConnection(); $locale = $this->_localeResolver->getLocale(); $joinCondition = $connection->quoteInto('rname.city_id = city.city_id AND rname.locale = ?', $locale); $select = $connection->select()->from( ['city' => $this->getMainTable()] )->joinLeft( ['rname' => $this->_regionNameTable], $joinCondition, ['name'] )->where( 'city.region_id = ?', $regionId )->where( "city.{$field} = ?", $value ); $data = $connection->fetchRow($select); if ($data) { $object->setData($data); } $this->_afterLoad($object); return $this; } public function loadByCode(\AiMai\ChinaRegion\Model\City $city, $cityCode, $regionId) { return $this->_loadByRegion($city, $regionId, (string)$cityCode, 'code'); } public function loadByName(\AiMai\ChinaRegion\Model\City $city, $cityName, $regionId) { return $this->_loadByRegion($city, $regionId, (string)$cityName, 'default_name'); } } 7. Added City.php at Model/
0 Kudos