returning values from repeater fields filtered by another custom field

• silverdarling

• September 24, 2013 at 9:21 pm

hi, great plugin, well worth the money
but i’m stuck here and probably missing something basic —

I want to
get all posts of post type $x
where ‘ACF standard select field’ = ‘ACF standard select field value’

then loop through specific repeater fields for each of the posts above to total the values and divide values by number of the repeater fields to get an average value.

so i have 2 field groups attached to each post, a ‘standard select field’ (assessor) and repeater field (values_and_outcomes)

The code below returns an average for ALL the repeater subfields in a repeater fieldgroup for ALL posts of post_type x. I need the average for a specific subfield (radio) for posts that have specific ‘ACF standard select field value’.

Problem code –`function get_average( $fieldname, $posttype, $assessor) {

$posts = get_posts(array(
'numberposts' => -1,
'post_type' => $posttype,
'meta_key' => 'assessor',
'meta_value' => $assessor
));

$total = 0;
$counter = 0;

if($posts)
{
foreach($posts as $post)
{
if(get_field('values_and_outcomes'))
{
while(has_sub_field('values_and_outcomes'))
{
$total = $total + get_sub_field($fieldname);
$counter++;
}
}
}
}

$paverage = $total / $counter;
$average = round($paverage, 1);
return $average;
} `

edit (NOT) working code that should return average for ALL posts of $posttype

function posttype_q_average( $fieldname, $posttype) {

$posts = get_posts(array(
	'numberposts' => -1,
	'post_type' => $posttype

));

$total = 0;
$counter = 0;

if($posts)
	{
	 
		foreach($posts as $post)
		{
if(get_field('values_and_outcomes'))
{
 
	while(has_sub_field('values_and_outcomes'))
	{
		$total = $total + get_sub_field($fieldname);
		$counter++;
	}
}

	}
$paverage = $total / $counter;
$average = round($paverage, 1);	
	}
return $average;
}

any pointers or insights would be much appreciated.

cheers
ian

• silverdarling

• September 25, 2013 at 1:14 am

seems i have to do this using $wpdb …

//average for all of post_type by assessor

function q_average_assessor( $fieldname, $posttype, $assessor) {

global $wpdb;
$repeaterfieldname = 'values_and_outcomes_%_' . $fieldname;

 
	// get all rows from the postmeta table where post_type = $postype AND has a repeater subfield of $fieldname
	// - http://codex.wordpress.org/Class_Reference/wpdb#SELECT_Generic_Results
        $rows = $wpdb->get_results($wpdb->prepare( 
            "
            SELECT * 
            FROM wp_postmeta
            INNER JOIN wp_posts ON ( wp_posts.ID = wp_postmeta.post_id ) 
            WHERE wp_posts.post_type=%s
            AND wp_postmeta.meta_key LIKE %s

            
            ",
            $posttype,
            $repeaterfieldname
        ));
    
     $coreval = 0;
     $counter = 0;
	// loop through the results
	if( $rows )
	{
		foreach( $rows as $row )
		{
			// for each result, find the 'repeater row number' and use it to load the image sub field!
            preg_match('_([0-9]+)_', $row->meta_key, $matches);
			$meta_key = 'values_and_outcomes_' . $matches[0] . '_' . $fieldname; // $matches[0] contains the row number!
 
			//  use get_post_meta 
			$thisassessor = get_post_meta( $row->post_id, 'assessor', true );
			if($thisassessor == $assessor) {
	         $counter++;
	         $coreval = $coreval + get_post_meta( $row->post_id, $meta_key, true );
            }

 
		}
	}
$paverage = $coreval / $counter;
$average = round($paverage, 1);
return $average;
}

but means a lot of my previous code is wrong. assumption is that for a query on an archive page (ie involving more than one post and repeater fields) get_posts() doesn’t work but $wpdb does?

• Elliot

• September 25, 2013 at 12:48 pm

Hi @silverdarling

Sorry mate, this doesn’t sound like a bug or issue with ACF, but a general ‘how to’ question regarding WP Dev.

Unfortunately, this forum is only available for bugs / issues / simple questions. The above question will take quite a while for me to understand and then solve for you which I don’t have the time at the moment.

If you have any issues regarding functions, please do ask away, but I can’t be of much help in this situation.

Thanks for understanding

Cheers
E

• silverdarling

• September 25, 2013 at 6:54 pm

ok, fair enough.

what i do want to be sure of is –

can i use get_posts with repeater subfields when working with more than one post?

examples: http://www.advancedcustomfields.com/resources/getting-started/code-examples/#working-with%20the%20repeater%20field

http://www.advancedcustomfields.com/resources/getting-started/code-examples/#query-posts%20with%20acf%20values

OR do i HAVE to use $wpdb
similar to this example:
http://www.advancedcustomfields.com/resources/tutorials/querying-the-database-for-repeater-sub-field-values/

thanks

• Elliot

• September 27, 2013 at 3:02 pm

Hi @silverdarling

When working with sub fields, you will need to use the approach shown in the http://www.advancedcustomfields.com/resources/tutorials/querying-the-database-for-repeater-sub-field-values/ tutorial.

This is because the field_name saved in the dataabse requires a LIKE lookup due to the changing row number in each name.

Hope that makes sense mate.

Thanks
E