Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
<?php
add_filter('frm_where_filter', 'custom_view_where_clause', 10, 2);
function custom_view_where_clause( $where, $args ) {
/* we'll use $view_id and field id to drive the switch statement below
* why a switch statement? switch statements provide better performance than ifs
*/
$view_id = $args['display']->ID;
$field_id = $args['where_opt'];
/* retrieve the form and field ids from their keys
* note: we use keys to identify forms and fields so code is transportable across WordPress isntances
* if we use IDs, the code can break because IDs change when forms are imported
* We still need to convert the keys to IDs for the SQL to work
* See this article to learn more: https://formidable-masterminds.com/writing-transportable-code-keys-vs-ids/
*/
/* 1. Provider form and fields */
$programproviderentry_key = 'programproviderentry';
$programproviderentry_id = FrmForm::get_id_by_key( $programproviderentry_key );
/* field values stored as text */
$program_partner_name_key = 'program_partner_name';
$program_partner_name_id = FrmField::get_id_by_key( $program_partner_name_key );
$program_partner_locations_key = 'program_partner_locations';
$program_partner_locations_id = FrmField::get_id_by_key( $program_partner_locations_key );
$program_partner_courses_key = 'program_partner_courses';
$program_partner_courses_id = FrmField::get_id_by_key( $program_partner_courses_key );
if ( ( $view_id == '807' && $field_id == $program_partner_name_id ) || ( $view_id == '826' && $field_id == $program_partner_locations_id ) || ( $view_id == '893' && $$field_id == $program_partner_courses_id ) ) {
global $wpdb;
/* create the variables for the formidable items and item metas tables */
$wpdb_prefix = $wpdb->prefix;
$frm_items_table = $wpdb_prefix . 'frm_items';
$frm_item_metas_table = $wpdb_prefix . 'frm_item_metas';
/* retrieve the current user id */
$student_id = get_current_user_id();
// $student_id = "1"; // for testing purposes
/* get the rest of the required form fields */
/* 2. Student form and fields */
$studentregistration_key = 'studentregistration';
$studentregistration_id = FrmForm::get_id_by_key( $studentregistration_key );
/* field stores provider id numbers */
$student_course_preferences_key = 'student_course_preferences';
$student_course_preferences_id = FrmField::get_id_by_key( $student_course_preferences_key );
/* field values stored as text and serialized arrays */
$student_location_preferences_key = 'student_location_preferences';
$student_location_preferences_id = FrmField::get_id_by_key( $student_location_preferences_key );
$student_organization_interest_key = 'student_organization_interest';
$student_organization_interest_id = FrmField::get_id_by_key( $student_organization_interest_key );
/* retrieve a list of providers student for whom student has an interest
* there is no limit to the number of virtual fair entries a student may have,
* therefore we have to retrieve all virtual fair entries for the student.
*/
/* get the data */
switch ( $view_id ) {
/* Best matches by location */
case 826 && $field_id == $program_partner_locations_id :
/* example result:
( fi.id = 10 AND ( ( meta_value like '%Afghanistan%' ) OR ( meta_value like '%American Samoa%' ) OR ( meta_value like '%Angola%' ) OR ( meta_value like '%Anguilla%' ) OR ( meta_value like '%Bermuda%' ) OR ( meta_value like '%Chile%' ) OR ( meta_value like '%Croatia%' ) OR ( meta_value like '%Czech Republic%' ) OR ( meta_value like '%Denmark%' ) OR ( meta_value like '%Germany%' ) OR ( meta_value like '%Ireland%' ) OR ( meta_value like '%Italy%' ) OR ( meta_value like '%Kazakhstan%' ) OR ( meta_value like '%Uruguay%' ) ) )
*/
$sql = $wpdb->prepare( "SELECT meta_value FROM `" . $frm_item_metas_table . "` where field_id = %s AND item_id IN ( SELECT id FROM `" . $frm_items_table . "` where form_id = %s and user_id = %s )", $student_location_preferences_id, $studentregistration_id, $student_id );
$student_location_preferences = $wpdb->get_results( $sql );
$where = process_dataset( $student_location_preferences, false, $program_partner_locations_id );
break;
/* Best matches by course */
case 893 && $field_id == $program_partner_courses_id :
/* example result:
( fi.id = 81 AND ( ( meta_value like '%Accounting%' ) OR ( meta_value like '%Advertising%' ) OR ( meta_value like '%African Studies%' ) OR ( meta_value like '%American Studies%' ) OR ( meta_value like '%Anthropology%' ) OR ( meta_value like '%Archaeology%' ) OR ( meta_value like '%Architecture%' ) OR ( meta_value like '%Business%' ) OR ( meta_value like '%Classical Studies%' ) OR ( meta_value like '%Creative Writing%' ) OR ( meta_value like '%Criminal Justice%' ) OR ( meta_value like '%Cultural Studies%' ) OR ( meta_value like '%Economics%' ) OR ( meta_value like '%Political Science%' ) ) )
*/
$sql = $wpdb->prepare( "SELECT meta_value FROM `" . $frm_item_metas_table . "` where field_id = %s AND item_id IN ( SELECT id FROM `" . $frm_items_table . "` where form_id = %s and user_id = %s )", $student_course_preferences_id, $studentregistration_id, $student_id );
$student_course_preferences = $wpdb->get_results( $sql );
$where = process_dataset( $student_course_preferences, false, $program_partner_courses_id );
break;
/* All providers matching student orgranization interests */
case 807 && $field_id == $program_partner_name_id :
/* first retrieve the student's organizational interests
* because field is a dynamic checkbox list, values contain
* entry ids. these have to be trasnlated to their
* respective provider partner names to match values
*/
$sql = $wpdb->prepare( "SELECT meta_value FROM `" . $frm_item_metas_table . "` where field_id = %s AND item_id IN ( SELECT id FROM `" . $frm_items_table . "` where form_id = %s and user_id = %s )", $student_organization_interest_id, $studentregistration_id, $student_id );
$student_organization_interests = $wpdb->get_results( $sql );
$entry_query = process_dataset( $student_organization_interests, true );
/* convert the entry ids to provider names for matching */
/* can't use $wpdb->prepare here because it escapes the quotes in $entry_query and breaks the where clause*/
$sql = "SELECT meta_value FROM `" . $frm_item_metas_table . "` where field_id = '" . $program_partner_name_id . "' AND item_id IN " . $entry_query;
$provider_entries = $wpdb->get_results( $sql );
$entry_query = process_dataset( $provider_entries, true );
/* build the where clause */
$where = "( fi.id = " . $program_partner_name_id . " AND meta_value IN " . $entry_query . " )";
break;
default:
}
}
return $where;
}
/**
* process_dataset function.
*
* processes and formats all data returned from the queries
*
* @access public
* @param object $dataset
* @param bool $use_in_operator (default: false)
* @param string $field (default: NULL)
* @return void
*/
function process_dataset( $dataset, $use_in_operator = false, $field = NULL ) {
/* determines whether to process as text string used with IN operator for provider name
* or to return formatted where filter for checkboxes
*/
$query_meta = ( $use_in_operator ) ? "" : "( fi.id = " . $field;
/* loop to take a first pass at the data returned from query */
$data_array = array();
if ( ! empty( $dataset ) ) {
foreach ( $dataset as $key => $value ) {
if ( is_serialized( $value->meta_value ) ) {
$temp_array = unserialize( $value->meta_value );
foreach( $temp_array as $provider_record ) {
$data_array[] = strval( $provider_record );
}
} else {
$data_array[] = strval( $value->meta_value );
}
}
/* sort and dedup the resulting array */
asort( $data_array );
$unique_array = array_unique( $data_array );
/* loop formats the array into working code */
$ctr = 0;
foreach ( $unique_array as $value ) {
if ( $use_in_operator ) {
$query_meta .= "'" . strval( $value ) . "',";
} else {
/* this is where we build the where filter for locations and courses */
if ($ctr == 0 ) {
$query_meta .= " AND ( ( meta_value like '%" . strval( $value ) . "%' )";
} else {
$query_meta .= " OR ( meta_value like '%" . strval( $value ) . "%' )" ; }
$ctr++;
}
}
/* finalize the working code */
if ( $use_in_operator ) {
$query_meta = "(" . substr($query_meta, 0, strlen($query_meta)-1) . ")";
} else {
$query_meta .= " ) )" ;
}
}
return $query_meta;
}
<h3>BEST MATCHES BY LOCATION:</h3>
[foreach 27 minimize="1"][frm-set-get provider="[74]"]
[display-frm-data id=826 filter=limited]
[/foreach]
a:3:{i:0;s:3:"252";i:1;s:3:"253";i:2;s:3:"254";}
<?php
add_filter('frm_where_filter', 'custom_view_where_clause', 10, 2);
function custom_view_where_clause( $where, $args ) {
global $wpdb;
/* create the variables for the formidable items and item metas tables */
$wpdb_prefix = $wpdb->prefix;
$frm_items_table = $wpdb_prefix . 'frm_items';
$frm_item_metas_table = $wpdb_prefix . 'frm_item_metas';
/* we'll use $view_id to drive the switch statement below
* why a switch statement? switch statements provide better performance than ifs
*/
$view_id = $args['display']->ID;
/* note: we use keys to identify fields so code is transportable across WordPress isntances
* if we use IDs, the code can break because IDs change when forms are imported
* We still need to convert the keys to IDs for the SQL to work
*/
/* get the id for the fair partner form repeater */
$virtual_fair_repeater_key = "fair_partner_selection";
$virtual_fair_repeater_id = FrmField::get_id_by_key( $virtual_fair_repeater_key );
return $where;
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment