1

As the title says - I am trying to create an admin grid from an external database-table.

I was able to create one with an ui_component and some dirty tricks in my data provider - but the problem is, that I dont know how to get the values from the filter inputs.

Is there a best-practice on how to do this?

*edit: see my answer

1

Ok, I finally found my mistake(?!) - I can get the applied filters in my data-provider php file by using the SessionManagerInterface:

use Magento\Framework\Session\SessionManagerInterface;
class YourClassname extents AbstractDataProvider
{
  protected $session;
  
    public function __construct(
        $name,
        $primaryFieldName,
        $requestFieldName,
        RequestInterface $request,
        UrlInterface $urlBuilder,
        Collection $collectionFactory,
        FilterBuilder $filterBuilder,
        array $meta = [],
        array $data = [],
        Data $helper,
        SessionManagerInterface $session
    ) {
        parent::__construct($name, $primaryFieldName, $requestFieldName, $meta, $data);
        $this->collection = $collectionFactory->load();
        $this->helper = $helper;
        $this->request = $request;
        $this->filterBuilder = $filterBuilder;
        $this->urlBuilder = $urlBuilder;
        $this->prepareUpdateUrl();
        $this->session=$session;
    }
      public function getData()
    {

        $this->session->start();

        $filters=$this->request->getParam('filters');
        $servername = $this->helper->getConfig('configuration/database/host');
         $username = $this->helper->getConfig('configuration/database/username');
         $password = $this->helper->getConfig('configuration/database/password');
         $dbname = $this->helper->getConfig('configuration/database/name');
         $port = $this->helper->getConfig('configuration/database/port');
         $filters=$this->request->getParam('filters');

         
         try {
             $conn = new \PDO("mysql:host=$servername;dbname=$dbname;port=$port", $username, $password);
             $selection="SELECT * from table";
             foreach ($conn->query($selection) as $dbitem) {
          $item=
          [
           'id' => utf8_encode($dbitem['id']),
              'product_name' =>utf8_encode($dbitem['product_name'])
            ];
        array_push($items,$item);
        array_push($sessionitems,$item['id']);
         }

         $pagesize = intval($this->request->getParam('paging')['pageSize']);
         $pageCurrent = intval($this->request->getParam('paging')['current']);
         $pageoffset = ($pageCurrent - 1)*$pagesize;

         $collection=[
             'totalRecords' => count($items),
             'items' => array_slice($items,$pageoffset ,$pagesize),
         ];
          return $collection;
        } catch(PDOException $e) {
            echo "Connection failed: " . $e->getMessage();
        }
    }

And here is my layout xml file:

<?xml version="1.0"?>
<page xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="urn:magento:framework:View/Layout/etc/page_configuration.xsd">  
    <referenceBlock name="page.title">
        <action method="setPageTitle">
            <argument name="title" xsi:type="string">Import</argument>
        </action>
    </referenceBlock>
    <body>
        <referenceContainer name="content">    
            <block class="Magento\Backend\Block\Template" template="Vendor_Module::yourphtmlfile.phtml"/>  <!-- change this part in your file -->
            <uiComponent name="importgrid"/>
        </referenceContainer>
    </body>
</page>

and the grid-file (listing):

<?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">importgrid_data_source</item>
        </item>
    </argument>

    <settings>
        <spinner>my_custom_columns</spinner>
        <deps>
            <dep>importgrid.importgrid_data_source</dep>
        </deps>
    </settings>

    <dataSource name="importgrid_data_source">
        <argument name="dataProvider" xsi:type="configurableObject">
            <argument name="class" xsi:type="string">Vendor\Module\Ui\DataProvider\YourProvider</argument> <!-- change this to your provider -->
            <argument name="name" xsi:type="string">importgrid_data_source</argument>
            <argument name="primaryFieldName" xsi:type="string">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>
    <listingToolbar name="listing_top">
        <argument name="data" xsi:type="array">
            <item name="config" xsi:type="array">
                <item name="sticky" xsi:type="boolean">true</item>
            </item>
        </argument>
        <massaction name="listing_massaction">

            <action name="Export">
                <settings>
                    <url path="path-to-this-site"/>
                    <type>import</type>
                    <label translate="true">Import products</label>
                </settings>
            </action>
      </massaction>
        <filters name="listing_filters"/>
        <columnsControls name="columns_controls"/>

        <paging name="listing_paging"></paging>
    </listingToolbar>

    <columns name="my_custom_columns">
        <argument name="data" xsi:type="array">
            <item name="config" xsi:type="array">
                <item name="childDefaults" xsi:type="array">
                    <item name="fieldAction" xsi:type="array">
                        <item name="provider" xsi:type="string">importgrid.importgrid.my_custom_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="appendTo" xsi:type="string">importgrid.importgrid.listing_top.columns_controls</item>
                    <item name="storageConfig" xsi:type="array">
                        <item name="provider" xsi:type="string">importgrid.importgrid.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>

        <selectionsColumn name="ids">
            <argument name="data" xsi:type="array">
                <item name="config" xsi:type="array">
                    <item name="indexField" xsi:type="string">id</item>
                    <item name="sortOrder" xsi:type="number">0</item>

                </item>

            </argument>
            <settings>
              <label translate="true">Selection</label>
            </settings>
        </selectionsColumn>

        <column name="id" sortOrder="1">
          <settings>
             <filter>text</filter>
             <dataType>text</dataType>
             <label translate="true">ID</label>
          </settings>
        </column>

        <column name="product_name" sortOrder="5">
          <settings>
             <filter>text</filter>
             <dataType>text</dataType>
             <label translate="true">Name</label>
          </settings>
        </column>

        <!-- add your additional colums here -->

    </columns>
</listing>

If anyone faces same problems, feel free to ask.

13
  • Hey Mario! I was wondering if you could share you di.xml where ever you declare the data provider, and the layout.xml for the listing.xml Thank you! – Brandon Irwin Sep 11 '20 at 18:11
  • Sounds good. I don't think I phrased my question correctly. We are trying to accomplish this same task and wondering what your di, listing looks like to accomplish going the grid to an external DB. Thank you for the time. – Brandon Irwin Sep 11 '20 at 18:25
  • I didnt declare the provider in the di.xml. But I can share the layout file and the listing file of course - see updated answer. – Mario Sep 11 '20 at 18:35
  • Awesome thank you. Another bother, did you do anything special in the Model/ResourceModel/Collection files? Normally they point to the Magento table but in this instance, they are pointing to the external table? – Brandon Irwin Sep 11 '20 at 18:43
  • no, I just created the file: <?php namespace Vendor\Module\Model\ResourceModel\Foldername; \\ <- change it to your actual folder use Magento\Framework\Data\Collection as DataCollection; class Collection extends DataCollection { } ?> Btw. I added some more of my code to the data-provider in my answer, so you can see, how I get my connection and values from the db. – Mario Sep 11 '20 at 18:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.