Is there a Data Explorer for Dummies article somewhere?

(Robert McIntosh) #1

I’ve ‘magpied’ (a lovely term a Primary School teacher once used to talk about how my son’s inspiration for stories could be heavily influenced by content he had read before … not really plagiarism) some wonderfully useful Data Explorer queries from this discussion, but I struggle to understand what they do and some aspects do not work

For example, I don’t understand the use of --- [params]

I’ve struggled to find an introduction to SQL queries for Data Explorer anywhere so that I didn;t have to ask potentially stupid questions or ask others to do work I should be able to do, so if you can point me to any useful resource I would be very grateful!

(Gerhard Schlager) #2

-- starts a comment in SQL. The -- [params] tells the Data Explorer that the following comments contain named parameters which can be used within the SQL query. Everything else is standard SQL, so you can do (mostly) everything PostgreSQL can do in queries.

Did you see the topic about the Data Explorer Plugin?

(Robert McIntosh) #3

Yes, thanks. I’ve read what I can

When I try to save a query with[params] I get an error message, and I don’t understand whether I am supposed to replace it when I save the query, or whether I will be prompted to enter them each time I run it, for example

Also, basic issues such as ‘What is the correct date format’?

(Gerhard Schlager) #4

What error message do you get?
You get input fields for every parameter and the values are used when you run the query.

(Robert McIntosh) #5

SO, I was playing with this query

and posting it ‘as is’ I get a pop-up with:

Parameter declaration error - the default value is not a valid int

(Gerhard Schlager) #6

That looks like a bug to me. 1 is definitely a valid integer. You can use it without the default value until this is fixed.

(Robert McIntosh) #8

Hmm … but a bug where?

When I replace the word [params] with [parameters] I can save the query, but then when I run it, I get the query returned with this message at the top (not sure I like my community calling me a Dummy!!) :

ActiveRecord::PreparedStatementInvalid: missing value for :date_from in /*

  • DataExplorer Query
  • Query: /admin/plugins/explorer?id=20
  • Started by: robert_mcintosh
  • :xxdummy

(Gerhard Schlager) #9

Please don’t replace [params] with [parameters], otherwise the params are not found and you get a nasty error message. :wink: All you need to do is omit the default value for int parameters until the data explorer plugin is fixed.

So, the beginning of your query should look like this:

-- [params]
-- date :date_from
-- date :date_to
-- int :min_posts

(Robert McIntosh) #10

OK, that worked, so the bug was the “= 1”

unfortunately, now the query works I realise it does not give me what I needed, but at least I know more about [params] and I’ve discovered a postgreSQL tutorial site


(Andreas Dorfer) #11

Where is something in the Range of 1361923200?
And where is 41332 in this unfortunate list?


Check this list of queries and tips too.

(Robert McIntosh) #13

thanks @SidV - I have been, but they are not all working for the same reasons above - the integer is creating issues. Not sure if there is a bug somewhere with Data Explorer and my version (1.9.0 b14)

my issue is that I can cut & paste some of these (which helps) but I can’t modify them if I don’t understand how they work … so I must educate myself


If you check the error part, I put how to solve the only error with int params.

Or did you have other error?
Can you put a screenshot with the error?

(Kane York) #15

Oops - someone can feel free to replace that with xxplaceholder or similar. The point of that parameter is so AR doesn’t complain about there not being any named parameters at all when there aren’t any intended.

(Robert McIntosh) #16

thanks, yes, that is the same error - I hadn’t seen that. Cheers

(Gerhard Schlager) #17

It took me a while until I figured this one out. I was testing on Linux, but the bug occurred only when you entered the query on Windows.

Strange problem with Data Explorer