Implement Query Source Type

[Vinai]

Allow developers to display db table content directly without having to implement intermediate PHP classes.

Roughly I had something like this in mind (EDIT: SEE UPDATED VERSION IN THREAD BELOW)):

<?xml version="1.0"?>
<grid xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
      xsi:noNamespaceSchemaLocation="urn:magento:module:Hyva_Admin:etc/hyva-grid.xsd">
    <source>
        <query>
            <columns>
                <column as="id">entity_id</column>
                <column>sku</column>
                <column as="name">t_name.value</column>
            </columns>
            <from as="mainTable">catalog_product</from>
            <where>
                <and>id IN(:ids)</and>
                <or>sku = :sku</or>
            </where>
            <join>
                <left conditionType="with">
                    <table as="t_name">catalog_product_entity_varchar</table>
                    <condition>t_name.entity_id=mainTable.entity_id AND attribute_id=47</condition>
                </left>
            </join>
            <bind_parameters>
                <param name="ids">productIds</param>
                <param name="sku">sku</param>
            </bind_parameters>
        </query>
    </source>
</grid>

This is a very rough draft and unfinished, all open to change. It is first attempt to come up with a intuitive XML schema to allow defining a query in an extensible manner.

Most of the above should hopefully be understandable, but the bind parameters might need an explanation: The use case for the bind params is to define a grid that is (for example) on a product page, and that then displays some data for that product. There needs to be some way to pass values to the grid from PHP via the bind parameters.

In this example, the grid grid block (?) could say something like

$grid->addSourceBindParams([
    'productIds' => $ids,
    'sku' => $request->getParam('sku')
]);

You might notice this is not completely thought out yet.

[Vinai]

In Hyva_Admin version 1.1.5 the \Hyva\Admin\Model\TypeReflection\DbSelectColumnExtractor was added, which can inspect \Magento\Framework\DB\Select instances to figure out columns and types.

Also, \Hyva\Admin\Model\MethodValueBindings provides all that is needed to pass values from PHP into the query via defaultSearchCriteriaBindings. Here is an example how that could look:

<source>
  <query>...</query>
  <defaultSearchCriteriaBindings>
    <field name="entity_id" condition="in" requestParam="ids">
    <field name="sku" method="Magento\Framework\App\RequestInterface::getParam" param="sku"/>
  </defaultSearchCriteriaBindings>
</source>

These bindings are then added to the query as where conditions. If multiple default conditions are specified, they are combined with an AND operator. So rather than specifying the default bindings in the <query> , they are specified as <defaultSearchCriteriaBindings> just like for any other grid source type.

All that is missing is the code to build the Select instance from the XML.

[Vinai]

Reworked draft for the query source type XML:

<?xml version="1.0"?>
<grid xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
      xsi:noNamespaceSchemaLocation="urn:magento:module:Hyva_Admin:etc/hyva-grid.xsd">
    <source type="query">
        <query unionSelectType="distinct">
           <select>
              <from table="product_entity" as="main_table"/>
              <columns>
                  <column name="entity_id" as="id"/>
                  <column name="sku"/>
                  <expression as="n">COUNT(*)</expression>
              </columns>
              <join type="left" table="catalog_product_entity_varchar" as="t_name">
                  <on>t_name.entity_id=main_table.entity_id AND attribute_id=47</on>
                  <columns>
                    <column name="value" as="name"/>
                  </columns>
              </join>
              <groupBy>
                <column name="main_table.attribute_set_id"/>
              <groupBy>
           </select>
           <unionSelect>
              ... all contents of select allowed ...
           </unionSelect>
        </query>
    </source>
</grid>

Any conditions are specified using the <defaultSearchCriteriaBindings> outside of the <query> spec. The <defaultSearchCriteriaBindings> conditions are applied to the initial select as well as any union select.

If no columns are specified on the main select, * is used. For joined tables it is different: if no columns are specified on a join, no columns are added to the result set. It is possible to specify * as a join column, too.

The default join type if no type attribute is specified is left, as it is probably the most common join type.

[Vinai]

Implement in future if it turns out they are needed:

• sortOrder for groupBy columns to determine the order in which they are applied through XML merging
• select distinct
• having
• subqueries
• disabling of declarations
• orderBy that is always applied before any orders from the searchCriteria are applied

[Vinai]

Feature complete in fb21882, now moving into manual testing and documentation.

[Vinai]

Implemented in eaa9072

Still missing: documentation and the features for future enhancements listed in https://github.com/hyva-themes/magento2-hyva-admin/issues/17#issuecomment-814982844

[Vinai]

First query source type released in 1.1.6. Closing this issue for now. If one of the missing features above needs to be implemented, I'll reopen.