145 posts
  • Has been part of the Envato Community for over 2 years
  • Located in Austria
  • Has collected 1+ items on Envato Market
  • Sells items exclusively on Envato Market
LovelessDesign says

Hey,

I haven’t found a solution with google. I have a loop that I want to sort with a sql table. The posts that have the most entries in my own sql-table, should be shown first.

Do you have an idea how to realize that?

$args = array(
    'paged'    => $paged,
    'cat'         => $catfilter,
);
if ($blogposts->have_posts()) :
   while ($blogposts->have_posts()) : $blogposts->the_post(); 

      $pageid = get_the_ID();
      $team1res = $wpdb->get_var("SELECT COUNT(*) FROM wp_votelock WHERE page_id=$pageid AND team=1");
3448 posts
  • Elite Author: Sold more than $75,000 on Envato Market
  • Has sold $750,000+ on Envato Market
  • Located in United States
  • Helps us moderate the forums
+10 more
sevenspark Moderator says

I think you’ll want to store your vote count as a meta field for the post (probably update the count meta field when the vote is tallied, or if you expect many votes then set a job to count votes periodically). That way you can sort by the post’s meta count in the query. In your example above it’s too late unless you want to loop twice and reorder your posts.

Assuming you have more page loads than votes, it’s going to be more efficient to run a COUNT on each vote (and store the result) rather than on each page load × number of posts/page.

1148 posts
  • Has referred 1+ members
  • Has sold $1,000+ on Envato Market
  • Has been a beta tester for an Envato feature
  • Has collected 10+ items on Envato Market
+3 more
fillerspace says

In general, to sort by count, try something like this:

SELECT COUNT(`column_to_count`) as `cnt` FROM `tablename` GROUP BY `column_to_count` ORDER BY `cnt` DESC
145 posts
  • Has been part of the Envato Community for over 2 years
  • Located in Austria
  • Has collected 1+ items on Envato Market
  • Sells items exclusively on Envato Market
LovelessDesign says

@sevenspark: so I should store the $team1res in a metafield. That was my idea too but how can I store it as a metafield? Doesn’t the user who vote has to be admin? I just know the other way when I type, as an admin, type something in a metafield and get the variable in the php code.

3448 posts
  • Elite Author: Sold more than $75,000 on Envato Market
  • Has sold $750,000+ on Envato Market
  • Located in United States
  • Helps us moderate the forums
+10 more
sevenspark Moderator says

Keep your separate table of individual posts (I assume you have a structure that stores post ID, user identifier, timestamp, or something like that) but each time you add a row to the table update a meta field for that post with the new count.

You’d use the update_post_meta function. The role of the user is not important but you should be using nonces to make sure that the submitted form is valid.

145 posts
  • Has been part of the Envato Community for over 2 years
  • Located in Austria
  • Has collected 1+ items on Envato Market
  • Sells items exclusively on Envato Market
LovelessDesign says

Thanks a lot sevenspark. You have perfectly identified my problem again and gave me the perfect solution. Great. :)

3448 posts
  • Elite Author: Sold more than $75,000 on Envato Market
  • Has sold $750,000+ on Envato Market
  • Located in United States
  • Helps us moderate the forums
+10 more
sevenspark Moderator says

You’re welcome, glad it helped! :)

by
by
by
by
by
by