wp query - Order by summing multiple values

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).

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).

• wp-query
• query
• mysql
• order
• sql

Share Improve this question asked Jan 19, 2019 at 11:43 ChristopherChristopher 33533 silver badges1414 bronze badges 9

• How many different parameters could be used for that sum? – Krzysiek Dróżdż Commented Jan 19, 2019 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 Commented Jan 19, 2019 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 Commented Jan 27, 2019 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 Commented Jan 27, 2019 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 Commented Jan 27, 2019 at 16:36

 |  Show 4 more comments

1 Answer 1

Sorted by: Reset to default 0

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.