KoolReport's Forum

Official Support Area, Q&As, Discussions, Suggestions and Bug reports.
Forum's Guidelines

Dynamic Loading of Tables in Visual Query #2870

Open sudhakar Pantula opened this topic on on Nov 10, 2022 - 3 comments

sudhakar Pantula commented on Nov 10, 2022

In the example given for Visual Query, the schemas are loaded in defineSchemas function - my question is how can I load this schema definition dynamically instead of hardcoding the tables and their column and providing relationship? I want to see a list available tables - when I select the tables can we auto populate the columns into this myReport.php

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"
                    // )
                ),
                "orderdetails"=>array(
                    "{meta}" => [
                        "alias" => "Order Details"
                    ],
                    // "orderNumber"=>array(
                    //     "alias"=>"Order Number"
                    // ),
                    "quantityOrdered"=>array(
                        "alias"=>"Quantity",
                        "type"=>"number",
                    ),
                    "priceEach"=>array(
                        "alias"=>"Price Each",
                        "type"=>"number",
                        "decimal"=>2,
                        "prefix"=>"$",
                    ),
                    // "productCode"=>array(
                    //     "alias"=>"Product Code"
                    // ),
                    "cost" => [
                        // "expression" => "orderdetails.quantityOrdered * orderdetails.priceEach",
                        "expression" => "quantityOrdered * priceEach",
                        "alias"=>"Cost",
                        "type"=>"number",
                        "decimal"=>2,
                        "prefix"=>"$",
                    ]
                ),
                "products"=>array(
                    "{meta}" => [
                        "alias" => "Table Products"
                    ],
                    "productCode"=>array(
                        "alias"=>"Product Code"),
                    "productName"=>array(
                        "alias"=>"Product Name"),
                )
            ],
            "relations" => [
                ["orders.customerNumber", "leftjoin", "customers.customerNumber"],
                ["orders.orderNumber", "join", "orderdetails.orderNumber"],
                ["orderdetails.productCode", "leftjoin", "products.productCode"],
            ]
        ),
        "separator" => ".",
    ];
}
Sebastian Morales commented on Nov 16, 2022

Depending on your database PHP driver you can get your tables' meta information such as column names, types, etc. Then use that meta information to create schema arrays and return them in function defineSchemas().

sudhakar Pantula commented on Nov 22, 2022

My database is MySQL. Could you give me an example code here.

Sebastian Morales commented on Nov 22, 2022

We haven't had specific examples for discovering automatic schema of database yet but there're several Stack Overflow guides here which could help you:

For list of tables and columns meta information:

https://stackoverflow.com/questions/468458/how-do-i-get-the-mysql-table-structure-in-php-plus-a-list-of-all-tables

For table relationship:

https://stackoverflow.com/questions/20855065/how-to-find-all-the-relations-between-all-mysql-tables

Build Your Excellent Data Report

Let KoolReport help you to make great reports. It's free & open-source released under MIT license.

Download KoolReport View demo
None yet

None