[Resolved] Can I run a manual query (including a calculated field) that passes to a View?

[Author]

Posts

[aaronM-9]

I'm about to start work on a feature within my website and would like to know what is and isn't possible with respect to Toolset before I move forward. I have a CPT's Products, Member Profiles, and Reviews. I would like to display a list of Products (using a View) that is sorted based on how well each Product correlates to some values stored in the Member Profile taking into consideration other users' Reviews of said Products, and I would like to be able to display that correlation value in the output. Basically, a product recommender.

My question is not necessarily how to do all of this per se, but more the smartest way to best integrate it with Toolset so I can maximize my usage of the Toolset GUI and minimize the custom programming. So the question becomes: is it possible to run a specific SQL query that calculates the correlation of the Product to the user, sort the query by the correlation factor, but then pass the value of that correlation factor to Views so I can display it in the output?

Something like:

SELECT posts.*, correlationValue
FROM posts INNER JOIN [to a bunch of other posts and things]
WHERE [bunch of sql magic here]
ORDER BY correlation

...and then be able to work with and display everything as you normally would in a View but have the extra "correlation" field available for display.

The other option I was thinking of was to create a custom field "Correlation" in some intermediary post type between the Member Profile and Product CPT's, which would probably be easiest to do because I could run all the SQL crunching separately, but I'm worried it would hugely bloat my database because you'd get a record for every User-Product combination.

Would like to hear your thoughts on how this might best work within the Toolset capabilities. Thanks.

- Aaron

[Beda]

Can Toolset query and order by fields of related posts?
No, but these are features that are under review and will eventually be possible in future.

Can Toolset display related information?
Yes, you can display information of basically every connected Post within a View.

You can as well create nested Views, where for example, you query Posts, and in a Child View, you query connected posts to the post in the parent View loop.
This can allow you to create lists that are ordered and queried by a parent post data, and in that list, for each post, you'd then have a list of connected data, ordered by the data sets of that post type.

Or, you can alter the View's Outputs and queries with the API:
https://toolset.com/documentation/programmer-reference/toolset-hooks/
https://toolset.com/documentation/programmer-reference/views-filters/

However, this required Custom Coding that we cannot provide as such, as well because these features are in the making, hence we will sooner or later provide them in Views.

[aaronM-9]

Thanks, that's helpful and good to know these features are being developed. I have in fact made use of nested views but this seemed different to me because I was looking to calculate a value on the fly (as opposed to just displaying some value stored in the database). On that specifically, would it be possible to use a hook to add that calculated field but then be able to display it inside of views? If not, I've got another idea in mind but just want to scratch that off the list first. Cheers.

- Aaron

[Beda]

Calculating is impossible with Toolset out of the box and there are no agreements to develop such features.

What we have in our plans (soon to be published) is a short DOC that shows some examples of custom ShortCodes which you can craft, adapt and use on your sites to make calculations with values in views as well.

Let me add both example here:

1. Define ShortCode

/**
 * Add custom shortcode to perform calculations with Types fields
 * 
 * attribute 'round' for decimal places, defaults to zero
 */
add_shortcode('calculate', function( $atts = [], $content=null ) {

	$atts = shortcode_atts([ 'round' => 0 ], $atts );

	$content = strip_tags( wpv_do_shortcode($content) );
	$content = eval("return $content;");
	return round($content, $atts['round']);
});

Use as:

[calculate round=2][types field="weight"]*[types field="unit-price"]/100[/calculate]

2. Define ShortCode:

global $total;
function add_total_shortcode($atts, $content = '') {
global $total;
 
$total += wpv_do_shortcode($content);
}
 
add_shortcode('add-to-total', 'add_total_shortcode');

function show_total_shortcode() {
global $total;
$totalNew = $total;
$total = 0;
return $totalNew;
}
 
add_shortcode('show-total', 'show_total_shortcode');

Usage:

<wpv-loop>
    [types field="your-numeric-field" output="raw"][/types]
    [add-to-total]
      [types field="your-numeric-field" output="raw"][/types]
    [/add-to-total]
</wpv-loop>
[show-total]

[Beda]

Changed status

[aaronM-9]

Thanks - that will definitely help me plan how I'm going to approach this next step. Appreciate the support.

- Aaron