Support

Account

Solved

Font-end search form for a subfield

Hello everyone !

I am facing a very demanding challenge with the ACF sub-field, maybe someone here will be able to give me some help.

There is an ACF with this structure :
-Title
-description
-picture
-subfield called “tous_lots”
–s.f.1.number // it’s a surface in m²
–s.f.2.multiple-checkboxes
–s.f.3.datepicker

What I would like to achieve is a research form on the front-end, on which we can search the information from the subfields.
I need in my search form :
– value min / value max (for the s.f.1.number)
– a date selector
– checkboxes that match the s.f.2.multiple-checkboxes

I’ve done almost everything, here is a simple version of the code (without all the if/else, just the wp Query) :

<?php

function my_posts_where( $where ) {
	
	$where = str_replace("meta_key = 'tous_lots_%", "meta_key LIKE 'tous_lots_%", $where);

	return $where;
}

add_filter('posts_where', 'my_posts_where');

// SURFACE
			
$surface = array('key' => 'tous_lots_%_surface_du_lot',
				'value'   => array( $surface_min, $surface_max ),
				'type'    => 'numeric',
				'compare' => 'BETWEEN');
// DATE
		
$date =  array('key' => 'tous_lots_%_date_de_livraison_du_lot',
			'value' => $date_du_jour,
			'compare' => '<=',
			'type' => 'DATE'); 
		

// checkbox nb 1
$non_loue = array('key' => 'tous_lots_%_etat_de_livraison', 
							'value' => '"1"', 
							'compare' => 'LIKE');

// checkbox nb 6 (I have a total of 6 checkboxes, but only 1 & 6 are in this form
$selection_cheminee = array('key' => 'tous_lots_%_etat_de_livraison', 
							'value' => '"6"',
							'compare' => 'LIKE');
			
		
$gridposts = new WP_Query( array('post_status' => 'publish',
									'post_type' => 'post',
									'category_name' => 'en_cours',
									'meta_query' => array('surface' => $surface,
														  'date' => $date,
														  'cheminee' => $selection_cheminee,
														  'non_loue' => $non_loue)) ); 
?>

“It works perfectly !!”
That’s what I thought…

The problem is that if I have in the same post one subfield which match for the date, and another subfield which for a checkbox, the post will be displayed.
It’s not what i was looking for : I would like the post to display ONLY IF all the conditions are met in ONE subfield.

Any help would be highly appreciated !

- John Huebner
- July 18, 2015 at 10:14 am
Have you looked at the information here http://www.advancedcustomfields.com/resources/query-posts-custom-fields/

Section 4 contains information about searching sub fields
- LucasG
- July 18, 2015 at 9:29 pm
Yes I have, I am using the ‘my_post_where’ function in my code to replace the ‘=’ by ‘LIKE’.

The problem is to search subfield by subfield, not “all the subfield in the post”.

(I’ve edited my first)

Searching sub field by sub field. That would be quite a complex meta query.

The first thing you’d need to know is the maximum number of rows that any post my have. So you’d need to loop through all the post once just to get that value. A example of this would be


// get all posts in the post type, then loop through them
$max_rows = 0; // will hold the maximum number of rows
$repeater = 'name_of_repeater_field'
while (have_posts()) {
    the_post();
    $count = intval(get_post_meta($post->ID, $repeater, true));
    if ($count > $max_rows) {
        $max_rows = count;
    }
}

// now that we know the number of rows
// dynamically generate meta query
// this uses nested meta queries introduced in WP 4.1
$meta_query = array(
    relation => 'OR'
);
$subfield1 = 'subfield1'; // name of first subfield
$subfield2 = 'subfield2'; // name of second subfield
for ($row=0; $row<$max_rows; $row++) {
    $row_query = array(
        'relation' = 'AND',
        array(
            'key' => $repeater.'_'.$row.'_'$sufield1,
            'value' => 'value to search for'
        ),
        array(
            'key' => $repeater.'_'.$row.'_'$sufield2,
            'value' => 'value to search for'
        ),
    );
    $meta_query[] = $row_query;
}

