Proposal to extend Query Loop functionality - Database query support
Functionality Description:
I propose to extend the functionality of Query Loop by the ability to perform queries on MySQL/MariaDb databases. This proposal includes the following changes and improvements:
Adding a new “Database” option to the Query Type:
The user should be able to select Database as the query type within the Query Loop (see Figure 1). After selecting this option, additional steps will become available to enable working with the database. Default Current Wordpress Database.
After selecting the Database query type, the user will be presented with a list of available databases that are available within the installation or that are manually connected (see Figure 2). The user should be able to select the database they want to work with.
After selecting a specific database, a list of tables in that database is displayed from which the user can choose (see Figure 3). This step defines the data source for further processing.
Once a database and table is selected, the columns of that table should be available as dynamic data (see Figure 4). Users will be able to work with this data in a similar way to metadata, allowing dynamic display of values directly in the frontend.
Alternatively, if the user selects “SQL Query” instead of selecting a table, a text box will open to enter a custom SQL query (see Figure 5). The user will still have the option to select a database, but instead of selecting a table, they will execute a custom query. The result of the query will then be processed as in the previous point, i.e. it will be offered in the form of dynamic data for further work.
This extended functionality will greatly increase the flexibility of Bricks when working with data in WordPress, allowing you to create dynamic pages linked to Wordpress or external databases.
Users will be able to work with external data as easily as with regular WordPress queries, increasing the potential for creating sophisticated applications and data visualizations. in the Bricks builder.
Thanks to the entire Bricks team for their great work.
This would be great. If it’s just queries, would say development would be easier. But if updating tables is also on the “table”, that will require an extra care and testing.
Great suggestion!
I meant only the presentation of data using SELECT queries. For editing and inserting data I will write my own WordPress plugin. However, I would like to leave the actual frontend presentation and data display, if I use a Bricks template, to the Bricks system. It’s designed for that, and the Query Loop option is really very powerful, covering most common scenarios.
Your point about the ability to insert or edit data is an interesting extension of my original suggestion. However, as you mention yourself, this could unnecessarily slow down the implementation of the current solution I am proposing.
I totally agree - having native query support would be fantastic! I’ve been hoping for something like this too.
Just thinking out loud here - while SQL queries would be great, we might also want to consider other data sources like external APIs with caching capabilities. Though to be fair, I can see why the Bricks team might hesitate to build this in - it’s probably quite complex to implement and might only serve a specific subset of users.
I actually ran into similar needs in several of my projects, particularly when working with loops and data tags for custom data structures. It led me to create a couple of solutions that might help others in the same boat:
I think this is a difficult request to add something like this
For starters, it would be enough to have some kind of universal function that would turn an array (or json) into dynamic tags and loops.
This would be super cool, but I think it may be too complex to do.
To give open access to entire DB rows, you’ll have to deal with data transforming on each field. I mean, some fields could be binary, some might have json or PHP objects. You might have to deal with string conversions, floats, encryption, the list goes on. It’s not as easy as just picking columns and saying “output here” without considering how every column might need some kind of data type testing and conversion.
And then the security concerns regarding where the data comes from that goes in those tables to begin with. Like what if there was a table acting as a “log” for search terms and someone gets code inserted to the table. If you output this code without care, it might execute it rather than display it.
What we need is something like this for custom database tables but to be able to run ALL the data through some kind of processor to make sure the outputs are what is required and the data is safe. Only after the processor would Bricks take over to loop the data and do conditions on it.
Maybe I’m overthinking it, but there will be cases where something in the DB row is just like an ID reference to something else, such as an ID reference to an image in the Media library, or a user ID or meta data etc. At some point you will require code or data transform I would think. Some kind of PHP or JS or data transform tool would have to be involved to pre-process fields.
