woocommerce offtopic - How to Order by random on a SQL query

admin管理员组

文章数量:1430143

How do I change the last line to order by rand? Now it's sorting by date of my woocommerce products.

INNER JOIN $wpdb->postmeta AS mt1 ON (posts.ID = mt1.post_id)
    WHERE
        posts.post_status = 'publish'
        AND  (mt1.meta_key = '_sale_price_dates_to' AND mt1.meta_value >= ".time().")

        GROUP BY posts.ID
        **ORDER BY posts.post_date DESC";**

How do I change the last line to order by rand? Now it's sorting by date of my woocommerce products.

INNER JOIN $wpdb->postmeta AS mt1 ON (posts.ID = mt1.post_id)
    WHERE
        posts.post_status = 'publish'
        AND  (mt1.meta_key = '_sale_price_dates_to' AND mt1.meta_value >= ".time().")

        GROUP BY posts.ID
        **ORDER BY posts.post_date DESC";**

• woocommerce-offtopic
• sql

Share Improve this question edited May 25, 2019 at 12:20 LoicTheAztec 3,3911717 silver badges2424 bronze badges asked May 24, 2019 at 21:25 RahmanRahman 2133 bronze badges 1

• How are you making use of this SQL query? That's a very expensive query, but it can be done with WP_Query. Also note that ordering things randomly in an SQL query is hideously expensive. It has to create a brand new table in memory with the same rows but randomly ordered before it can even begin searching – Tom J Nowell ♦ Commented May 24, 2019 at 22:07

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 2

You can use ORDER BY rand(), but it's a very heavy query as a temporary table is created for it.

A WC_Product_Query or a WP_Query could be a good alternative to WPDB custom query. Both support 'orderby' => 'rand',…

本文标签： woocommerce offtopicHow to Order by random on a SQL query