Display Data Explorer query results in WordPress (with TwigAnything)

data-explorer

(Anton) #1

Data Explorer plugin lets us extract and explore about any data from Discourse.

In this tutorial I’ll show you how to display it in WordPress, like this:

  • The data can be displayed in posts/pages, widgets or in Visual Composer blocks
  • The data is optionally cached in WordPress
  • You have full control on how data is output.
  • In this tutorial we will use Twig Anything WordPress plugin (I’m the plugin author)

Use-case

So, let’s build a list of topics from particular categories published in the last 7 days that had comments, sorted by popularity.

We first build a query in Data Explorer to retrieve the topics list from database by our criteria.

Once done, we then use Twig Anything to fetch this data from Discourse and output it in WordPress.

This tutorial is built based on the live Discourse installation at forum.kozovod.com, a Ukrainian community of goat farmers, which I manage.

Step 1. Build Data Explorer Query

In our query, we will need:

  • topic score, which we display in the list
  • topic id and slug, which we use to build a link to the topic
  • topic title, which makes up the text of the link
  • username of the user who created the topic, to show it in the list

Here is our query

SELECT score, t.id, t.slug, t.title, u.username
FROM topics t
LEFT JOIN categories c ON c.id = t.category_id
LEFT JOIN users u ON u.id = t.user_id
WHERE c.name IN (
    'Козоводство',
    'Разведение',
    'Содержание и кормление',
    'Ветеринария',
    'Селекция и генетика')
  AND t.created_at >= current_timestamp - interval '7 days'
  AND t.posts_count >= 2
  AND t.visible = true
  AND t.closed = false
  AND t.archived = false
  AND t.pinned_globally = false
  AND t.archetype = 'regular'
  
ORDER BY score DESC
LIMIT 10

Here is how to adjust the query to your needs:

  • LIMIT 10 - change to the maximum number of topics you want in your list
  • AND t.posts_count >= 2 - change to the minimum number of posts in the topic to be qualified for the list
  • interval '7 days' - change to how many days are OK for the topic creation date to let it appear in the list
  • c.name IN (...) - list category names to only allow topics from those categories; remove the clause altogether if you don’t want to filter by categories

When running the query in the Data Explorer, you’ll see an output like on the following screenshot. Please note the columns are indexed from 0 - 4, we’ll need this later:

You’ll also need query ID, which you can find from the URL after saving and running your query:

http://forum.kozovod.com/admin/plugins/explorer?id=15

Here, the id is 15.

Step 2. Create Twig Template in WordPress

In WordPress, start by creating a new Twig Template:

Give it a meaningful name:

To not get confused when the number of Twig Templates in my WordPress grows, in the name I use both Kozovod, which is my Discourse instance name, and 15, which is my Data Explorer query’s ID.

Next, scroll down and find the “Data Source” box and configure it as follows:

  • Source Type: URL

  • Data Format: JSON-encoded string

  • Cache lifetime in seconds: leave empty for now while we’re setting it up

  • Data errors handling: choose what WordPress should show if an error occurs while retrieving or rendering data from Discourse

  • Data URL: here, you’ll have to build the correct URL: http://<your.discourse.com>/admin/plugins/explorer/queries/<ID>/run?api_username=...&api_key=...

    • User your correct Discourse API username and key
    • Put your query ID where it says <ID>
    • Put your discourse domain where it says <your.discourse.com>
    • Don’t forget to change http:// to `https://`` if you’re running secure connections over HTTPS
  • HTTP Request Method: Select POST. It won’t work with GET to fetch data from Data Explorer.

Now, enter the following template that will render your data in a list:

<ul>
{% for row in data.rows %}

  <li>
    <a href="http://forum.kozovod.com/t/{{row[2]}}/{{row[1]}}" style="color: blue">
      {{row[3]}}
    </a>
    by <code><strong>{{ row[4] }}</strong></code> scored
    <strong>{{ row[0]|number_format(1) }}</strong>
  </li>

{% endfor %}
</ul>

This template is in Twig Syntax, which is very easy to learn.

Note how we loop over data.rows
The data is how you access the JSON data fetched from Discourse.
The data.rows contains to the list of rows as you saw them in Data Explorer, in the same sequence.

Now, to access a particular column we use {{ row[0] }}, where 0, 1, 2 etc is the index of the column.

So, for example, in this line we use column No. 4 to output the nickname of the user who created the topic:

by <code><strong>{{ row[4] }}</strong></code> scored

