Support

Account

Register Login

Home › Forums › Add-ons › Repeater Field › Repeater Plugin – Group By Help

Solved

Repeater Plugin – Group By Help

  • I have a custom post type with a custom field group that contains information about the locations.

    The post title is the company name. Each company may have several locations in different states. The repeater field contains all of the address information.

    Ultimately what i want to be able to do is access and filter this data in multiple ways.

    The first is for the map. I want to query the database and group all of the locations by there state. Is there a way using repeater plugin to do a custom query ( perhaps using WP_QUERY) to do a mysql group by (or something similar) to get all locations showing the company name (the_title) and a count of how many locations are in each state (GROUP by repeater field state sub_field) ?

  • Hi,

    If I’ve understood then you can use the example code here.

    This will allow you to run a query based on your search criteria. You can also check out the Query posts with acf values on this page.

    As it’s the repeater, you may need to do some custom SQL work. I did something similar with the repeater and the date picker. You can see that here.

    Hopefully that will put you on track

  • I think the query i am after is a little more complicated. I am trying to do a GROUP BY on a repeater field subfield value.

    My data is setup like this:

    custom post type called “companies”.
    In the post type i have a repeater field called “Locations”.
    In the locations repeater field I have address, city, state, zipcode — all of the address information.

    I am building a map so I want to show all results grouped by state. Ideally, I’d be able to show what all states are in my repeater field and show the count of how companies are in that location.

    I’m finding it a bit tricky to group by the repeater state sub_field that I’ve created. Any ideas how to do that?

  • So may be this example may help:

    $posts = get_posts(array(
	'post_type'		=> 'event',
	'posts_per_page'	=> -1,
	'meta_query'		=> array(
		'relation' => 'OR',
		array(
			'key' => 'location',
			'value' => 'melbourne',
			'compare' => '='
		),
		array(
			'key' => 'location',
			'value' => 'sydney',
			'compare' => '='
		)
	)
));
 
if($posts)
{
	foreach($posts as $post)
	{
		// ...
	}
}

    Or maybe something like this (untested):

    $rows = $wpdb->get_results($wpdb->prepare( 
"
SELECT * 
FROM wp_postmeta
WHERE meta_key LIKE %s GROUP BY meta_key ASC",
'Locations', 			
""		
));

    I’d be inclined to look at some examples. Maybe try running the SQL in PHPMyAdmin and then convert that into the code you need.

  • $plfsByState = $wpdb->get_results("
    select pm.meta_value as state, count(*) as postcount
      from $wpdb->posts p
           join $wpdb->postmeta pm on p.ID = pm.post_id
     where p.post_type = 'plfmaps'
       and p.post_status = 'publish'
  group by state
  order by state ASC");

    This gives me way too much data…And doesnt exactly work. This basically returns everything. I’m seeing div with the address, city, state. How can i modify this query to only show the states?

    Where I am getting mixed up is the meta_key value for the location_state actually looks like this in the database table “locations_0_state” and “locations_1_state” — any ideas on this one? I feel like I am really close, but I’m missing something.

  • I had similar.

    If you look at the below:

    $rows = $wpdb->get_results($wpdb->prepare( 
"
SELECT * 
FROM wp_postmeta
WHERE meta_key LIKE %s AND meta_value LIKE %s GROUP BY meta_value ASC",
'dates_%_available_dates', ''.$year.''.$month.'%', 			
""		
));

    You can see that dates_%_available_dates is the repeater field as it went:
    dates_0_available_dates
    dates_1_available_dates
    dates_2_available_dates
    etc

    Using the % allows you to be more dynamic, as you can search on all with the LIKE statement

    This is the link I used:

    It may help you with your code. I most certainly think rewriting your query to something like my example or the one in the link will help though

  • Thanks jarvis. I probably can’t use the first query because then i would have specify each state in the query and we will be constantly adding new company locations, so i kind of want it to work a bit organically.

    The second one, i am going to try that out now.

  • hmm…let me try that last one out…let me see what i can get.

  • I realised the link didn’t show:
    http://www.advancedcustomfields.com/resources/tutorials/querying-the-database-for-repeater-sub-field-values/

  • You can then loop to get all the states and then loop within that to show the info you need – or something like that anyway :-/

  • Does this look correct? I’m getting good results with it. So, i think i got it.

    $plfsByState = $wpdb->get_results($wpdb->prepare( 
"
SELECT pm.meta_value as state, count(*) as postcount 
FROM $wpdb->posts p
			join $wpdb->postmeta pm on p.ID = pm.post_id
where p.post_type = 'plfmaps'
and p.post_status = 'publish'
AND meta_key LIKE %s 
AND meta_value LIKE %s 
GROUP BY meta_value ASC",
'locations_%_state',
'%',
""		
));

    Thanks for your help. This appears to be showing all of the states, grouped by the state and then showing a count of the total number of results from that state. This is a little tricky with those % signs.

    Question about the query. At the very end, ‘locations_%_state” is the meta_key, and ‘%’ is the meta_value, so then what is the “” at the very end? What does that do?

  • Hi,

    Glad to hear you got it working. The above code certainly looks to be on the right lines and clearly returns your result so that must be a good thing.

    The double “” is basically:
    One closing ” as the opening one is on line 2 and the other is the closing ” from line 10

    Cheers

Viewing 12 posts - 1 through 12 (of 12 total)

The topic ‘Repeater Plugin – Group By Help’ is closed to new replies.