In a custom post type (products) I want to order products by summing multiple values based on user selection. I use custom taxonomies to filter the products.

I could use the orderby meta_value but the combinations a user could make are so many that it's not possible to store all these totals as post meta.

I think I will need to alter the wp query using filters like posts_clauses. Could a custom SQL query handle this effective (or maybe a different approach)?

It's a custom setup so I don't mind how the values are stored. Could be as post meta, in a custom table or even as a json/string in the post_content field (I don't make use of it).

  • How many different parameters could be used for that sum? – Krzysiek Dróżdż Jan 19 '19 at 11:53
  • The user will be able to filter up to 10-15 taxonomies. The sum will be between those but each taxonomy term will be considered as a different value. Every taxonomy will have 10-50 or more terms. – Christopher Jan 19 '19 at 15:10
  • I don't see enough here to propose an answer. For instance, how are the "multiple values" being turned into criteria for sorting the products, and how many products are there all together? Even if your code doesn't work, an example of what you've tried - or even the logic of it laid out schematically - might be very helpful: It's quite possible, for example, that part of the solution will involve capturing the results of one more or less complex query, and the rest from sorting through them. – CK MacLeod Jan 27 '19 at 8:56
  • @CKMacLeod I will use the pre_get_posts with URL parameters to filter the products. Products could be 1000 or 10.000+. Each filter (taxonomy) will correspond to a different value/price for each product. Simple I am looking for an approach to order by the total of multiple prices. – Christopher Jan 27 '19 at 10:24
  • 1
    I'm still not understanding either why you've settled on this particular approach or, more important, what precisely you're trying to accomplish. Personally, I'd like to see a fully formed explanation of the problem in your post, not in comments, including whatever code is already written or drafted. But it's up to you how you go about finding a solution. Good luck! – CK MacLeod Jan 27 '19 at 16:36

I wouldn't use taxonomies or custom fields for this purpose. Every time you're add one custom field for a query, you have to add a JOIN part for that query - it means that with 10-15 custom fields, you'll get query with 10-15 joins - so such query will be very slow.

But there is some nice trick you can use in such case. I would approach it this way:

  1. Create custom DB table with a column for every property that can be included in sum. (So there should be one external key post_id which will allow you to join this table with posts table and then one column for every property - so basic_cost, extra_cost, extra_cost_2 and so on - or whatever your properties are...) This way you'll be able to use these columns in a sum and the query will need only one join.
  2. Use save_post hook to fill that table with meta values.
  3. Use posts_clauses hook to join your custom table and order by whatever sum you want based on fields in that table.
  • Can you provide more details about the table structure? – Christopher Feb 4 '19 at 10:27
  • As I have answered you above I have 10-15 taxonomies and each taxonomy will have 10-50 terms. Each term will be considered as a different price/property. So there will be cases that a product will have 500+ prices/properties. – Christopher Feb 7 '19 at 19:37
  • So? Table containing 500 columns isn't a problem. On the other hand query with 500 joins will be a huge problem... – Krzysiek Dróżdż Feb 7 '19 at 19:38

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.