view filters with multiple values in one param

By: Andrew Christensen | Asked: 05/07/2024
ForumsCategory: General questionsview filters with multiple values in one param
Andrew Christensen asked 3 weeks ago
I have a need to load a view where it will return all entries in the table where one column is one of several values. For example, if we had a job search board where there are say 30 industries but a user wants to search through the jobs for only a couple industries.

I have a page setup with a search function where the user can select multiple industries. When they submit, the page changes to include a url param like this: ?industries=Retail,Hospitality. I'm working on making a shortcode that will execute the form view shortcode. But the best I can figure, in the form views setup (in Visual Views) the only thing I can do is set it so that the industry column is the filter and use contains or equals. There's no option for a "where in". Is there some way I can do this?
1 Answers
Victor Font answered 3 weeks ago
You can create a custom SQL select with the frm_view_order filter. The issue you may bump into is how the data is stored in the database. You didn't specify the type of field "industries" is. Formidable stores some multi-select values as serialized arrays. If industries is such a field, it adds complexity. https://formidableforms.com/knowledgebase/frm_view_order/
Andrew Christensen replied 3 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 3 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 3 weeks ago

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

Andrew Christensen replied 3 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 3 weeks ago

Maybe this reference will help better?

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

Victor Font replied 3 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;
}

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
crossarrow-right