5

Lets say we have two CPTs, one of cities, and another of restaurants. Each restaurant is located in a city (duh) and serves different types of foods, different hours, different prices, etc. Each restaurant has the post ID of the city in a "city" meta field, and the rest of the properties are in other meta fields.

My goal is to be able to do something like "get all cities in which there are italian restaurants that are open on sunday", with pagination by cities.

This obviously have to scale as in every country there are many cities and restaurants. (yes, the analogy is actually in the scale the DB is going to be)

One way to do it that came into my mind is to search for the restaurants, and then group them by cities, but this sounds like something that can easily overflow memory in a trivial search (all restaurants that open on monday).

Basically what I am looking for is for a way to join two queries, but any other idea is welcome

2

These meta queries are going to be expensive, and figuring out the answer and caching it is a poor solution, so why not go to the root of the problem and adjust the data structure?

Your core data type is the restaurant, and you want to filter/query so that means taxonomies, so why not have a Location/city taxonomy? Just because you have a city post type doesn't mean you can't also add a city or area taxonomy. Structure it hierarchically via continent -> country -> province -> city.

Now your search is a search of restaurants with a tax_query that specifies the city. Do this for every child term in a province/country and you have what you want.

As an aside, this still doesn't avoid meta queries, which are the number 1 performance issue you'll face by a huge margin. Anything you currently store as post meta that you're going to let users filter or search by needs to be a taxonomy, or have an equivalent. E.g. for Prices you might lay out ranges of "€10-20 per person" etc, then let users search by this instead of entering a precise value.

So the end result will give you:

  • A page that when given a term in the location taxonomy, grabs the deepest child terms, then loops over them
  • For each of these child terms it
    • Queries for restaurants in that term that match the other search parameters, with a max of 4 or 5 restaurants for performance reasons
    • If any are found
      • Displays the title of the term/city
      • Displays the results of the search and a link to a full results page

I'd also advise the following things:

  • Use pre_get_posts, this will give you pagination for free, just put the search UI at the top of the template, if you can use the taxonomy archive for the cities/location taxonomy then even better!
  • Taxonomies taxonomies taxonomies taxonomies, don't use post meta queries. If you do the scalability and performance of this page will fall of dramatically. A single meta query can bring down a site with heavy traffic, if you're doing one for each city here then this page will take a long time to load

Finally, consider that just because you can query this, doesn't mean you should. For an efficient data structure with fast page loads, you need to decide what constraints are acceptable. The more freeform your data is the more expensive your queries will be, or the more data you need to duplicate and approximate. At some point you'll need Elastic Search to attain even mediocre performance if you want to be able to query anything.

Make UX decisions to change what a user can search for in ways that help you but don't deny the user what they want. E.g. letting the user pick a predefined price range or ranges, rather than giving them an entry box that accepts any number ( sliders can snap to the closest €5, turning a pricy meta query into a faster taxonomy query that mentions multiple terms )

|improve this answer|||||
  • thx. I was actually starting to think in that direction. As for terms and meta, I always have your preference for terms over meta in my mind, but in this case users have to be able to do range based search .... I am actually having an age based search criteria that I am going to "collapse" in similar way to what you proposed but some data is harder to handle this way (price of renting the place for an event) – Mark Kaplun Jan 11 '17 at 14:03
  • You can use post meta for precision/display and duplicate the data in terms, so long as you update it on save – Tom J Nowell Jan 11 '17 at 15:44
  • +1 for data structure/flow in consideration with taxonomies. – Michael Ecklund Jan 12 '17 at 17:48
  • 1
    That is what I am going to do with the additional twist of aggregating from the "restaurant" level into the "city" level, for example if the city has any Italian restaurant, the city itself will be associated with "italian" term of the "food type" taxonomy, and not only the restaurants themselves – Mark Kaplun Jan 24 '17 at 5:13
0

If you need cities:

Note in meta_value you can add anything...

meta_id bigint(20) unsigned Auto Increment   
post_id bigint(20) unsigned [0]  
meta_key    varchar(255) NULL    
meta_value  longtext NULL

You can have the meta value it for Italy and you can set the CityID on another meta.

The pagination is based on $wp_query global. So in order to have the pagination you just need to execute query_posts at the end of your search template and before the pagination function for the pagination links to be correct.

The arguments for this query_posts would have to include all city IDs you fetched when you searched the restaurants.


If you need restaurants and both City and Restaurant are CPTs you can create two queries, or one nested query to get all Italian citie IDs, via the query with 'fields' => 'ids' parameter.

Then get all restaurants having City IN list of these IDs.

This sounds simple to me.

|improve this answer|||||
  • the problem is that I need the cities, not the restaurants, and I need pagination. Getting 1000 restaurants and filtering only the cities out of them is not very friendly to pagination..... although maybe I can sort it based on the city meta value which might help.... need to think – Mark Kaplun Jan 11 '17 at 7:38
  • Updated the answer @MarkKaplun. – prosti Jan 11 '17 at 7:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.