Visual query builder is not displaying properly

Anjali commented on Dec 16, 2021

Hello, I have added same as example of visual query builder but UI doesn't look good. Please let me know what i am missing.

Code:

<?php

use \koolreport\visualQuery\VisualQuery;
use \koolreport\datagrid\DataTables;
?>
<!DOCTYPE html>
<html lang="fr">

<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Présentation</title>
</head>

<body>
    <form method="post">
        <div class="report-content">
            <div class="text-center">
                <h1>VisualQuery</h1>
                <p class="lead">
                    The basic working of VisualQuery
                </p>
            </div>


            <?php

            \koolreport\visualquery\VisualQuery::create(array(
                "name" => "visualquery1",
                "themeBase" => "bs4",
                "schema" => "salesSchema",
                "defaultValue" => [
                    "selectDistinct" => false,
                    "selectTables" => [
                        "orders",
                        "customers",
                    ],
                    "selectFields" => [
                        "orders.orderNumber",
                    ],
                    "filters" => [
                        "(",
                        [
                            "field" => "orders.orderDay",
                            "operator" => ">",
                            "value1" => "2001-01-01",
                            "value2" => "",
                            "logic" => "and",
                            "toggle" => true,
                        ],
                        ")",
                    ],
                    "groups" => [
                        [
                            "field" => "orders.orderNumber",
                            "toggle" => true
                        ]
                    ],
                    "limit" => [
                        "offset" => 5,
                        "limit" => 10,
                        "toggle" => false,
                    ]
                ],
                "activeTab" => "filters",
            ));
            ?>

            <button type='submit' class='btn btn-light'>Submit</button>

            <style>
            pre {
                overflow-x: auto;
                white-space: pre-wrap;
                white-space: -moz-pre-wrap;
                white-space: -pre-wrap;
                white-space: -o-pre-wrap;
                word-wrap: break-word;
            }
            </style>
            <div style="margin: 30px; width:800px">
                <b>Select query:</b>
                <pre style="width:800px"><?php echo $this->queryStr; ?></pre>
                <b>Query with parameters:</b>
                <pre style="width:800px"><?php echo $this->paramQuery; ?></pre>
                <b>Parameters:</b>
                <?php \koolreport\core\Utility::prettyPrint($this->sqlParams); ?>
            </div>

            <?php
            // print_r($this->dataStore('vqDS')->meta());
            DataTables::create(array(
                "name" => "charttable1",
                "dataSource" => $this->dataStore('vqDS'),
                // "columns" => ["Quantity", "Product Name"],
                "options" => [
                    "paging" => true
                ]
            ));
            ?>
        </div>
    </form>
</body>

</html>

Sebastian Morales commented on Dec 17, 2021

The most likely case is your defined schema is not totally correct, which causes error when rendering VisualQuery widget. Pls post your defined schemas in your report file. Rgds,

Anjali commented on Dec 17, 2021

pls check defined schema.

<?php

namespace App\visualquerysample;

class vquery extends \koolreport\KoolReport
{
    use \koolreport\visualquery\Bindable;
    use \koolreport\laravel\Friendship;

    public function defineSchemas()
    {
        return [
            "salesSchema" => array(
                "tables" => [
                    "customers" => array(
                        "{meta}" => [
                            "alias" => "Table Customers"
                        ],
                        "customerNumber" => array(
                            "alias" => "Customer Number",
                        ),
                        "customerName" => array(
                            "alias" => "Customer Name",
                        ),
                    ),
                    "orders" => array(
                        "{meta}" => [
                            "alias" => "Table Orders"
                        ],
                        "orderNumber" => array(
                            "alias" => "Order Number"
                        ),
                        "orderDay" => array(
                            "alias" => "Order Day",
                            "expression" => "date(orderDate)",
                            "type" => "date",
                        ),
                        "orderDate" => array(
                            "alias" => "Order Date",
                            "type" => "datetime"
                        ),
                        "orderMonth" => [
                            "expression" => "month(orderDate)",
                        ]
                        // "customerNumber"=>array(
                        //    "alias"=>"Customer Number"
                        // )
                    ),
                ],
                "relations" => [
                    ["orders.customerNumber", "leftjoin", "customers.customerNumber"],
                ]
            ),
            "separator" => ".",
        ];
    }

    protected function setup()
    {
        // echo "post=";
        // \koolreport\core\Utility::prettyPrint($_POST);

        $params = \koolreport\core\Utility::get($this->queryParams, 'visualquery1');

        $qb = $this->paramsToQueryBuilder($params);
        $this->queryStr = $params ? $qb->toMySQL() : "select * from customers where 1=0";
        $this->paramQuery = $params ? $qb->toMySQL(['useSQLParams' => "name"]) : "select * from customers where 1=0";
        $this->sqlParams = $qb->getSQLParams();

        $this
            ->src('mysql')
            ->query($this->paramQuery)
            ->params($this->sqlParams)
            ->pipe(new \koolreport\processes\ColumnMeta([
                "Order Number" => [
                    "type" => "string"
                ],
                "orderMonth" => [
                    "type" => "string"
                ],
            ]))
            ->pipe($this->dataStore('vqDS'));
    }
}

Anjali commented on Dec 17, 2021

Thanks issue is resolved

KoolReport's Forum

Visual query builder is not displaying properly #2499

Build Your Excellent Data Report