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");
3532 posts
  • Became a Top 20 Author of the Month
  • Had an item that was trending
  • Had an item that became a weekly top seller
  • Elite Author: Sold more than $75,000 on Envato Market
+13 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.

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

3532 posts
  • Became a Top 20 Author of the Month
  • Had an item that was trending
  • Had an item that became a weekly top seller
  • Elite Author: Sold more than $75,000 on Envato Market
+13 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. :)

3532 posts
  • Became a Top 20 Author of the Month
  • Had an item that was trending
  • Had an item that became a weekly top seller
  • Elite Author: Sold more than $75,000 on Envato Market
+13 more
sevenspark
Moderator
says

You’re welcome, glad it helped! :)

by
by
by
by
by
by