PocketQuery: display result horizontally

[Pavel Potcheptsov]

When I execute something like

SELECT users.name
FROM users
ORDER BY name

I get table where each row it's a user_name.

What if I need to display all names in a single row (maybe with comma after each)?

[Carla Roocks (Scandio) · Accepted solution]

Hi Pavel,

Thank you for using PocketQuery!

What you are looking for is a custom template. You can assign a template to each of your query that defines the way its result is displayed. Templates consist of arbitrary Velocity code and are very powerful.

For your query, it could be something basic like this:

#foreach($row in $result)
	${row.name}#if($velocityCount < $result.size()),#end
#end

This template goes through all rows and prints only the "name" attribute. Additionally, to avoid a comma behind the very last element, it uses the loop counter provided by Velocity ($velocityCount).

You can find more information on templates in the PocketQuery Documentation, where you can also have a look at the code of the PocketQuery default template. But for an easy start I highly recommend you to have a look at our Quick Start Guide or the Templating Tutorial.

Hope this helps? Let me know if you need further help!

Best regards

Carla

[Pavel Potcheptsov]

Thank you Carla, your code does exactly what I wanted!

I'd like to know if it possible to use datepicker for :parameters in query when you construct select query for range of dates? It's a bit annoying to fill each time fields From: 2017-02-01 and Till: 2017-02-20.

[Carla Roocks (Scandio)]

Thank you for accepting my answer, I'm happy to hear I was able to help!

I'm not sure I understand what you mean - where exactly would you like to have a datepicker? Within the code editor for writing SQL statements in the PocketQuery administration? Or when adding the PocketQuery macro to a page and setting the parameter values?

[Pavel Potcheptsov]

When you create query in PocketQuery Administration you can use :params.

When you add macro with above query/statement then you can tick Use change params template and Enable dynamic parameters so after saving the page PocketQuery macro shows input fields(form) on the page. So user can pass some values to query.

It's very often that you query something for the range of dates. This is why it'll be useful to have datepicker for such case.

[Carla Roocks (Scandio)]

Ah, I see. Yes, you can include a datepicker, but it will require some advanced templating. PocketQuery doesn't come with a datepicker, so you will need to add one manually. For example this one provided by Confluence: https://docs.atlassian.com/aui/latest/docs/date-picker.html

Basic example code for a single field PocketQuery template using this datepicker:

$webResourceManager.requireResourcesForContext("com.atlassian.auiplugin:aui-date-picker")

<div class="field-group">
<input class="aui-date-picker" id="demo-range-always" type="date" max="2017-02-28" min="2011-12-25" />
</div>

<script>
AJS.$(document).ready(function() {
    AJS.$('#demo-range-always').datePicker({'overrideBrowserDefault': true});
});
</script>

The template used when enabling "Use change params template" is also contained in the documentation: Default Form Template. You can start from there and extend it using the datepicker.

Good luck!

[Carla Roocks (Scandio)]

By the way, if you are happy with PocketQuery, would you mind leaving a review at the Marketplace? We would highly appreciate it!

[Pavel Potcheptsov]

Thanks for your help but this time I couldn't make it work.

What I've done:

1) create new template with name Datepicker

2) past there code from standard template + code for datepicker that you've provided above

<form method="get" class="aui pq-dynamic-parameter-form" action="">
  <button type="submit" class="pq-change-button aui-button aui-style aui-button-primary">Change</button>
  #foreach($key in $queryParameters.keySet())
  #set($reqKey = "pq_$key")
  <div>
    <label for="$reqKey">$key</label>
    <input class="text" type="text" id="$reqKey" name="$reqKey" value="$!req.getParameter($reqKey)" />
  </div>
  #end
</form>
  
$PocketQuery.template("selected-template")

$webResourceManager.requireResourcesForContext("com.atlassian.auiplugin:aui-date-picker")
 
<div class="field-group">
<input class="aui-date-picker" id="demo-range-always" type="date" max="2017-02-28" min="2011-12-25" />
</div>
 
<script>
AJS.$(document).ready(function() {
    AJS.$('#demo-range-always').datePicker({'overrideBrowserDefault': true});
});
</script>

3) assign this template to query that have two parameters for date input

 After that I still have input fields without datepicket. I suspect I'm doing something wrong in template.

I've left review on Marketplace.

[Carla Roocks (Scandio)]

Thanks for the great review! Now I'm even immortalized in a review 

Your template won't work out of the box by copying these code snippets together. For the start, please try to use only the minimalistic template I provided above and asign it to a simple query without any parameters or advanced options - just to see, if the datepicker itself works. (Depending on your Confluence version maybe you might need to adjust the code. Please refer to the Atlassian documentation I linked above.)

If it does work, you need to give the combining of the two code snippets some more thoughts. The default form template uses input fields for parameters and you will want to replace one or two of them with datepicker fields. Also, you might want to replace $PocketQuery.template("selected-template") with $PocketQuery.template("default"), to include the default template there. In any case, you will need to do some more adjustments.

Have you already had a look at our Templating tutorial? It should give you a good introduction to the topic. Building complex templates is not a trivial task, but it's really worth the efforts.