SQL query to select N posts around a post id with same category

Hi,

I have a database table to store my posts. Posts has post_id and post_category information. Each post has one single category.

I am trying to come up with a SQL query that will select (up to) 10 posts in the same category with a post (except the post itself), that are in the neighborhood of that post in the table in terms of their post_id.

Sample DB table:

post_id | post_category
1 | 1
2 | 1
3 | 2
4 | 1
5 | 2
6 | 3
7 | 1
8 | 1
9 | 2
10 | 1
11 | 2
12 | 3
13 | 2
14 | 1
15 | 1
16 | 1
17 | 2
18 | 3
19 | 3
20 | 2
21 | 1
22 | 2
23 | 1
24 | 1
25 | 1

Expected output for the SQL query:

If I enter 4 as post_id, it will select the posts with post_id (1,2,7,8,10,14,15,16,21,23)
If I enter 14 as post_id, it will select the posts with post_id (2,4,7,8,10,15,16,21,23,24)
If I enter 23 as post_id, it will select the posts with post_id (4,7,8,10,14,15,16,21,24,25)
If I enter 12 as post_id, it will select the posts with post_id (6,18,19)

$post_id and $category_id will be fed to the query. I started with the following:

SELECT * FROM posts WHERE post_category = $category_id AND (Trying to figure out what should come here) ORDER BY post_id DESC

I hope my example is clear enough, thanks for any ideas.

$categoryid is not needed

SELECT those.post_id , ABS(those.post_id - this.post_id) AS diff FROM posts AS this INNER JOIN posts AS those ON those.post_category = this.post_category AND those.post_id <> this.post_id WHERE this.post_id = $postid ORDER BY diff ASC LIMIT 10

by the way, the result for $postid 14 is (4,7,8,10,15,16,21,23,24,25), not (2,4,7,8,10,15,16,21,23,24)

1 Like

Hi, thank you for the query. I kind of understand what you are doing. Checking the difference of id values of posts in the same category and sorting them to get 10 posts that are closest to the entered post.

I tried the query but couldn’t make it work. Are you sure it works as expected? If so, I will continue trying to make it work.

yes, i tested it on your data, which is how i discovered that your results for 14 were wrong by one

I had listed the results for 14 by five before and five after posts, not by post_id difference. But it doesn’t matter actually, I just need 10 static results around a post that will not change as I add more posts in the future.

I will continue to try to figure out why it didn’t work for me. Thanks again.

[quote=“nayen, post:5, topic:218107, full:true”]
I had listed the results for 14 by five before and five after posts[/quote]

this is a new twist that you did not mention in your original post

I am glad I didn’t because now that I saw it, I prefer your approach of finding the difference and getting the list based on that difference. Either way, it really doesn’t matter. I just need to list 10 posts in the same category, and that list shouldn’t change as I add more posts past 10 in one category. I mean even if I add 100 posts in category 1 later on, the result for post 14 should still be (4,7,8,10,15,16,21,23,24,25), which your query seems to be satisfying.

This is the first time I am working with a query of this kind, so I am failing to to tell why it doesn’t work for me. I will try creating a new table and a separate function to test this.

could you show me your query?

I use the exact query you provided. Here is something that may help: When I enter the first post of a category, the query returns the expected result. But when I enter a random post in that category other than the first post, the query causes “Fatal error: Call to a member function fetch_assoc() on boolean” error on the fetch_assoc() I use after the query, which means the query doesn’t return what it is supposed to.

I have the following:

function related_posts($post_id) {
	$r = DB::$db->query("SELECT those.post_id
     , ABS(those.post_id - this.post_id) AS diff
  FROM posts AS this
INNER
  JOIN posts AS those
    ON those.post_category = this.post_category
   AND those.post_id <> this.post_id
 WHERE this.post_id = $post_id
ORDER
    BY diff ASC LIMIT 10");
	while ($row = $r->fetch_assoc()) $posts[] = $row;
	return $posts;
}

/* testing */
$related_posts = related_posts(64);

foreach ($related_posts as $post) {
	echo $post['post_id'].'<br>';
}

no, it means there’s a bug in your php

1 Like

That’s possible, but it works for the 1st entry of a category. The PHP I have is the PHP I posted in the previous comment. If the PHP was problematic, I would expect it to throw an error for the 1st category entry too.

Thanks for your contribution, I will see if I can make it work, or abandon it and use something else to display related posts.

Hi, just an update to tell that I was able to identify the cause of the issue. I tried removing “ORDER BY diff ASC LIMIT 10” from the query, which helped. It still worked for the 1st post of each category, but for other posts it gave the following error: “Warning: mysqli::query(): (22003/1690): BIGINT UNSIGNED value is out of range”. Then I checked my posts table and noticed I have the post_id as Unsigned INT. Unchecking Unsigned fixed the issue. I guess somewhere during the calculation in the query, the post_id value is becoming negative which was preventing the selection.

The reason I have the post_id as Unsigned is because it will only be 1+, no negative values, naturally. I can surely keep it as not Unsigned but I was wondering if there is a way to modify the query so that I can keep the post_id as Unsigned.

Thanks a lot for helping me solve this issue.

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.