Andrew Christensen replied 2 weeks ago

@Victor Font Will adding that filter affect other uses of the visual views?

In the example I provided, the form has a text field for "industry" that has just one industry entered. Hopefully this makes the query fairly simple.

Victor Font replied 2 weeks ago

The filter will run for all views. You wrap your code in an if statement with $args['display']->ID = 9999 where 9999 is the ID of your view.

The thing to keep in mind is that this will not provide the benefits of transportable code. If you ever migrate to a different WordPress instance, your custom code will stop working because IDs change.

To bullet proof your code, use the view key to retrieve the view ID. As an example, you world use something like this:

add_filter( 'frm_view_order', 'my_callback_function', 10, 2 );
function fmy_callback_function $query, $args ) {

$my_view_id = FrmViewsDisplay::get_id_by_key( "my_view_key" );

if ( $args['display']->ID = $my_view_id) {

Bobby Clapp replied 2 weeks ago

Only the ID listed in "$args['display']->ID == 2412" would be affected.

Andrew Christensen replied 2 weeks ago

Ok thank you all for the help. I'm getting close. But I'm having trouble with understanding how the $query['select'] should look. Here's what I have so far (simplified but general idea):
```
add_filter('frm_view_order', 'change_view_order', 10, 2);
function change_view_order($query, $args){
global $wpdb;

if ( $args['display']->display_key != "occupational-data-grid" ) {
return $query;
}

// now set the county if provided or set to "San Diego" by default
$county = $_REQUEST['county'] ?? 'San Diego';

$county_items = $wpdb->get_results( "SELECT item_id FROM `{$wpdb->prefix}frm_item_metas` WHERE `field_id` = 6 and `meta_value` = '{$county}'", OBJECT);
$county_item_ids = array_column($county_items, 'item_id');

// get all the entries limited to ones for the county that match the education values OR the industry values
$id_select = "SELECT item_id FROM `{$wpdb->prefix}frm_item_metas` WHERE `item_id` IN (" . implode(",", $county_item_ids) .") AND ((field_id = 17 and meta_value IN ('diploma', 'degree')) or (field_id = 25 and meta_value IN ('manufacturing', 'transportation')))";

$all_items = $wpdb->get_results($id_select, OBJECT);
$all_item_ids = array_column($all_items, 'item_id');
$all_item_ids = array_unique(array_values($all_item_ids));

$select = "SELECT * from " . $wpdb->prefix . "frm_items WHERE id IN (" . implode(",", $all_item_ids) . ")";
$query['select'] = $select;

return $query;
}
```

It's hard to tell what the final select statement should look like. As it is above, I get nothing. But if I call the actual wpdb->get_results on that select statement, it delivers all the entries that match. But I get nothing out of my view other than "No entries found".

Bobby Clapp replied 2 weeks ago

Maybe this reference will help better?

https://formidableforms.com/knowledgebase/frm_filter_where_val/

Victor Font replied 2 weeks ago

This is code I use for frm_view_order. Make sure query['where'] is emptied:

if ( isset( $args['display']->post_name ) && $args['display']->post_name == "codexv2-hooks-list-header" ) {
add_filter( 'frm_before_display_content', 'FrmViewsDisplaysController::prepend_pagination', 10, 4 );
/* initialize variables for the sql */
global $wpdb;
$wpdb_prefix = $wpdb->prefix;
$frm_items_table = $wpdb_prefix . 'frm_items';
$frm_item_metas_table = $wpdb_prefix . 'frm_item_metas';

$codexv2_plugin_name_key = 'codexv2_plugin_name';
$frm_codex_lu_addon_plugin_name_key = 'frm_codex_lu_addon_plugin_name';
$codexv2_type_key = 'codexv2_type';

$codexv2_plugin_name_id = FrmField::get_id_by_key( $codexv2_plugin_name_key );
$frm_codex_lu_addon_plugin_name_id = FrmField::get_id_by_key( $frm_codex_lu_addon_plugin_name_key );
$codexv2_type_id = FrmField::get_id_by_key( $codexv2_type_key );
$form_id = $query['where']['it.form_id'];

/* query string parameters */
$header_id = ( isset( $_GET[ 'header_id' ] ) ) ? $_GET[ 'header_id' ] : "";
$entry_type = ( isset( $_GET[ 'entry_type' ] ) ) ? $_GET[ 'entry_type' ] : "";

if ( $header_id !== "" ) {
// add_filter('frm_before_display_content', 'customize_before_content', 10, 4);
}

$sql = "SELECT it.id FROM `{$frm_items_table}` it LEFT JOIN `{$frm_item_metas_table}` em0 ON em0.item_id = it.id AND em0.field_id = '{$codexv2_plugin_name_id}' LEFT JOIN `{$frm_item_metas_table}` em1 ON em1.item_id = em0.meta_value AND em1.field_id = '{$frm_codex_lu_addon_plugin_name_id}'";

$where = "WHERE it.is_draft = 0 AND it.form_id = {$form_id}";
$where .= ($header_id !== "") ? " AND em0.meta_value = '{$header_id}'" : "";

if ( $entry_type !== "" ) {
$sql .= " LEFT JOIN `{$frm_item_metas_table}` em2 ON em2.item_id = it.id AND em2.field_id = '{$codexv2_type_id}'";
$where .= " AND em2.meta_value = '{$entry_type}'";
}

$order = "GROUP BY em0.meta_value ORDER BY em1.meta_value ASC";
$order .= ($header_id !== "" && ! isset( $args['limit'] ) ) ? " LIMIT 0, 1" : "";

$query['select'] = $sql . " " . $where;
$query['where'] = '';
$query['order'] = $order;
}