Forum Searching - Relating Posts

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

User avatar
jackpf
DevNet Resident
Posts: 2119
Joined: Sun Feb 15, 2009 7:22 pm
Location: Ipswich, UK

Forum Searching - Relating Posts

Post 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.
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: Forum Searching - Relating Posts

Post by Eran »

Are you still using that monolithic table to store post and threads together? you should post your DB structure regardless
User avatar
jackpf
DevNet Resident
Posts: 2119
Joined: Sun Feb 15, 2009 7:22 pm
Location: Ipswich, UK

Re: Forum Searching - Relating Posts

Post 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.
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: Forum Searching - Relating Posts

Post 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?
User avatar
jackpf
DevNet Resident
Posts: 2119
Joined: Sun Feb 15, 2009 7:22 pm
Location: Ipswich, UK

Re: Forum Searching - Relating Posts

Post 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.
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: Forum Searching - Relating Posts

Post 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.
User avatar
jackpf
DevNet Resident
Posts: 2119
Joined: Sun Feb 15, 2009 7:22 pm
Location: Ipswich, UK

Re: Forum Searching - Relating Posts

Post 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.
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: Forum Searching - Relating Posts

Post by Eran »

Post your current query
User avatar
jackpf
DevNet Resident
Posts: 2119
Joined: Sun Feb 15, 2009 7:22 pm
Location: Ipswich, UK

Re: Forum Searching - Relating Posts

Post 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.
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: Forum Searching - Relating Posts

Post 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
User avatar
jackpf
DevNet Resident
Posts: 2119
Joined: Sun Feb 15, 2009 7:22 pm
Location: Ipswich, UK

Re: Forum Searching - Relating Posts

Post 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.
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: Forum Searching - Relating Posts

Post 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.
User avatar
jackpf
DevNet Resident
Posts: 2119
Joined: Sun Feb 15, 2009 7:22 pm
Location: Ipswich, UK

Re: Forum Searching - Relating Posts

Post 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.
User avatar
superdezign
DevNet Master
Posts: 4135
Joined: Sat Jan 20, 2007 11:06 pm

Re: Forum Searching - Relating Posts

Post 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
User avatar
jackpf
DevNet Resident
Posts: 2119
Joined: Sun Feb 15, 2009 7:22 pm
Location: Ipswich, UK

Re: Forum Searching - Relating Posts

Post 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.
Post Reply