Pass current pagename to query

[jbeumer]

How can i pass the current pagename ($page.title) to the pocket-query?

Not working sample:

select fielda, fieldb from tablex

where id=:id and pagename=:$page.title

[Felix Grund (Scandio)]

Hi Alexey,

If you add this code at Confluence Admin > Custom HTML, all PocketQuery query parameters that end with a "_" will only be shown in the macro browser if you're a Confluence administrator:

<script>
AJS.toInit(function() {
	var $style = '<style>#pocket-param-list label[for$="_"],#pocket-param-list label[for$="_"] + input { display: none; }';
	if (!AJS.Meta.get('is-confluence-admin')) {
		$(document.body).append($style);
	}
});
</script>

There is unfortunately no such feature to specify defaults for date parameters. But we'll keep this idea in mind 

Regards, Felix

[Felix Grund (Scandio)]

Hi Jeroen!

You can simply use this in your query:

select fielda, fieldb from tablex
where id=:id and pagename=:page

Then in your macro, you can enter @page for the parameter "page". You can see all wildcards in our documentation in the section "PocketQuery Macro".

Let me know if you need further help! 

[Alexey Anisimov]

Hi Felix, Actually Jeroen have asked a very good question. Sometimes it is desirable to have dynamic parameters like "@userName" directly inside SQL query to restrict users from changing it in the page macro. In confluence I can restrict page editing to lock users from changing macro parameters, but they can create their own page and override parameters there. Thanks!

[Felix Grund (Scandio)]

Yes, there is currently no such feature to replace wildcard parameters right before the SQL statement is built. The feature does not have the highest priority for us at the moment. But I can show you workarounds with CSS/JS if you're interested.

[Alexey Anisimov]

It would be great! Thanks for the promptly answer.

[Felix Grund (Scandio)]

See here... assume you have this query: SELECT * FROM Users WHERE Username = :_hiddenUserName Would it be OK if this field is pre-filled with @username by default in the macro browser and the parameter field in the macro browser is ONLY available to specific users OR to Confluence administrators?

[Alexey Anisimov]

Cool! That would be great to choose if query parameter is available to all users, specific users or admins only. for example select * from all_bonuses where username=:_hiddenUserName and bonus_date between :startDate and :endDate; Here user will have only :startDate and :endDate available for change. By the way, is it possible to provide default values for date parameters like @TODAY or @TODAY-30 (30 days ago)? :)

[Jeroen van der Hoek]

Hi Felix! Any news on this feature to directly add username/email to the query? We would be interested as well. Just hiding the parameter field in the front end might be to insecure for some of our customers.

Ps. Is this the right place to request new features? I had trouble signing up at https://help.scandio.de/servicedesk/customer/portal/5/user/signup

[Felix Grund (Scandio)]

Hi Jeroen! Thank you for staying in touch! There hasn't been any progress on this topic, unfortunately. The link you posted is exactly the right place. You can submit a feature request there and we will analyze and prioritize it. What exactly is the issue signing up?

[Jeroen van der Hoek]

Now it's working!