Support

Home Forums Event Espresso Premium Extra Meta Key Query not working

Extra Meta Key Query not working

Posted: December 5, 2018 at 3:08 pm


ARAGATO

December 5, 2018 at 3:08 pm

A follow up on this thread:
https://eventespresso.com/topic/query-question/

This is my where statement:
'Extra_Meta.EXM_key' => array( '!=', 'acms_registration_processed' ),
'Extra_Meta.EXM_value' => array( '!=', true ),

Alternatively:
'NOT' => array( 'Extra_Meta.EXM_key' => 'acms_registration_processed', 'Extra_Meta.EXM_value' => true )

This is my DB:
http://uploads.aragato-server.net/screenshots/201812050bd434e88c.png

This is my query result:
http://uploads.aragato-server.net/screenshots/201812059e16aa8548.png

From my understanding, registration with ID 90 should not be returned as a result with those where statements. But it is. Why?


Josh

  • Support Staff

December 5, 2018 at 8:02 pm

To get results without including registration ID 90, you could do:

'Extra_Meta.EXM_key' => 'acms_registration_processed',
'Extra_Meta.EXM_value' => array( '!=', true )

but, you might want results including registrations that don’t have the extra meta key/value set at all. So instead you could build out your queries like this:

$where_params_meta =
array(
	array(
		'Extra_Meta.EXM_key' => 'acms_registration_processed',
		'Extra_Meta.EXM_value' => true,
	)
);
// get the reg ID's of the regs that have 
// acms_registration_processed
// key value pair in extra meta
$proc_regs = EEM_Registration::instance()->get_all($where_params_meta);
foreach( $proc_regs as $reg) {
	$proc_array[]=$reg->ID();
}
// now run the query
$registrations = EEM_Registration::instance()->get_all(
	array(
		array(
			'STS_ID' => 'RAP', 
			'OR' => array( 
			'REG_final_price' => 0,	
			'REG_paid' => array( '<', 'REG_final_price', true )
			),
			'REG_ID' => array('NOT IN', $proc_array)
		)
	) 
);


ARAGATO

December 6, 2018 at 2:58 am

Yes, indeed. I would like to get the rows where the key is not set at all.
In your example, you are running two querries in sequence. Isn’t that bad habit performance wise? Isn’t it possible to have it in one query?

Isn’t there an SQL command/EEM command that just says: “Get me all the results where metakey “xyz” is not set”?


ARAGATO

December 6, 2018 at 3:33 am

I also still do not understand the query logic:
For example this where statement:

'OR' => array( 'Transaction.STS_ID' => 'TCM', 'Transaction.STS_ID*1' => 'TIN' ),
'OR' => array( 'REG_final_price' => 0, 'REG_paid' => array( '<', 'REG_final_price', true ) ),
'OR' => array( 'REG_final_price*1' => 0, 'Transaction.Payment_Method.PMD_slug' => 'acms_addon_payment_methods_sepa' )

From my understand it reads like:
Give me all registrations WHERE it’s transaction is either TCM or TIN …AND… WHERE it’s reg price is either 0 or lower than the final price … AND … WHERE it’s reg price is either 0 or it’s transaction has the payment method acms_addon_payment_methods_sepa.

Why do I get registrations in the result that have for example final price of 10 and reg paid is also 10 That means the second OR Statement did not work. Why is that.


ARAGATO

December 6, 2018 at 3:47 am

Same with this one:
‘OR’ => array( ‘Transaction.PMD_ID’ => 17, ‘Transaction.PMD_ID*1’ => array( ‘IS NULL’ ) ),
‘OR’ => array( ‘Transaction.STS_ID’ => ‘TCM’, ‘Transaction.STS_ID*1’ => ‘TIN’ )
‘OR’ => array( ‘REG_final_price’ => 0, ‘REG_paid’ => array( ‘<‘, ‘REG_final_price’, true ) ),

Why do I get registrations that have transaction with PMD_ID = 12? Why does first statement not work?


Tony

  • Support Staff

December 6, 2018 at 4:29 am

Isn’t that bad habit performance wise?

Depends entirely on the query.

Isn’t it possible to have it in one query?

Sure there is, you’d use a JOIN within your SQL statement (although you can also do it other ways, there’s always multiple ways)

