Query Not Returning As Expected

[jarvis]

Hi,

I think I'm missing the obvious, so turned to here for some assistance! I have the following query:

		SELECT DISTINCT a1.user_id
		FROM $wpdb->usermeta a1
		INNER JOIN $wpdb->usermeta a2 ON a1.user_id = a2.user_id
		INNER JOIN $wpdb->usermeta a3 ON a1.user_id = a3.user_id
		INNER JOIN $wpdb->usermeta a4 ON a1.user_id = a4.user_id
		INNER JOIN $wpdb->usermeta a5 ON a1.user_id = a5.user_id
		WHERE (
			a1.meta_key = 'q1'
			AND a1.meta_value REGEXP '$q1_regex'
		)
		OR (
			a2.meta_key = 'q2'
			AND a2.meta_value REGEXP '$q2_regex'
		)
		OR (
			a3.meta_key = 'q3'
			AND a3.meta_value REGEXP '$q3_regex'
		)
		AND (
			a4.meta_key = '$siteID'
			AND a4.meta_value NOT LIKE 'a:1:{s:12:\"shop_manager\";b:1;}'
		)
		AND (
			a5.meta_key = '$siteID'
			AND a5.meta_value = 'a:1:{s:10:\"subscriber\";b:1;}'
		)

It returns results (which is a good start). However, it also returns some results that shouldn't be there.

 

In this example. $siteID =   'table_99_capabilities';

 

For example. It returns the following user IDs

61

64

65

 

Yet I know the following:

61 = is a customer, so fails on the last part of the query (AND a5.meta_value = 'a:1:{s:10:\"subscriber\";b:1;}')

64 & 65 fail as they have a siteID of table_66_capabilities

 

I'm hoping this is enough info and clearly explained 

 

Any help is much appreciated

 

Thanks for your time

[requinix]

Searching through serialized data... ugh.

 

 

AND has higher precedence than OR, just like how multiplication has higher mathematical precedence than addition. That means writing

a OR b AND c
2 + 4 * 5

is equivalent to

a OR (b AND c)
2 + (4 * 5)

Applying that to your query we discover you've effectively written

matches q1 OR matches q2 OR (matches q3 AND is not shop manager AND is subscriber)

Use parentheses to force evaluation the way you want:

(matches q1 OR matches q2 OR matches q3) AND is not shop manager AND is subscriber

But I fear you may discover other problems.

[jarvis]

Thanks requinix

 

Yes, sadly it's Wordpress and stores data that way - made for an interesting day so far!

 

Ok, I will look to amend the query but last time i tried if broke it lol. It looked like this:

WHERE 		(
			a1.meta_key = '$siteID'
			AND a1.meta_value NOT LIKE 'a:1:{s:12:"shop_manager";b:1;}'
		}
		AND 
		{
			a2.meta_key = '$siteID'
			AND a2.meta_value = 'a:1:{s:10:"subscriber";b:1;}'
		)
		AND (
			a3.meta_key = 'q1'
			AND a3.meta_value REGEXP '$q1_regex'

		OR 
			a4.meta_key = 'q2'
			AND a4.meta_value REGEXP '$q2_regex'
		
		OR 
			a5.meta_key = 'q3'
			AND a5.meta_value REGEXP '$q3_regex'
		)

But think that may be wrong also!?

[requinix]

Look closer at those parentheses you thought you typed.

[jarvis]

I can't see for looking. Have I just put the AND and OR the wrong way round? But then the AND has higher priority which is why I put it first. That said, I'd it has a higher priority, then I guess it could go after the OR?

[requinix]

You have curly braces {} between a1 and a2 instead of parentheses ().

[jarvis]

Aaaaah! Gotcha!!

 

Working perfectly now - thank you. More so for not giving the code and making me do the work :-)

[benanamen]

More so for not giving the code and making me do the work :-)

 

I have a lot of respect for @requinix for that approach.