Suggestion: Add a sub-query feature

By: Andrew Christensen | Asked: 09/07/2023
ForumsCategory: General questionsSuggestion: Add a sub-query feature
Andrew Christensen asked 7 months ago
So formidable is basically a relational table system. So, I suggest to Formidable (not sure if they monitor this) that they create a shortcode that could be placed in a view that can be used to get values from the related table.

For example, let's say I'm building a job posting system. I have the following Formidable forms with their fields:

- Company Name - entry_id = 1
- Company Logo - entry_id = 2
- Company Email - entry_id = 3
- Company City - entry_id = 4
- Company State - entry_id = 5 Positions
- Position name- entry_id = 6
- Position Pay Range - entry_id = 7
- Position Qualifications - entry_id = 8

Job Postings
- Company (lookup to Companies table - single select list) - entry_id = 9
- Position (lookup to Positions table - single select list) - entry_id = 10
- various other fields....

So, now I want to display a list of the job postings on a page in say a table with the following info on each row:

Company Logo
Company Name
Position Pay Range
Company City
Company State

In order to do this currently I have to create a series of views:
1. Main view to create the table.
2. View to return the company logo as an image tag.
3. View to return the Position Pay Range as a string.
4. View to return the Company City and State as a string.

This is very cumbersome and really clutters up the admin panel. Not to mention having to setup all the filters on theme to return the right data.

So my proposition would be to create a shortcode that would be something like this:

[frm-query form="Companies" entry_key="Company Name" entry_value="9" column="Company Logo"]

This would go to the Companies table and query where Company Name is the value in the entry_value parameter (entry_id 9). This would then return the value of the requested "column", in this case the url to the logo file.

This would help remove a lot of these annoying little views.

I'm certain this could be expanded upon to a great deal to accept additional tags that are relevant to the column type being requested, such as "show_image=1" to return the image tag and not just the url on an image file upload, or "show_link" to return an href link for an attached file etc.

Additionally, perhaps the entry_key and entry_value params could be allowed to accept a comma separated list to indicate AND operator for getting results where both values match or a ";" delimited list to perform an OR operation.

Am I just crazy or would this be a nice addition?

Victor Font replied 7 months ago

Andrew, your assumption that Formidable creates relational database tables is incorrect. Both WordPress and Formidable use a metadata schema. Metadata schemas are not relational tables in the classic sense of database design. Please take a look at Formidable database schema to follow along with the explanation.

Relational databases store data in structured manner, organized in tables and columns and extended with constraints on the data—primary and unique constraints, foreign keys, check constraints or data types.

The metadata schema in WordPress/Formidable follows a 2-table design regardless of how many forms you create. When a user submits an entry, the two tables that are touched are frm_items and frm_item_metas. The frm_items table is the entry's header record. The frm_item_metas table stores the form's input data, one table row per field. If your form has 40 fields and all 40 are populated, when the user clicks submit, frm_items gets the header record and frm_item_metas gets 40 new table rows.

This is how a metadata schema works. If you examine the table indexes in PHPMyAdmin, you'll see primary keys only. There are no related foreign keys or cascading events. Therefore, these are not relational tables.

The SQL used for extracting data is built with a series of LEFT JOINS where each LEFT JOIN is is a connection back to the frm_item_metas table.

As for your shortcode idea, it's easy to build your own shortcodes. You can certainly do what you envision. There are many ways to improve performance with a metadata schema, but that's a tutorial unto itself. I regularly build relational schemas with flat tables that can be used with Formidable views and lookup fields. If I can do it, anyone can.

Making the Best WordPress Plugin even better - Together

Take on bigger projects with confidence knowing you have access to an entire community of Formidable Experts and Professionals who have your back when the going gets tough. You got this!
Join the community