Page 1 of 2

Forum Searching - Relating Posts

Posted: Sun Oct 18, 2009 7:11 am
by jackpf
Hi :)

Ok, so for my forum, I'm just wondering how I can display threads that have relative posts when someone searches.

So, for example, if someone searches "apples", and there is a thread that has a post containing the word "apples" in it, select that thread.

At the moment I join the posts onto the thread something similar to this (simplified):
[sql]SELECT `T`.*, `P`.*FROM `Threads` `T`LEFT OUTER JOIN `Posts` `P` ON `P`.`Threadid` = `T`.`ID`GROUP BY `T`.`ID`HAVING `T`.`Subject` LIKE '%apples%' OR `T`.`Post` LIKE '%apples%' OR `P`.`Subject` LIKE '%apples%' OR `P`.`Post` LIKE '%apples%'[/sql]
However, this only returns threads that contain apples in the starting subject/post, not the rest of the posts in the thread.

I'm not really sure how to do this...any help would be cool, or just a point in the right direction.

Cheers,
Jack.

Re: Forum Searching - Relating Posts

Posted: Sun Oct 18, 2009 8:26 am
by Eran
Are you still using that monolithic table to store post and threads together? you should post your DB structure regardless

Re: Forum Searching - Relating Posts

Posted: Sun Oct 18, 2009 9:20 am
by jackpf
Well...yes :P Really I'm storing everything in one table, but for simplicity's sake, I've given an example where I'm storing them in separate tables. My actual query is a bit of a monster, so I think it's best to stick with the example.

So, I'll have two tables - Threads & Posts.

`Threads`
`ID` - primary key
`Subject` - Thread subject
`Post` - Thread post

`Posts`
`ID` - primary key
`Threadid` - relation with `Thread`.`ID`
`Subject` - Subject
`Post` - Post

But I can't think of a way to select from `Threads` where any one of it's joined posts, from `Posts`, contain the search term :/

Cheers pytrin,
Jack.

Re: Forum Searching - Relating Posts

Posted: Sun Oct 18, 2009 10:03 am
by Eran
It should work if you switch the HAVING with a WHERE (and move it before the GROUP BY). HAVING is applied last and WHERE is applied before the grouping. After the grouping, the individual posts are discarded. Also, is there any particular reason you are using a LEFT OUTER JOIN instead of an INNER JOIN?

Re: Forum Searching - Relating Posts

Posted: Mon Oct 19, 2009 5:38 am
by jackpf
I think I tried a WHERE clause, but it says the joined columns don't exist. I thought that the joined columns could only be filtered in the HAVING clause. I'll give it another go tonight though.

And yeah, in reality I am actually using an inner join. But if I were using two tables, I would have to use an outer join surely? Because a thread can have no posts, but it should still be displayed. The way I'm actually doing it, the thread itself is returned in the join, so I can use an inner join, since there will always be at least 1 row returned.

Re: Forum Searching - Relating Posts

Posted: Mon Oct 19, 2009 8:27 am
by Eran
I think you mean a left join. Left and right joins are both 'outer' joins - in fact, there is no need to specify 'outer' in the syntax, as 'left join' and 'left outer join' are equivalent.
In a normalized two-table structure, there would always be one post for each thread - that would be the first post in the thread. The thread row only contains the subject and identifier of the creator.

Re: Forum Searching - Relating Posts

Posted: Sat Oct 24, 2009 4:26 pm
by jackpf
Ahh I forgot about this ^^ Sorry.

Oh right. I just got into the habit of specifying outer.

And yeah, I get a "Unknown column `P`.`Subject` in WHERE clause" error. :?

This has me confused.

Re: Forum Searching - Relating Posts

Posted: Sat Oct 24, 2009 5:52 pm
by Eran
Post your current query

Re: Forum Searching - Relating Posts

Posted: Sat Oct 24, 2009 9:13 pm
by jackpf
Well...it's a bit hefty.

