Your problem is you have:
foreach post where A is true, that has a child where B is true
Which is actually:
foreach post where A is true
that has a child where B is true
So this is not something you should be doing in 1 query. Instead you would need to do n queries where n= parentposts+1. 1 query to find the parents, and then a query for each parent to find the children.
Clearly this is not going to be fast.
The Root Of The Problem
Your data structure is inappropriate. You’ve attempted to do is reinvent taxonomies using post meta.
Instead, create a custom taxonomy called ‘Location’, and create a Stockholm term. Then do a query for all posts that have the meta for size, and add 'Location' => 'Stockholm'
.
Benefits:
- Querying is dramatically simplified
- You’re given a working, extensively tested admin interface for free
- Its fast
- You get free archives, e.g. example.com/location/stockholm/
- Tight integration into the template heirarchy with new templates:
taxonomy.php
,taxonomy-location.php
and eventaxonomy-location-stockholm.php
Disadvantages:
- None
- Nothing
- There aren’t any
As a sidenote, if you run into a situation where you need to execute an sql query, you’ve done something wrong.