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
idandslug, 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 listAND t.posts_count >= 2- change to the minimum number of posts in the topic to be qualified for the listinterval '7 days'- change to how many days are OK for the topic creation date to let it appear in the listc.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 withGETto 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!
