How to pass query parameters from within a template using the PocketQuery.queryArray command

[Mark Robinson]

Hi, 

I am writing a Pocket Query Template that requires a SQL Query parameter (date value) to be passed to the command PocketQuery.queryArray() to allow it to query the DB twice with two dates to retrieve to sets of data for array manipulation. There are three other parameters that will get specified from the PocketQuery instantiation in the normal fashion.

Could you please give the code snippet to allow to do this.

I looked at $queryParameters which gives me the parameters within the template but not a means of modifying and sending it back via the JavaScript,

Much Appreciated,and thank you for your support.

Mark

P.S. I think PocketQuery is really good and are starting to get some good graphs and tables from it.

[Mark Robinson]

The best solution is to the keep the Pocket Query Template simple for tables, using simple if's to highlight or drop rows. Then have calculated fields in the DB Tables that are pre-calculated during table updates or in a separate process. 

Mark

[Mark Robinson]

Felix,

Thank you for your answer and you've already pointed me in the right direction for the original issue, and have found an example using  $PocketQuery.renderPocketQueryMacro. Now I understand it further, I've managed for the application I have, to do just a single query to get all the data I require from the DB. I can then transform the data I require using JavaScript commands/functions, however I can't display the data from the <script> area like I can a pocket google chart using the following command:

PocketQuery.chart('Table', {
    dataTable: ProcessTable
});

Since I don't know the format of the ProcessTable Array shown above. In standard Google Visualization commands it using this inbuilt format to display a table.

var tableData = new google.visualization.DataTable();

where 

for(var i = 0; i < data.columns.length; i++) {
   tableData.addColumn('number', data.columns[i]);
}

for (var i = 0; i < data.rows.length; i++) {
    // Rows are a list of comma separated columns
 tableData.addRow(data.rows[i].rows);
}

var table = new google.visualization.Table(document.getElementById('task_etc_fluctuations-table'));
table.draw(tableData, options);

I could try and use this format, like in your example to create a new Pocket Query table of data from the single query

<table class="pocketquery-table confluenceTable">

  <tr>
  #foreach ($column in $columns)
    <th>$!column</th>
  #end
  </tr>
  
  #foreach ($row in $result)
    <tr class="#if($row.Population < 60000000)low#{elseif}($row.Population > 80000000)high#end">
    #foreach ($column in $row)
      <td>$!column</td>
    #end
    </tr>
  #end

</table>

But in may case I need two or three functions from the RAW data to the displayed data to get the result I require. Each function iterates over the arrays doing calculations

So could you please tell me the either:

The format of the array for PocketQuery to display a table, or allow it to use google visualization functions or how to pass the output of my JavaScript functions array to the this format

class="pocketquery-table confluenceTable">?

thanks

Mark

[Felix Grund (Scandio)]

Hi Mark,

dataTable must be a two-dimensional array where every row is an array in the array. See this example:

<script>
PocketQuery.chart('Table', {
	dataTable: [
		['Column 1', 'Column 2', 'Column 3'],
		[11, 12, 13],
		[21, 22, 23],
		[31, 32, 33]
	]
});
</script>

Does this help?

[Felix Grund (Scandio)]

Hi Mark!

Thank you for your interest in PocketQuery! I do not really understand your question just yet. PocketQuery.queryArray() just gives you the results of a query in JavaScript. The only parameter it receives is the name of the query for which you want to get the results. If you want to query the DB twice you will either have to use two PocketQuery macros, or you render another PQ macro from within the template using $PocketQuery.renderPocketQueryMacro.

Please clarify what you really want to achieve.

Best, Felix (Scandio)