10 Replies - 653 Views - Last Post: 19 February 2017 - 12:16 PM

#1 huzi8t9  Icon User is offline

  • D.I.C Regular
  • member icon

Reputation: 25
  • View blog
  • Posts: 367
  • Joined: 11-July 07

Limited Results?

Posted 17 February 2017 - 12:57 AM

Hi,

I have a query to populate the most popular posts and, it seems to work. Now there is more posts and interactivity, it doesn't seem to work, it brings the same two results?

The SQL is:

                SELECT
                    Likes, Comments, Post, (Likes + Comments) AS Total
                FROM
                (
                    SELECT
                        COUNT(likes.like_post) AS Likes,
                        COUNT(comments.comment_post) as Comments,
                        likes.like_post as Post,
                        posts.post_owner as Owner
                    FROM
                        likes, posts, comments
                    WHERE
                        posts.post_id=likes.like_post AND likes.like_countable='1'
                    AND
                        comments.comment_post=posts.post_id
                    GROUP BY
                        like_post, comment_post
                ) AS t
                ORDER BY Total DESC LIMIT :limit;



Just so you know, my ":param" is set to 16 in my code.

Sorry if I can't get straight back to any answers - going to work now!

Thanks in advance

~huzi

Solution to count likes and comments
My SQL query should have been:

SELECT
	post_id, 
	@comments := (SELECT COUNT(*) FROM likes WHERE like_post = post_id) AS likes,
	@likes := (SELECT COUNT(*) FROM comments where comment_post = post_id) AS comments,
	(@comments + @likes) as total
FROM
	posts,
    (SELECT @comments:=0, @likes := 0) a
ORDER BY
	total
DESC;


My main mistake was not using a distinct count on my likes and comments.

Attached image(s)

  • Attached Image

This post has been edited by huzi8t9: 19 February 2017 - 12:40 AM


Is This A Good Question/Topic? 0
  • +

Replies To: Limited Results?

#2 Dormilich  Icon User is offline

  • 痛覚残留
  • member icon

Reputation: 4129
  • View blog
  • Posts: 13,023
  • Joined: 08-June 10

Re: Limited Results?

Posted 17 February 2017 - 01:01 AM

I see two different results in your output ....
Was This Post Helpful? 0
  • +
  • -

#3 huzi8t9  Icon User is offline

  • D.I.C Regular
  • member icon

Reputation: 25
  • View blog
  • Posts: 367
  • Joined: 11-July 07

Re: Limited Results?

Posted 17 February 2017 - 01:04 AM

View PostDormilich, on 17 February 2017 - 08:01 AM, said:

I see two different results in your output ....


Sorry, never explained clearly enough.

The two results which above never change - it's always the same two. I have more posts with more likes and comments but, it's always the two shown above which appear.

Thanks
Was This Post Helpful? 0
  • +
  • -

#4 Dormilich  Icon User is offline

  • 痛覚残留
  • member icon

Reputation: 4129
  • View blog
  • Posts: 13,023
  • Joined: 08-June 10

Re: Limited Results?

Posted 17 February 2017 - 01:13 AM

Then I would examine the subquery to see what data you draw your results from.
Was This Post Helpful? 0
  • +
  • -

#5 huzi8t9  Icon User is offline

  • D.I.C Regular
  • member icon

Reputation: 25
  • View blog
  • Posts: 367
  • Joined: 11-July 07

Re: Limited Results?

Posted 17 February 2017 - 12:17 PM

No matter how I try, the results are just not what I'd expect.

The count is wrong - it just shows 2 for everything...

Can someone help? I've created a SWL Fiddle: http://sqlfiddle.com/#!9/7dea26/1
Was This Post Helpful? 0
  • +
  • -

#6 astonecipher  Icon User is offline

  • Too busy for this
  • member icon

Reputation: 2329
  • View blog
  • Posts: 9,359
  • Joined: 03-December 12

Re: Limited Results?

Posted 18 February 2017 - 12:32 PM

How is this solved if you are still questioning something?



Basing the result set on the sum of comments and likes is going to be an issue. What business logic is in place to define what a popular post is? Should more weight be given to likes or comments? Here is the reason. If you have a post that has 15 likes and another that has 20 comments saying it is stupid, what is actually popular? The attention by itself of the quality that it brings?
Was This Post Helpful? 0
  • +
  • -

#7 huzi8t9  Icon User is offline

  • D.I.C Regular
  • member icon

Reputation: 25
  • View blog
  • Posts: 367
  • Joined: 11-July 07

Re: Limited Results?

Posted 19 February 2017 - 12:39 AM

Thank you very much for your input. I hadn't even considered that, merely counting the values.

Do you have a recommendation? Counting the likes and a percentage of the comments? That'd would need lots of data to say "what percentage of comments are negative"

Wouldn't that require a bespoke algorithm?

Thanks

This post has been edited by andrewsw: 19 February 2017 - 01:50 AM
Reason for edit:: Removed previous quote, just press REPLY

Was This Post Helpful? 0
  • +
  • -

#8 andrewsw  Icon User is online

  • lashings of ginger beer
  • member icon

Reputation: 6342
  • View blog
  • Posts: 25,595
  • Joined: 12-December 12

Re: Limited Results?

Posted 19 February 2017 - 02:04 AM

This thread has become confusing. Please clarify what you are now asking?



Adding the original solution to the first post causes confusion. In future just add new information, including any solutions, as new posts. People interested enough to read through the topic will recognise where a solution, or partial solution, has occurred, and the flow of the discussion will not be interrupted.



Anyway, I think it would really help if you state clearly what you mean by "popular" and "most popular".
Was This Post Helpful? 0
  • +
  • -

#9 huzi8t9  Icon User is offline

  • D.I.C Regular
  • member icon

Reputation: 25
  • View blog
  • Posts: 367
  • Joined: 11-July 07

Re: Limited Results?

Posted 19 February 2017 - 12:03 PM

OK,

I'm sorry you're finding it confusing.

My initial query was to populate likes and comments which, in theory, would make one post more popular - I got that query.

It was then suggested that if one image had 20 likes and another image had 5 likes and 20 comments saying the image is rubbish - the "rubbish" image would take precedence as it has more interactions.

From there, I queried as to how it may be more achievable to populate true popularity instead of interactions.

Thank you.
Was This Post Helpful? 0
  • +
  • -

#10 andrewsw  Icon User is online

  • lashings of ginger beer
  • member icon

Reputation: 6342
  • View blog
  • Posts: 25,595
  • Joined: 12-December 12

Re: Limited Results?

Posted 19 February 2017 - 12:10 PM

There is no need to apologise. However, I still think it requires clarification. What do you mean by "true popularity"?

Something can be popular because it is well liked, or it can be popular because there is a lot of activity concerning it. The reason I am persisting is that I believe if you can clearly state what your definitions are then you will find yourself much nearer to a solution and, coincidentally, make it easier for someone to help you.
Was This Post Helpful? 0
  • +
  • -

#11 andrewsw  Icon User is online

  • lashings of ginger beer
  • member icon

Reputation: 6342
  • View blog
  • Posts: 25,595
  • Joined: 12-December 12

Re: Limited Results?

Posted 19 February 2017 - 12:16 PM

Anyway, you could take a pragmatic approach and ignore the comments. If something has a lot of likes then the majority of the comments are likely to be favourable. (How would you distinguish unfavourable comments anyway?)

This isn't perfect though. Something that divides people will have a lot of likes and a lot of unfavourable comments. (A thumbs-down icon would help to distinguish.)
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1