[sql]SELECT `F`.*,P.*, COUNT(`P`.`ID`) AS `PostCount`, IF(`F`.`Type`='forum', (SELECT SUM(`D`.`PostCount`) FROM `Forum_Data` `D` WHERE `D`.`Alias`='jackpf' AND `F`.`ID`=`D`.`Forum`), (SELECT `D`.`PostCount` FROM `Forum_Data` `D` WHERE `D`.`Alias`='jackpf' AND `F`.`Forum`=`D`.`Forum` AND `F`.`ID`=`D`.`Thread`)) AS `Forum_Data_PostCount`, IF(`F`.`Type`='forum', SUM(`P`.`Stats`), `F`.`Stats`) AS `Stats`, MAX(`P`.`ID`) AS `LastPost` FROM `Forum` `F`#LEFT OUTER JOIN `Forum` `P` ON IF(`F`.`Type`='forum', `F`.`ID`=`P`.`Forum`, `F`.`ID`=`P`.`Thread`) AND `P`.`Type` IN('thread', 'post')INNER JOIN `Forum` `P` ON IF(`F`.`Type`='forum', `F`.`ID`=`P`.`Forum` /*OR `F`.`ID`!=`P`.`Forum`*/, `F`.`ID`=`P`.`Thread`/*)*/ AND `P`.`Type` IN('thread', 'post')) WHERE (`F`.`Forum`='2009' OR SUBSTRING_INDEX(`F`.`Status`, ':', -1)=2)AND (`F`.`Subject` LIKE '%search_term%' OR `F`.`Post` LIKE '%search_term%' OR `P`.`Subject` LIKE '%search_term%' OR `P`.`Post` LIKE '%search_term%')AND ((`F`.`Type`='forum' AND `F`.`ID`!=`F`.`Forum`) OR `F`.`Type`='thread')GROUP BY `F`.`ID` ORDER BY IF(`F`.`Type`='forum', 0, 1), IF(SUBSTRING_INDEX(`F`.`Status`, ':', -1)>=1, 1, 2), `LastPost` DESC;[/sql]

But it's here:
[sql]`P`.`Subject` LIKE '%search_term%' OR `P`.`Post` LIKE '%search_term%'[/sql]that's giving me the error, saying the fields don't exist.

Re: Forum Searching - Relating Posts

Posted: Sat Oct 24, 2009 9:32 pm
by Eran
ye gods..

I believe the problem is with the IF statement in the ON condition, since when it equates to false it is not joined (rendering those fields invalid). Use a left join instead

Re: Forum Searching - Relating Posts

Posted: Sat Oct 24, 2009 10:26 pm
by jackpf
No, it still doesn't work.

But the inner join returns the record itself...so I wouldn't have thought that would make a difference.

Cheers,
Jack.

Re: Forum Searching - Relating Posts

Posted: Sun Oct 25, 2009 4:14 am
by Eran
But the inner join returns the record itself
No idea what you meant by that.
Did you change the IF statement into a regular ON condition?

In anyway case, you need to debug this query like you debug code. Start dropping subqueries and conditions and you figure out the root of the problem.

Re: Forum Searching - Relating Posts

Posted: Sun Oct 25, 2009 9:31 am
by jackpf
Wow.

Just changing it to a left join worked. Sorry I didn't believe you :P

The only reason I use an inner join is because it takes about half the time a left join does.

Oh well, it's only searches that will be affected. It's set up to still use an inner join for just displaying threads normally.

Thanks once again pytrin :)

Cheers,
Jack.

Re: Forum Searching - Relating Posts

Posted: Sun Oct 25, 2009 11:45 am
by superdezign
So, before we end this thread, I'd like to address your database structure. I'm not saying I'm right, but I'm not saying I'm wrong either. :P

Anyway, your threads table has a subject and a post. But, what if you implemented your threads as a linked list instead of an actual post? You know, like a thread? :P

Now, you wouldn't want to actually make it a linked list in the sense that posts linked to each other... That would be too complex to query. However, instead of a thread having a subject and a post, make it only have a reference to it's topic post (root node), and have all of the posts link to the thread. Hell, you could technically not even have a reference to the topic post, but it'd help with querying.

Code: Select all

CREATE TABLE `threads` (
  `id` UNSIGNED int AUTO_INCREMENT,
  `op_id` UNSIGNED int NOT NULL, # original post id
PRIMARY KEY (`id`));
 
CREATE TABLE `posts` (
  `id` UNSIGNED int AUTO_INCREMENT,
  `author_id` UNSIGNED int NOT NULL,
  `thread_id` UNSIGNED int NOT NULL, # thread id
  `subject` text DEFAULT "",
  `post` text,
  `date_posted` datetime,
  `date_edited` datetime,
PRIMARY KEY (`id`));
Then, you'd search only the `posts` table for the search term. You could then join the `threads` table and use `threads`.`op_id` if you want only the main topic post to show.


:3

Re: Forum Searching - Relating Posts

Posted: Sun Oct 25, 2009 4:33 pm
by jackpf
Lol.

Yeah, I get what you're saying. But basically...it's almost the same as what I have now, except I have a `Type` field specifying what type of post the post is (eg, Thread, Post, Forum etc), rather than having separate tables for each type.

Except the latency of having to join the table to itself, are there actually any other disadvantages to my structure? I guess you could argue that it's disorganised...but I actually find it easier to manage.



:P

All the best,
Jack.