An experiment that uses ContentBlocks to configure datasets and retrieve them from the database.
A common pattern for websites is to present data to their visitors. Think of characteristics or specifications for the items being displayed, or results from search queries listed in tables or diagrams.
Well presented data makes it easier for visitors to gather and compare information.
Usually, data lives in a database. Different types of data are stored in their own table, and each table contains rows with entries for that data type.
If you want to retrieve some of the data, you need to tell the database what kind of data you are looking for and which conditions should apply. This is known as a database query.
Now, there are many ways and languages available for writing database queries, but most of them tend to be too technical for non-programmers to understand. A typical SQL query for retrieving some resources in MODX could look something like this for example:
INSERT SQL query FROM hell
Probably not something you feel like wrestling with while editing content.
That's where DataBlocks come in. DataBlocks attempts to hide the most technical parts of constructing a database query by allowing you to use configurable blocks to retrieve the data you want. It's still pretty technical, but it at least allows you to create queries without writing any code. And once you've wrapped your head around it, you'll see the immense firepower that you now have at your disposal.
So how does it work?
++use our instead of your++
First of all: you need, of course, some data. For the rest of this guide, we'll assume that you have at least 1 custom table set up in MODX, containing some data. Please refer to the guide below on how to set that up, or ask a developer to help you out.
[[how to set up a custom table in MODX]]
And second: you need to connect a few ContentBlocks to your custom table. There's another guide to explain that as well:
[[how to connect your custom table to DataBlocks]]
Once you've got that set up, we can create your first Dataset.
Datasets are defined inside regular resources in MODX. They don't have a special template or anything. In fact, it's probably best to set the template to 'empty' to avoid confusion. What's important is that they're all under the same parent resource, because this is how they'll be referenced everywhere.
If you create a new dataset resource, it probably loaded with a few default layouts and fields already. If not, you can do so by adding a Layout and choosing a Dataset template.
You're given a few standard layouts and fields to start off with.
NB! The layouts carry disctinct titles for retrieving the correct data. Although you can use any available layout to add data fields to, make sure that you're always using the correct title. And that you are adding fields to the correct layout.
The query block decides which data will be retrieved. You can define multiple criteria and chain them together using AND and OR statements.
You start by selecting a column of the database table. This will be your subject. Next, you have to decide what criteria should be met in order for this data to be included in the set. For example, if you want to show all transactions above 100, you select the
greater than operator and fill in
100 as operand.
Our query block looks something like this now:
This will grab all transactions higher than 100 and feed them to your overview or snippet call. Maybe that's all you need, but what if the list of results is still very long and you want to narrow the selection a bit further?
In that case, you can add another query row. There's 2 ways to do this. If you want to select a column in the same database table, you can add another row to the existing block:
This example will tell the query to also look for transactions from a specific user.
But if you need to check a different table, you have to add a separate block for that:
Now we've included inventory items in our search area as well. It looks like something that could work for us, but there's one more thing we need to do: tell the query if all criteria should be met, or if it's ok to include results that only match some of the criteria.
We control this with AND and OR statements.
AND and OR (or OR and AND?)
++use the word conditions instead of criteria++
Or OR or AND? Yeah, it confuses me too sometimes. But it's like this:
AND is satisfied only if all the conditions separated by AND are TRUE.
OR is satisfied if at least one of the conditions separated by OR are TRUE.
For each condition you add to the query, you can choose if it's an AND or OR condition. This will be added at the beginning. You can use these settings to chain your conditions together. For example:
screenshot with multiple conditions
So altogether, the query is now looking for all transactions higher than 100, from user 3, for items 21, 22 or 23.
show output in a table
Now we have our query, but its results are not returned in any particular order yet. We can add a Sortby block to take care of that.
Inside the block you can select the field you wish to, well, sort by. Then decide whether you want the items to go up in range (ASC) or down (DESC). For example: with a list of recent blog items, you probably want to sort by date and let it DESCent in range. But with a list of prices, you'd want to start with the cheapest option and ASCend from there.
You can also add multiple sort blocks. They get applied after each other (if that's still possible). Sort preference in the Overview block itself are then applied after that still.
Knowing that, you should be able to sort it out!
Alright, we defined the criteria for our query. There's only one step left: tell whoever executes the query what fields they will get in return. These fields are then available in the output template.
Let's take a look at our above scenario. You have a list of transactions for a user