Query statement has WHERE 1=1 instead of wp_postmeta.meta_value=1.

[laiconsulting]

The WP_Query statement is interpreted to have WHERE 1=1 instead of wp_postmeta.meta_value=1. The result is that all posts are matched instead of those that satisfy the geospatial constraint.

[andrewminion-luminfire]

@laiconsulting I’ve seen that WHERE 1=1 condition in quite a few WP queries, even on sites not using this plugin.

What’s the PHP code you’re using and the full SQL query it generates?

[laiconsulting]

@andrewminion-luminfire

        $arr_query = array(
                'relation' => 'AND',
                "meta_query" => array(
                        array(
                                "key" => "geom_line2__",
                                "compare_key" => "LIKE",
                                "compare" => "ST_INTERSECTS",
                                "value" => $geom_poly
                        ),
                        array(
                                'key' => 'geom_point',
                                'compare' => 'EXISTS'
                        ),
                ),
                'posts_per_page' => -1,
                'post_type' => 'post',
                'post_status' => 'publish',
                'order' => 'DESC',
                'orderby' => 'date',
        );
 $withinbox_query = @(new WP_Query( $arr_query)); 

The SQL query is

 SELECT   wp_10_posts.*
                        FROM wp_10_posts  INNER JOIN ( SELECT meta_id, post_id, meta_key, ST_INTERSECTS( meta_value,ST_GeomFromText( 'MULTIPOLYGON (((5.3626552303716 42.903086695485, 14.324314214076 42.903086695485, 14.324314214076 46.914380072658, 5.3626552303716 46.914380072658, 5.3626552303716 42.903086695485)))', 4326, 'axis-order=long-lat' ) ) AS meta_value FROM wp_10_postmeta_geo ) AS wp_10_postmeta ON ( wp_10_posts.ID = wp_10_postmeta.post_id )  INNER JOIN wp_10_postmeta AS mt1 ON ( wp_10_posts.ID = mt1.post_id )
                        WHERE 1=1  AND ( 
  wp_10_postmeta.meta_key LIKE '%geom\\_line2\\_\\_%' 
  AND 
  mt1.meta_key = 'geom_point'
) AND wp_10_posts.post_type = 'post' AND ((wp_10_posts.post_status = 'publish'))
                        GROUP BY wp_10_posts.ID
                        ORDER BY wp_10_posts.post_date DESC

The only thing missing in the WHERE clause WHERE wp_10_postmeta.meta_value = 1.
You are right that the 1=1 is always there.
Perhaps after there, I need an extra clause like "AND $metatable.meta_value = 1 " . $clauses['where']

ps. It is a multisite setup.

[andrewminion-luminfire]

@laiconsulting it might be the location of the relation key; try this:

$arr_query = array(
-    'relation' => 'AND',
    "meta_query" => array(
+        'relation' => 'AND',
            array(
                    "key" => "geom_line2__",
                    "compare_key" => "LIKE",
                    "compare" => "ST_INTERSECTS",
                    "value" => $geom_poly
            ),
            array(
                    'key' => 'geom_point',
                    'compare' => 'EXISTS'
            ),
    ),
    'posts_per_page' => -1,
    'post_type' => 'post',
    'post_status' => 'publish',
    'order' => 'DESC',
    'orderby' => 'date',
);
$withinbox_query = @(new WP_Query( $arr_query)); 

[laiconsulting]

@andrewminion-luminfire Thanks for the suggestion.
Tried it just now. No dice unfortunately...

Looking at the SQL query, the ST_INTERSECTS turns the geometa_value into boolean, so it is natural to require a WHERE clause to select the 1 from the meta_value column. What do you think?

[andrewminion-luminfire]

@laiconsulting please take a look at the usage notes here for an example of how to use ST_INTERSECTS with a meta query: https://github.com/BrilliantPlugins/wp-geometa-lib/blob/master/docs/USAGE.md#querying

[laiconsulting]

@andrewminion-luminfire If your concern is the compare_key, I have tried without it. It still does not change the result. The SQL query came out the same structure, namely ST_INTERSECTS turns the geo-meta_value field into a boolean, regardless of key or other things. Then the compare_key turns into a WHERE clause that matches the key with either the default = or in my case LIKE. However, in order to select the relevant rows based on geospatial column, an extra WHERE is needed to pick up the effect of ST_INTERSECTS, as far as I understand.

Or do you mean something different when you meant taking a look at the example?

[andrewminion-luminfire]

Yes, I meant without the compare_key. Is $geom_poly a GeoJSON string or is it this? ST_GeomFromText( 'MULTIPOLYGON (((5.3626552303716 42.903086695485, 14.324314214076 42.903086695485, 14.324314214076 46.914380072658, 5.3626552303716 46.914380072658, 5.3626552303716 42.903086695485)))', 4326, 'axis-order=long-lat' ) )

