How to convert queryParams to sql raw query?

Anjali commented on Jan 1, 2022

Hi, I am converting queryParams to queryStr but it says no table sql data found or array to string conversion. Please guide me how to get raw queries from paramquery arrays i have been getting.

 protected function setup()
    {
        if (isset($this->params['query_params'])) {
            $this->queryParams = ($this->params['query_params']);
        }
        $this->id = $this->params['id'] ?? '';
        $this->name = $this->params['name'] ?? 'Visual';
        $this->description = $this->params['description'] ?? '';
        $params = \koolreport\core\Utility::get($this->queryParams, $this->name);

        $qb = $this->paramsToQueryBuilder($params);
         $this->queryStr = $params ? $qb->toMySQL() : "select * from users where 1=0";
        $this->paramQuery = $params ? $qb->toMySQL(['useSQLParams' => "name"]) : "select * from users ";
        $this->sqlParams = $qb->getSQLParams();
        $this
            ->src('mysql')
            ->query($this->paramQuery)
            ->query($this->queryStr)
            ->params($this->sqlParams)
            ->pipe($this->dataStore('vqDS'));
    }

Please guide me.

  \koolreport\visualquery\VisualQuery::create(array(
                    "name" => $this->name,
                    "themeBase" => "bs4",
                    "schema" => "salesSchema",
                    "defaultValue" => [
                        "selectDistinct" => false,
                        "selectTables" => [
                            "users",
                        ],
                        "filters" => [],
                        "limit" => [
                            "offset" => 5,
                            "limit" => 10,
                            "toggle" => false,
                        ]
                    ],
                    "queryParams" => $this->queryParams,
                    "queryStr" => $this->queryStr,
                ));
                ?>

Also I am not able to get this queryParams and queryStr in my controller. I am using in Laravel.

Sebastian Morales commented on Jan 4, 2022

Pls copy and post the exact error message and stack traces so we no which method caused the error. Tks,

Anjali commented on Jan 4, 2022

Hello, I am getting this error while adding new table from 'Add Table' button.

Sebastian Morales commented on Jan 4, 2022

Pls post your defined schemas. There could have been a problem with its structure that the VisualQuery widget could not be built. Rgds,

Anjali commented on Jan 4, 2022

Sure.

 public function defineSchemas()
    {
        return [
            "salesSchema" => array(
                "tables" => [
                    "users" => array(
                        "{meta}" => [
                            "alias" => "Table Users"
                        ],
                        "name" => array(
                            "alias" => "User Name",
                        ),
                        "email" => array(
                            "alias" => "User Email",
                        ),
                        "procure_id" => array(
                            "alias" => "Procure Id",
                        ),
                    ),
                    "procuring_entities" => array(
                        "{meta}" => [
                            "alias" => "Table Procurement Entities"
                        ],
                        "id" => array(
                            "alias" => "Procure ID"
                        ),
                        "name" => array(
                            "alias" => 'Name'
                        ),
                        "phone_number" => array(
                            "alias" => "Phone Number",
                        ),
                    ),
                    "suppliers" => array(
                        "{meta}" => [
                            "alias" => "Table Suppliers"
                        ],
                        "id" => array(
                            "alias" => "Supplier ID"
                        ),
                        "name" => array(
                            "alias" => 'Name'
                        ),
                        "type" => array(
                            "alias" => "Supplier Type",
                        ),
                    ),
                ],
            ),
            "separator" => ".",
        ];
    }

Sebastian Morales commented on Jan 4, 2022

Pls try to add "selectFields" => array() to your VisualQuery widget create:

\koolreport\visualquery\VisualQuery::create(array(
    ...
    "selectFields" => array()
));

Anjali commented on Jan 4, 2022

I have added but still showing same error.

 \koolreport\visualquery\VisualQuery::create(array(
                    "name" => $this->name,
                    "themeBase" => "bs4",
                    "schema" => "salesSchema",
                    "defaultValue" => [
                        "selectDistinct" => false,
                        "selectTables" => [
                            "users",
                        ],
                        "selectFields" => array(),
                        "filters" => [],
                        "limit" => [
                            "offset" => 5,
                            "limit" => 10,
                            "toggle" => false,
                        ]
                    ],
                    "queryParams" => $this->queryParams,
                    "queryStr" => $this->queryStr,
                ));

Sebastian Morales commented on Jan 4, 2022

Ah, you also need to add join relation between your tables in defined schemas similarly to this:

                "relations" => [
                    ["orders.customerNumber", "leftjoin", "customers.customerNumber"],
                    ["orders.orderNumber", "join", "orderdetails.orderNumber"],
                    ["orderdetails.productCode", "leftjoin", "products.productCode"],
                ],

Pls note that the table and field names are case sensitive.

Anjali commented on Jan 4, 2022

I have added realtion as well still not work, I have found the issue caused due to

data-table="undefined"

table value getting undefined in js file.

PLease find my schemea used:

 public function defineSchemas()
    {
        return [
            "salesSchema" => array(
                "tables" => [
                    "users" => array(
                        "{meta}" => [
                            "alias" => "Table Users"
                        ],
                        "name" => array(
                            "alias" => "User Name",
                        ),
                        "email" => array(
                            "alias" => "User Email",
                        ),
                        "procure_id" => array(
                            "alias" => "Procure Id",
                        ),
                    ),
                    "procuring_entities" => array(
                        "{meta}" => [
                            "alias" => "Table Procurement Entities"
                        ],
                        "id" => array(
                            "alias" => "Procure ID"
                        ),
                        "name" => array(
                            "alias" => 'Name'
                        ),
                        "phone_number" => array(
                            "alias" => "Phone Number",
                        ),
                    ),
                    "suppliers" => array(
                        "{meta}" => [
                            "alias" => "Table Suppliers"
                        ],
                        "id" => array(
                            "alias" => "Supplier ID"
                        ),
                        "name" => array(
                            "alias" => 'Name'
                        ),
                        "type" => array(
                            "alias" => "Supplier Type",
                        ),
                    ),
                    "supplier_supplier_categories" => array(
                        "{meta}" => [
                            "alias" => "Table Category"
                        ],
                        "supplier_id" => array(
                            "alias" => "Supplier ID"
                        ),
                        "supplier_categories_id" => array(
                            "alias" => 'Cat ID'
                        ),
                    ),
                ],
            ),
            "relations" => [
                ["users.procure_id", "leftjoin", "procuring_entities.id"],
            ],
            "separator" => ".",
        ];
    }

Sebastian Morales commented on Jan 4, 2022

You only have one relation so you can at most add 2 tables. No worries about console error after 2 tables, it doesn't affect how the widget works. After choosing tables you have to also select fields so that a correct select sql query can be built upon submitting.

Anjali commented on Jan 4, 2022

But i am not able to add 2 table from UI. ONly one table can be added.

Sebastian Morales commented on Jan 4, 2022

Oh, forgot to tell you that "relations" should be inside "salesSchema" and the same level with "tables", i.e we define tables and relations for each schema.

Anjali commented on Jan 4, 2022

Yes its working now, Thanks a lot for quick response.

KoolReport's Forum

How to convert queryParams to sql raw query? #2515

Build Your Excellent Data Report