Posted: December 5, 2018 at 3:08 pm
A follow up on this thread:
This is my where statement:
This is my query result:
From my understanding, registration with ID 90 should not be returned as a result with those where statements. But it is. Why?
To get results without including registration ID 90, you could do:
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:
Yes, indeed. I would like to get the rows where the key is not set at all.
Isn’t there an SQL command/EEM command that just says: “Get me all the results where metakey “xyz” is not set”?
I also still do not understand the query logic:
From my understand it reads like:
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.
Same with this one:
Why do I get registrations that have transaction with PMD_ID = 12? Why does first statement not work?
Depends entirely on the 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.
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.
That’s incorrect because you are overwriting the ‘OR’ key, the only ‘OR’ that applies above is the last one:
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:
What will var_dump output for that array?
Why? Because it’s the same as doing this:
Meaning you are replacing the value of ‘OR’ each time.
The models are designed to handle this by adding
So for the above you could use
So like this:
Please read over the docs on using the models:
This has all been documented in those docs and they recently had some changes to make them clearer.
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
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. 😀