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" => ".",
];
}