We also use topic ID and topic slug to build a URL that links to that topic:

href="http://forum.kozovod.com/t/{{row[2]}}/{{row[1]}}"

Which will eventually become something like this:

http://forum.kozovod.com/t/istochniki-sovremennyh-znanij-po-genetike/2770

Finally, we use Twig’s number_format filter to format topic scores so that only one digit after decimal point is shown:

<strong>{{ row[0]|number_format(1) }}</strong>

Step 3. Preview your Twig Template

All done! Now click on the Preview button in WordPress:

A new tab will be opened displaying the template preview:

If you’re satisfied with the output, click Publish to make the template available for embedding and proceed to the next step!

Step 4. Embed your data anywhere in WordPress

Showtime!

Now copy the shortcode to clipboard and paste it into your post or page:

Alternatively, you can use a Twig Template Widget:

Finally, you can embed it right in Visual Composer:


When Data Explorer and Twig Anything work together, endless possibilities come for integrating Discourse and WordPress.

Twig Anything is not free, you can buy it here. It’s not expensive either, and your purchase will help me to dedicate time and develop more features and add-ons, which are always free.

Enjoy and ask me any questions!


SQL: The most N used words per user (speak their language!)
Announcing WordPress plugin to display any data from Discourse
Effective & complex 1-month long contest; goal — actively develop selected categories, and share its topics in the Internet
Twig Anything – Show any data from Discourse in Wordpress – an Open Source WordPress plugin
(SMHassanAlavi) #2

Hi.
I find a query here and customized it to return the number I want .and now I want to show everybodys score (I find the score from query)in their profile .what should I do?


(Anton) #3

Hi Alavi, are you asking about the WordPress user profile?


(Anton) #4

Display newly registered Discourse users in WordPress

This quick how-to is about displaying the list of the newly registered users from Discourse in WordPress.

I implemented this on our WordPress website, which is complementary to our Discourse instance, so sharing a quick how-to with illustrations while it’s fresh in my memory.

Here is how it looks on our WordPress website:

It lists the latest 8 registered users who reached Trust Level 1.

If your forum is very active and you have new user registrations every single day, we can elaborate the guide to show two lists: “Today registrations” and “Yesterday registrations”; let me know if this is something you’d like to try.

You can also easily adapt the steps below to show how many days (hours) ago the user registered, or the exact registration date, or additionally show their names and/or their avatars.

I’m not showing the avatars because it’s rare that new users upload their avatars immediately, so there will be a lot of placeholders, i.e. a circle of a particular color with a capital letter in it.

Steps

Step 1. Create a new query in your Data Explorer in Discourse:

SELECT id as user_id,
  username, username_lower,
  name, title, created_at,
  uploaded_avatar_id,
  suspended_at, suspended_till
FROM users
WHERE admin = false
  AND moderator = false
  AND trust_level >= 1
  AND active = true
  AND staged = false
  AND silenced_till IS NULL
  AND suspended_till IS NULL

ORDER BY created_at DESC
LIMIT 8

You should tune the query to your needs:

  • LIMIT 8 — how many users you would like to show in the list
  • AND trust_level >= 1 — what is the minimum trust level; in this example, users with trust level 1 and above will be shown, but not users with trust level 0; users hold level 0 after they sign up and before they do any activity;

Once you save the query, grab its unique ID from the URL:

Step 2. Create a new Twig Template in WordPress:

image

Configure the template:

The template code:

{% for row in data.rows %}
  <a href="https://forum.kozovod.com/u/{{ row[1] }}">{{ row[1] }}</a>
  {% if not loop.last %},{% endif %}
{% endfor %}

Source Type: URL
Data Format: JSON-encoded string

Cache lifetime in seconds: 300
You can set it to whatever is suitable.
The Discourse API will be queries only one time per 300 seconds (5 minutes).
I do not recommend to leave it empty.

HTTP Request Method: POST

Data URL

https://forum.kozovod.com/admin/plugins/explorer/queries/35/run.json?api_username=...&api_key=...

Change https://forum.kozovod.com to your website.
Change 35 to the Data Explorer Query ID we grabbed in the previous step.
Insert your Discourse api_username and api_key

Now click on “Save” and then on “Preview changes”, make sure the user list is fetched and is displayed:

Display in WordPress

Now, just copy the shortcode and embed it in a widget in your sidebar:

A nice box was provided by the theme for me, so I just had to insert the shortcode:

image


Announcing WordPress plugin to display any data from Discourse