CODEWITHSUNDEEP
mysqlwordpress
John mido
John mido

Reputation: 269

limit results for wpdb query

this code displays the tags from current category only but, it gets all the tags (hundreds) so, i need to limit the number of returned results and make em random.

How to make this query get only 20 results randomly ?

/* Retrieve all tags from posts in selected categories */

$cats = array('beaches','mountains');  // Must be an array even if only one category
$cats_string = "'" . implode($cats,"','") . "'";
$sql = <<<EOSQL
SELECT DISTINCT t.*
FROM $wpdb->posts p
JOIN $wpdb->term_relationships tr ON p.ID = tr.object_id
JOIN $wpdb->term_taxonomy tt ON (tr.term_taxonomy_id = tt.term_taxonomy_id
   AND tt.taxonomy = 'post_tag')
JOIN $wpdb->terms t ON tt.term_id = t.term_id
WHERE
   p.ID IN (
      SELECT p2.ID
      FROM $wpdb->posts p2
      JOIN $wpdb->term_relationships tr2 ON p2.ID = tr2.object_id
      JOIN $wpdb->term_taxonomy tt2 ON (tr2.term_taxonomy_id = tt2.term_taxonomy_id     AND tt2.taxonomy = 'category')
  JOIN $wpdb->terms t2 ON (tt2.term_id = t2.term_id AND t2.name IN ($cats_string))
  WHERE p2.post_type = 'post'
  AND p2.post_status = 'publish'
  AND p2.post_date <= NOW()
  )
  EOSQL;

$terms = $wpdb->get_results($sql);

// print_r($terms);

echo "<br />";
foreach ($terms as $term) {
   echo "ID:$term->term_id NAME:$term->name SLUG:$term->slug<br />";
}

Thanks

Upvotes: 0

Views: 2361

Answers (1)

dan
dan

Reputation: 13272

You can try an ORDER BY RAND() LIMIT 20, depending on your table size this can run in decent times. See here some details on when to avoid the order by rand() logic. Like suggested, in the specified post, the other approach is to retrieve all the entries and randomly select 20 entries, in PHP rather then using mysql.

Upvotes: 2

Related Questions