Order By Sub Field Value with SQL

• ashley

• July 23, 2015 at 7:24 pm

I’ve followed this guide:

http://www.advancedcustomfields.com/resources/querying-the-database-for-repeater-sub-field-values/

To get to here:

<?php

				$field = get_field_object('field_55ae5fd80d08d');

				if( $field )
{
		foreach( $field['choices'] as $k => $v )
		{ ?>
			<h3><?php echo $v; ?></h3>
			<table><tr><th></th><th>Score</th><th>Name</th><th>School</th></tr>
							<?php // get all rows from the postmeta table where the sub_field (type) equals $k
				// - http://codex.wordpress.org/Class_Reference/wpdb#SELECT_Generic_Results
			        $rows = $wpdb->get_results($wpdb->prepare(
			            "
			            SELECT *
			            FROM {$wpdb->prefix}postmeta
			            WHERE meta_key LIKE %s
			                AND meta_value = %s
			            ",
			            'results_%_technique', // meta_name: $ParentName_$RowNumber_$ChildName
			            $k  // meta_value
			        ));

				// loop through the results
				if( $rows )
				{

					$rank = 1;

					foreach( $rows as $row )
					{
						// for each result, find the 'repeater row number' and use it to load the sub fields
						preg_match('_([0-9]+)_', $row->meta_key, $matches);
						$technique_meta_key = 'results_' . $matches[0] . '_technique'; // $matches[0] contains the row number!
						$score_meta_key = 'results_' . $matches[0] . '_score'; // $matches[0] contains the row number!
						$first_name_meta_key = 'results_' . $matches[0] . '_first_name'; // $matches[0] contains the row number!
						$last_name_meta_key = 'results_' . $matches[0] . '_last_name'; // $matches[0] contains the row number!
						$age_meta_key = 'results_' . $matches[0] . '_age'; // $matches[0] contains the row number!
						$second_first_name_meta_key = 'results_' . $matches[0] . '_second_first_name'; // $matches[0] contains the row number!
						$second_last_name_meta_key = 'results_' . $matches[0] . '_second_last_name'; // $matches[0] contains the row number!
						$second_age_meta_key = 'results_' . $matches[0] . '_second_age'; // $matches[0] contains the row number!
						$school_meta_key = 'results_' . $matches[0] . '_school'; // $matches[0] contains the row number!
						$location = get_the_terms( $school[0], 'location' );

						//  use get_post_meta to load the sub fields
						// - http://codex.wordpress.org/Function_Reference/get_post_meta
						$technique = get_post_meta( $row->post_id, $technique_meta_key, true );
						$score = get_post_meta( $row->post_id, $score_meta_key, true );
						$first_name = get_post_meta( $row->post_id, $first_name_meta_key, true );
						$last_name = get_post_meta( $row->post_id, $last_name_meta_key, true );
						$age = get_post_meta( $row->post_id, $age_meta_key, true );
						$second_first_name = get_post_meta( $row->post_id, $second_first_name_meta_key, true );
						$second_last_name = get_post_meta( $row->post_id, $second_last_name_meta_key, true );
						$second_age = get_post_meta( $row->post_id, $second_age_meta_key, true );
						$school = get_post_meta( $row->post_id, $school_meta_key, true );
						$terms = get_the_terms( $school[0], 'location' );

						?>
						<tr>
							<td>
									<?php echo $rank++; ?>
							</td>
							<td>
									<?php echo $score; ?>
							</td>
							<td>
									<?php echo $first_name . ' ' . $last_name; ?>
									<?php if ( $technique == 'face-to-face') : echo ' & ' . $second_first_name . ' ' . $second_last_name; ?>
										<?php endif; ?>
							</td>
							<td>
									<a href="<?php echo get_the_permalink( $school[0] ); ?>">
									<?php echo get_the_title( $school[0] ); ?></a>,

									<?php if ( $terms && ! is_wp_error( $terms ) ) :

										$location_links = array();

										foreach ( $terms as $term ) {
											$location_links[] = $term->name;
											$term_link = get_term_link( $term );
										}

										$location = join( ", ", $location_links );
									?>

									<?php
									echo '<a href="' . esc_url( $term_link ) . '">' . $term->name . '</a>';
									?>

									<?php endif; ?>
							</td>
						</tr>
						<?php

					}
				}
				echo '</table>';
		}
}

?>

However I need to order the results by score, looking around it looks like Inner Join might be the answer, but everything I do breaks the query.

Any help would be appreciated.

• John Huebner

• August 5, 2015 at 8:38 am

The problem is that with the way you’re doing the query, which is really the only way to do it with repeater sub fields, I don’t thing that there’s a way to match up, for example results_0_technique with results_0_score.

I think that if you need to sort by the score field that you’re going to need to do that sorting using PHP.

You could loop through the rows as you’re doing and then assign the values to a multidimensional array, the you could build a php usort function to sort the array.

This is just an example and it has not been tested.

// example array with a couple of the fields
$results = array();
foreach ($rows as $row) {
    $results[] = array (
        'technique ' => get_post_meta($row->post_id, $technique_meta_key, true)
        'score ' => get_post_meta($row->post_id, $score_meta_key, true);
    );
} // end foreach
usort($results, 'sort_results');

    
function sort_results($a, $b) {
    if ($a['score'] == $b['score']) {
        return 0;
    } elseif ($a['score'] < $b['score']) {
        return -1;
    } else {
        return 1;
    }
} // end function