[laiconsulting]

@andrewminion-luminfire Yes. $geom_poly is geoJSON. In fact, ST_INTERSECTS performed as it should, as I mentioned turning meta_value into boolean. If I select the rows with 1 and ignore the rows with 0, the result is precise the desired result.

[andrewminion-luminfire]

Looks like this line should probably be checking that meta_value but I’m not sure why it isn’t.

Maybe define( 'WP_GEOMETA_DEBUG', true ); to enable debugging and use xdebug or another debugging tool to inspect the code around that area? 🤷🏻‍♂️

[stuporglue]

@andrewminion-luminfire The WHERE 1=1 piece is used so that all additional conditions can simply be appended with an "AND xxxx", "AND xxxx". Since there's already a known true value at the start, there's no need to figure out if we need to add an "AND" at the start of the next condition. This is especially useful when conditions are appended conditionally.

[laiconsulting]

Ok, I figured that out. The problem was 2 folds.

1. My $geom_poly was geoJSON, but as @andrewminion-luminfire pointed out, it ought to be geoJSON string. So I had to wrap it with json_encode($geom_poly)
2. My "compare_key"=>"LIKE" did cause problem, because of how $std_query is defined for str_replace()

I needed this line, but it could not get into the WHERE cause because meta_value did not match due to 1. above; meta_keydid not match due to 2. above.
I propose the following fix that drops the $metatable.meta_key part of $std_query. It allows "compare_key"=>"LIKE" and only applies when WP_GeoUtil::metaval_to_geom( $meta_value, true ) is non-empty

@@ -223,15 +223,16 @@
 				$new_meta_value = "{$meta_query['compare']}( meta_value,ST_GeomFromText( %s, %d, 'axis-order=long-lat' ) )";
 				$new_meta_value = $wpdb->prepare( $new_meta_value, array( $geometry, WP_GeoUtil::get_srid() ) ); // @codingStandardsIgnoreLine
 
-				$std_query = "( $metatable.meta_key = %s AND CAST($metatable.meta_value AS $meta_type) = %s )";
-				$std_queries[] = $wpdb->prepare( $std_query, array( $meta_query['key'], $meta_query['value'] ) ); // @codingStandardsIgnoreLine
+				$std_query = " CAST($metatable.meta_value AS $meta_type) = %s ";
+				$std_queries[] = $wpdb->prepare( $std_query, array( $meta_query['value'] ) ); // @codingStandardsIgnoreLine
 
 				// In WP 4.6 and above CHAR values aren't cast anymore, so we do a replace on both versions
 				// so that we maintain 4.5.x capabilities too.
 				if ( 'CHAR' === $meta_type ) {
-					$std_query = "( $metatable.meta_key = %s AND $metatable.meta_value = %s )";
-					$std_queries[] = $wpdb->prepare( $std_query, array( $meta_query['key'], $meta_query['value'] ) ); // @codingStandardsIgnoreLine
+					$std_query = " $metatable.meta_value = %s ";
+					$std_queries[] = $wpdb->prepare( $std_query, array( $meta_query['value'] ) ); // @codingStandardsIgnoreLine
 				}
+				$geom_query = $wpdb->prepare( " $metatable.meta_value " ); // @codingStandardsIgnoreLine
 			} else {
 				// If we don't have a value, then our subquery gets written without parenthesis wraps.
 				// IDK why.
@@ -239,11 +240,11 @@
 
 				$std_query = "  $metatable.meta_key = %s";
 				$std_queries[] = $wpdb->prepare( $std_query, array( $meta_query['key'] ) ); // @codingStandardsIgnoreLine
-			}
 
-			// Our geom_query will be against our aliased meta table so we just need to check for boolean true.
-			$geom_query = "( $metatable.meta_key = %s AND $metatable.meta_value )";
-			$geom_query = $wpdb->prepare( $geom_query, array( $meta_query['key'] ) ); // @codingStandardsIgnoreLine
+				// Our geom_query will be against our aliased meta table so we just need to check for boolean true.
+				$geom_query = "( $metatable.meta_key = %s AND $metatable.meta_value )";
+				$geom_query = $wpdb->prepare( $geom_query, array( $meta_query['key'] ) ); // @codingStandardsIgnoreLine
+			}
 
 			$this->make_join_spatial( $clauses,$meta_query,$type,$primary_table,$primary_id_column,$context, $metatable, $geotable, $id_column, $new_meta_value );

[andrewminion-luminfire]

@laiconsulting would you mind submitting that as a PR?