Hope that helps

- LucasG
- July 20, 2015 at 7:23 pm
Hello, yes this helps me a lot !! I’ll give it a try asap.

I just have a concern : what if the $max_rows is 10 (for example) and I have only 2 or 3 rows in most of my posts ?
$repeater.’_’.$row.’_’$sufield2 would then point to something that doesn’t exist in the db, doesn’t it ?

What if I insert the “for” loop in the “while” loop ? For each post it would make the right amount of loops and I reset the $max_posts at the end of the post loop.

Thanks a lot, I will try to start with your idea 🙂

Okay so I’ve tried your code, it theoretically works!

But the query takes very long and I eventually get an error (502 bad gateaway or error 500 internal server error)

Here is my query (it is in an array and I use that array to make the WP_Query) :

I know it’s long, and for now the $max_rows is just 4… it will be around 20 when all the data will be in the DB.

Looking at the array, it seems it should work…

Here is my PHP (added with my first post code):


function replace_row($array, $row) {
	if($array != '') {
		$array = str_replace('_%_', '_'.$row.'_', $array);
	}
	return $array;
}

$repeater_query = array(relation => 'OR');
		
		while ($countposts->have_posts()) {
			$countposts->the_post();
			
			 $count = intval(get_post_meta($post->ID, $repeater, true));

			if ($count > $max_rows) {
				$max_rows = $count;
			}
		}
		
		
	
		for ($row=0; $row<$max_rows; $row++) {
			
			$row_query = array('relation' => 'AND',
							   'surface' => replace_row($surface, $row),
							   'date' => replace_row($date, $row),
							   'selection_cheminee' => replace_row($selection_cheminee, $row),
							   'non_loue' => replace_row($non_loue, $row)
								);
			
			$repeater_query[] = $row_query; 

		}

EDIT : here is a clue ->
– when i set $max_rows = 1; i have no error (but the search only cover the 1st element of each repeater).
– if i set it with the value “2” it no longer works… Despite the fact that each post has 2 or more element in the repeater field

- John Huebner
- July 21, 2015 at 11:41 am
there could be something wrong with the nesting, it’s really hard for me to make out. There is a limit to how far you can nest the meta query. Nesting further than the example given in the codex will cause a timeout. I think there is an infinite loop or something else going on. http://codex.wordpress.org/Class_Reference/WP_Query

If you’re nesting is too deep and you can’t reduce the depth, or if there’s just too much for WP to handle the you’ll need to go directly to the database with wbdb https://codex.wordpress.org/Class_Reference/wpdb

Hello @hube2

Many thanks, I’ve tried this.
I can see how close I am to the solution, but it’s just out of reach with the knowledges I have.
It’s the first time I use the $wpdb method and I’m not sure what i am doing wrong (again !).

So I dynamically make the SQL request to have something like this in a var:

SELECT *
FROM wp_posts
INNER JOIN wp_term_relationships ON (wp_posts.ID = wp_term_relationships.object_id)
INNER JOIN wp_postmeta ON (wp_posts.ID = wp_postmeta.post_id)
INNER JOIN wp_postmeta AS mt1 ON (wp_posts.ID = mt1.post_id)
INNER JOIN wp_postmeta AS mt2 ON (wp_posts.ID = mt2.post_id)
INNER JOIN wp_postmeta AS mt3 ON (wp_posts.ID = mt3.post_id)
INNER JOIN wp_postmeta AS mt4 ON (wp_posts.ID = mt4.post_id)
WHERE 1=1
  AND (((mt3.meta_key = 'tous_lots_0_surface_du_lot'
         AND CAST(mt3.meta_value AS SIGNED) BETWEEN '110.5' AND '155.25')
        AND (mt4.meta_key = 'tous_lots_0_etat_de_livraison'
             AND CAST(mt4.meta_value AS CHAR) LIKE '%"1-loc"%'))
       OR ((mt3.meta_key = 'tous_lots_1_surface_du_lot'
            AND CAST(mt3.meta_value AS SIGNED) BETWEEN '110.5' AND '155.25')
           AND (mt4.meta_key = 'tous_lots_1_etat_de_livraison'
                AND CAST(mt4.meta_value AS CHAR) LIKE '%"1-loc"%'))
       OR ((mt3.meta_key = 'tous_lots_2_surface_du_lot'
            AND CAST(mt3.meta_value AS SIGNED) BETWEEN '110.5' AND '155.25')
           AND (mt4.meta_key = 'tous_lots_2_etat_de_livraison'
                AND CAST(mt4.meta_value AS CHAR) LIKE '%"1-loc"%'))
       OR (etc...)
  AND (wp_term_relationships.term_taxonomy_id IN (10))
  AND (wp_postmeta.meta_key = 'dept_nb_for_the_order'
       AND mt1.meta_key = 'city_name_for_the_order'
       AND mt2.meta_key = 'arrondissement')
  AND post_status = 'publish'
  AND wp_posts.post_type = 'post'
GROUP BY wp_posts.ID
ORDER BY CAST(wp_postmeta.meta_value AS CHAR) ASC, CAST(mt1.meta_value AS CHAR) ASC, CAST(mt2.meta_value AS CHAR) ASC LIMIT 0,
                                                                                                                                10

Then I send the query and loop through the results.
It almost works !!!

I think I just have a problem with LIKE ‘%”1-loc”%’
Even if this string isn’t in the db, the result shows up.

Thanks a lot for your help, I almost reach the end 🙂

- John Huebner
- July 23, 2015 at 2:48 pm
try escaping the ” in '%\"1-loc\"%' That’s about the only thing I can see or think of.
- LucasG
- July 28, 2015 at 1:07 am
Thanks @hube2

Sorry for the delay, I’ve had a big DB error for several days…

I’ve tried to escape but it doesn’t work. Maybe with the prepare method, I’ll try to figure it out.

Just before setting this topic on solve, I have one last question : what if this query takes too long and generate an error ? My website has been down for 4 days, and the host said it was due to a “never ending SQL request”…

Is there a way to secure this query ?
What I’ve done for now is to set a max for the $max_rows var (if it’s above 35 the loop won’t start).
If you have any idea they are more than welcome 🙂

Thanks a lot again for all your help on this

Lucas
- John Huebner
- July 28, 2015 at 8:14 am
I’ve never seen a “never ending SQL request” My only guess there is that you’ve somehow created an infinite loop in the SQL syntax, something else that I don’t recall ever seeing. My only guess is that’s is cause where you’re trying to match rows.

I dislike giving up, but I think you’ve gone further than my ability to help, and maybe you’re trying to do something that WP can’t do, but I can’t say.

More than likely, if this was something that I had to complete I would give up on doing it with a query at this point. I’d instead look at loading all of the data into a multidimensional array, or possibly multiple arrays, and then use PHP to build something that could do the searching.

I’m really sorry I can’t help more.
- LucasG
- July 28, 2015 at 7:01 pm
Okay, I’ve asked my host to tell me (if this happens again) what was the responsible query.

About the LIKE in the SQL, here was a solution : double the %
```
$loc = '"what im looking for"';
$posts_query = $wpdb->get_results("...... NOT LIKE '%%$loc%%' ....);
```
Again, many thanks !! It works perfectly (see here it’s a temporal link while the site is under construction) 🙂

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

The topic ‘Font-end search form for a subfield’ is closed to new replies.

Advanced Custom Fields

Support

Account

Font-end search form for a subfield

LucasG

John Huebner

LucasG

John Huebner

LucasG

LucasG

John Huebner

LucasG

John Huebner

LucasG

John Huebner

LucasG

Welcome

Forums

Helping Others

About

Help

Community