JOINS themselves can be expensive, just as much as an additional query can be (if not more) but again it depends on the query so if you’re looking at that level of optimization you’d need to try both and benchmark each solution.

Isn’t there an SQL command/EEM command that just says: “Get me all the results where metakey “xyz” is not set”?

In either of those cases, you’ll be doing one of the two options above.

Is there an SQL command, sure there is, but without this coming across the wrong way, if you are asking if there is one you’ll need to some more research as it’s not something we can walk you through.

Regarding an EEM command, the models are there to help create the queries for you and join the tables required for the data needed without you needing to do all of it yourself. If you know the SQL command you want to run it’s usually fairly easy to translate into something within the models but the models are not magic.

Give me all registrations WHERE it’s transaction is either TCM or TIN …AND… WHERE it’s reg price is either 0 or lower than the final price … AND … WHERE it’s reg price is either 0 or it’s transaction has the payment method acms_addon_payment_methods_sepa.

That’s incorrect because you are overwriting the ‘OR’ key, the only ‘OR’ that applies above is the last one:

'OR' => array( 'REG_final_price*1' => 0, 'Transaction.Payment_Method.PMD_slug' => 'acms_addon_payment_methods_sepa' )

Your creating an array and setting the keys of the array to various values for the models to work through, in your example your setting the key ‘OR’ multiple times meaning only the ‘last’ sticks. Heres a simple example:

$test = array(
	'OR' => 'Tony',
	'OR' => 'Dave',
	'OR' => 'Peter'
);

var_dump($test);

What will var_dump output for that array?

Its array(1) { ["OR"]=> string(5) "Peter" }

Why? Because it’s the same as doing this:

$test = array( 'OR' => 'Tony' );

$test['OR'] = 'Dave';
$test['OR'] = 'Peter';

var_dump($test);

Meaning you are replacing the value of ‘OR’ each time.

The models are designed to handle this by adding *{anything} to the key (which you seem to be doing in the wrong places), you just have to make sure the keys are unique.

So for the above you could use OR, OR*, OR**, or OR, OR*2nd, OR*3rd

So like this:

$registrations = EEM_Registration::instance()->get_all(
	array(
		array(
			'STS_ID' => 'RAP', 
			'OR' => array( 'REG_final_price' => 0, 'REG_paid' => array( '<', 'REG_final_price', true )),
			'OR*' => array( 'Transaction.STS_ID' => 'TCM', 'Transaction.STS_ID*1' => 'TIN' ),
			'OR**' => array( 'REG_final_price' => 0, 'REG_paid' => array( '<', 'REG_final_price', true ) ),
			'OR***' => array( 'REG_final_price*' => 0, 'Transaction.Payment_Method.PMD_slug' => 'acms_addon_payment_methods_sepa' ),
			'REG_ID' => array('NOT IN', $proc_array),

		)
	) 
);

Please read over the docs on using the models:

https://github.com/eventespresso/event-espresso-core/tree/master/docs/G–Model-System

This has all been documented in those docs and they recently had some changes to make them clearer.

Why do I get registrations in the result that have for example final price of 10 and reg paid is also 10 That means the second OR Statement did not work. Why is that.

When you run the query and don’t get the expected results, you need to view the query itself to see what is happening, to do that you can add echo $wpdb->last_query right after you run the query, it should then stick out what is happening form that.


ARAGATO

December 6, 2018 at 5:09 am

Wow, Tony. Thanks for that thorough reply.

I need to work through it a bit more, but you the key fact that ‘OR’ is pretty much a variable/fieldname that gets overwritten by itself was the most important one. Thus, by “naming” each ‘OR’ ‘OR*’ etc. individually the problem solved itself.

I could have used pure SQL query, but I really like the EE Model-based approach you guys developed. It keeps things clean, simple and from my point of view more efficient. That is why I wanted it to work with it by all means. 😀

Cheers.

You must be logged in to reply to this support post. Sign In or Register for an Account

Support forum for Event Espresso 3 and Event Espresso 4.
Documentation for EE3 and EE4
Documentation for Event Espresso 3

Documentation for Event Espresso 4

Status: publish

Updated by  ARAGATO 1 week, 4 days ago ago

Topic Tags

Tagged: , , ,

Notifications

This topic is: resolved
Do NOT follow this link or you will be banned from the site!