Templating with complex MySQL formula

Grafana MySQL
,
1

I have a very complex Mysql formula in the Templating Query. The result of it not ideal for templating. I used the __value and the __text for definitions of the value and the metric, but the result is included other values of the query.

How can I hide the temporary values and show only the required value in the list of the template?

The Query is:

SELECT xx.*, p.fullname AS __text FROM (SELECT x.* FROM (SELECT id, url, REGEXP_SUBSTR(REGEXP_REPLACE(url, '^https?://([^/]+).+', '\\1'),'([^.]+(\.)?){2}$') AS __value, partner_id, MAX(pub_date) FROM link WHERE partner_id=$source) AS x JOIN link l ON x.id = l.id ) xx LEFT JOIN partner p ON xx.partner_id = p.id WHERE p.active AND p.visible

(The $source is a variable from the other query.)

I would like to use the p.fullname AS __text and the result of the regex AS __value only. But the result also lists the other values.

The result:

Preview of values (shows max 20)
13617032
http://www.example.com/path1/blablabla
example.hu /* I would like to use this value only */
24
2018-01-09T09:53:00Z
Example

I would like to use the 3rd line only.

2

Hi,

I’m not sure I understand what you’re trying to accomplish, but it looks to me that your extracting way too much data that you’re not interested in for the template queries.

How about using something like this?

SELECT 
  p.fullname AS __test
  REGEXP_SUBSTR(REGEXP_REPLACE(l.url, '^https?://([^/]+).+', '\\1'),'([^.]+(\.)?){2}$') AS __value
FROM 
  link l
    JOIN partner p
    ON p.id = l.partner_id
WHERE 
  p.partner_id=$source AND
  p.active AND 
  p.visible

Please note I’m not an expert on MySql, but rather MsSql. Should be quite similar syntax though.

Please let me know if this helps

Marcus

3

After I retried to the query, I realized that the formula was very complicated and could be much simpler. This is how the simplified formula looks:

SELECT url, 
REGEXP_SUBSTR(
 REGEXP_REPLACE(url, '^https?://([^/]+).+', '\\1'),'([^.]+(\.)?){2}$') 
 AS __value,
MAX(pub_date) 
FROM link 
WHERE partner_id=$forras

The result of it:

Preview of values (shows max 20)

http://www.example.com/path1/blablabla
example.hu /* I would like to use this value only */
2018-01-09T09:53:00Z

Unfortunately, your solution didn’t help for me.

I find that the first line of the query is always the first value of the MySQL formula. But I need the second value in the drop-down menu in my Template. This feature is unable to handle complex MySQL queries correctly, even though the value alias can be defined (see AS __value definition).

It may be the right thing to do if I make two queries, the first asking for the link and the second removing the domain from the link.

But unfortunately i don’t understand well for MySQL queries so that I can easily solve this. I’m still looking for a solution…

If you have any ideas, please don’t hesitate. Thank you!

4

Why are you selecting url and max(pub_date) if you don’t want them to appear in the drop down? Just use this:

SELECT 
REGEXP_SUBSTR(
 REGEXP_REPLACE(url, '^https?://([^/]+).+', '\\1'),'([^.]+(\.)?){2}$')
FROM link 
WHERE partner_id=$forras

Marcus

5

Thank you! SOLVED! :smiley: