SEO

Here is the description of my problem:


I try to build a search engine for the forum

This is the database structure of the forum:

Two tables: threads, posts

threads: id, subject, message

posts: id, thread_id, message


This query will print out results - list of id of main threads

(SELECT id AS my_id FROM threads WHERE Match(subject,message)
Against('keyword'))
UNION (SELECT thread_id AS my_id FROM posts WHERE Match(message)
Against('keyword')
LIMIT 10 OFFSET 0)

now i would like to use results from this query to get subject and
message for every thread

so I was thinking about using IN statement like:

SELECT * FROM threads WHERE id
IN
(SELECT id AS my_id FROM threads WHERE Match(subject,message)
Against('keyword'))
UNION
(SELECT thread_id AS my_id FROM posts WHERE Match(message)
Against('keyword')
LIMIT 10 OFFSET 0)


but this doesnt work

anyone can help me with this?

thanks,
Bart

joeriviera@gmail.com wrote:
> Here is the description of my problem:
>
>
> I try to build a search engine for the forum
>
> This is the database structure of the forum:
>
> Two tables: threads, posts
>
> threads: id, subject, message
>
> posts: id, thread_id, message
>
>
> This query will print out results - list of id of main threads
>
> (SELECT id AS my_id FROM threads WHERE Match(subject,message)
> Against('keyword'))
> UNION (SELECT thread_id AS my_id FROM posts WHERE Match(message)
> Against('keyword')
> LIMIT 10 OFFSET 0)
>
> now i would like to use results from this query to get subject and
> message for every thread
>
> so I was thinking about using IN statement like:
>
> SELECT * FROM threads WHERE id
> IN
> (SELECT id AS my_id FROM threads WHERE Match(subject,message)
> Against('keyword'))
> UNION
> (SELECT thread_id AS my_id FROM posts WHERE Match(message)
> Against('keyword')
> LIMIT 10 OFFSET 0)
>
>
> but this doesnt work
>
> anyone can help me with this?
>
> thanks,
> Bart

First off you should be using joins not sub selects.

Please explain what the difference is between a thread message and a post
message?

On Mar 16, 10:17 pm, "Paul Lautman" <paul.laut...@btinternet.com>
wrote:
> joerivi...@gmail.com wrote:
> > Here is the description of my problem:
>
> > I try to build a search engine for the forum
>
> > This is the database structure of the forum:
>
> > Two tables: threads, posts
>
> > threads: id, subject, message
>
> > posts: id, thread_id, message
>
> > This query will print out results - list of id of main threads
>
> > (SELECT id AS my_id FROM threads WHERE Match(subject,message)
> > Against('keyword'))
> > UNION (SELECT thread_id AS my_id FROM posts WHERE Match(message)
> > Against('keyword')
> > LIMIT 10 OFFSET 0)
>
> > now i would like to use results from this query to get subject and
> > message for every thread
>
> > so I was thinking about using IN statement like:
>
> > SELECT * FROM threads WHERE id
> > IN
> > (SELECT id AS my_id FROM threads WHERE Match(subject,message)
> > Against('keyword'))
> > UNION
> > (SELECT thread_id AS my_id FROM posts WHERE Match(message)
> > Against('keyword')
> > LIMIT 10 OFFSET 0)
>
> > but this doesnt work
>
> > anyone can help me with this?
>
> > thanks,
> > Bart
>
> First off you should be using joins not sub selects.
>
> Please explain what the difference is between a thread message and a post
> message?


thread message is the first post in the thread and all other posts in
the same thread are connected to this post

On Mar 16, 10:17 pm, "Paul Lautman" <paul.laut...@btinternet.com>
wrote:
> joerivi...@gmail.com wrote:
> > Here is the description of my problem:
>
> > I try to build a search engine for the forum
>
> > This is the database structure of the forum:
>
> > Two tables: threads, posts
>
> > threads: id, subject, message
>
> > posts: id, thread_id, message
>
> > This query will print out results - list of id of main threads
>
> > (SELECT id AS my_id FROM threads WHERE Match(subject,message)
> > Against('keyword'))
> > UNION (SELECT thread_id AS my_id FROM posts WHERE Match(message)
> > Against('keyword')
> > LIMIT 10 OFFSET 0)
>
> > now i would like to use results from this query to get subject and
> > message for every thread
>
> > so I was thinking about using IN statement like:
>
> > SELECT * FROM threads WHERE id
> > IN
> > (SELECT id AS my_id FROM threads WHERE Match(subject,message)
> > Against('keyword'))
> > UNION
> > (SELECT thread_id AS my_id FROM posts WHERE Match(message)
> > Against('keyword')
> > LIMIT 10 OFFSET 0)
>
> > but this doesnt work
>
> > anyone can help me with this?
>
> > thanks,
> > Bart
>
> First off you should be using joins not sub selects.
>
> Please explain what the difference is between a thread message and a post
> message?


Hi ,

thread message is the first post in the thread and all posts in the
same thread are connected to this post ( so threads.id =
posts.thread_id)

joeriviera@gmail.com wrote:
> On Mar 16, 10:17 pm, "Paul Lautman" <paul.laut...@btinternet.com>
> wrote:
> > joerivi...@gmail.com wrote:
> > > Here is the description of my problem:
> >
> > > I try to build a search engine for the forum
> >
> > > This is the database structure of the forum:
> >
> > > Two tables: threads, posts
> >
> > > threads: id, subject, message
> >
> > > posts: id, thread_id, message
> >
> > > This query will print out results - list of id of main threads
> >
> > > (SELECT id AS my_id FROM threads WHERE Match(subject,message)
> > > Against('keyword'))
> > > UNION (SELECT thread_id AS my_id FROM posts WHERE Match(message)
> > > Against('keyword')
> > > LIMIT 10 OFFSET 0)
> >
> > > now i would like to use results from this query to get subject and
> > > message for every thread
> >
> > > so I was thinking about using IN statement like:
> >
> > > SELECT * FROM threads WHERE id
> > > IN
> > > (SELECT id AS my_id FROM threads WHERE Match(subject,message)
> > > Against('keyword'))
> > > UNION
> > > (SELECT thread_id AS my_id FROM posts WHERE Match(message)
> > > Against('keyword')
> > > LIMIT 10 OFFSET 0)
> >
> > > but this doesnt work
> >
> > > anyone can help me with this?
> >
> > > thanks,
> > > Bart
> >
> > First off you should be using joins not sub selects.
> >
> > Please explain what the difference is between a thread message and a post
> > message?
>
>
> Hi ,
>
> thread message is the first post in the thread and all posts in the
> same thread are connected to this post ( so threads.id =
> posts.thread_id)

I think there's an implicit suggestion in Paul's post that you might
want to consider dispensing with the threads table - and instead have
a structure like this:

post(post_id*,parent_id,subject,message)

in this scenario a thread is simply a post whose parent_id is NULL. It
also means that responders can change the Subject as the thread
evolves - just like I just did.

On Mar 17, 12:16 pm, "strawberry" <zac.ca...@gmail.com> wrote:
> joerivi...@gmail.com wrote:
> > On Mar 16, 10:17 pm, "Paul Lautman" <paul.laut...@btinternet.com>
> > wrote:
> > > joerivi...@gmail.com wrote:
> > > > Here is the description of my problem:
>
> > > > I try to build a search engine for the forum
>
> > > > This is the database structure of the forum:
>
> > > > Two tables: threads, posts
>
> > > > threads: id, subject, message
>
> > > > posts: id, thread_id, message
>
> > > > This query will print out results - list of id of main threads
>
> > > > (SELECT id AS my_id FROM threads WHERE Match(subject,message)
> > > > Against('keyword'))
> > > > UNION (SELECT thread_id AS my_id FROM posts WHERE Match(message)
> > > > Against('keyword')
> > > > LIMIT 10 OFFSET 0)
>
> > > > now i would like to use results from this query to get subject and
> > > > message for every thread
>
> > > > so I was thinking about using IN statement like:
>
> > > > SELECT * FROM threads WHERE id
> > > > IN
> > > > (SELECT id AS my_id FROM threads WHERE Match(subject,message)
> > > > Against('keyword'))
> > > > UNION
> > > > (SELECT thread_id AS my_id FROM posts WHERE Match(message)
> > > > Against('keyword')
> > > > LIMIT 10 OFFSET 0)
>
> > > > but this doesnt work
>
> > > > anyone can help me with this?
>
> > > > thanks,
> > > > Bart
>
> > > First off you should be using joins not sub selects.
>
> > > Please explain what the difference is between a thread message and a post
> > > message?
>
> > Hi ,
>
> > thread message is the first post in the thread and all posts in the
> > same thread are connected to this post ( so threads.id =
> > posts.thread_id)
>
> I think there's an implicit suggestion in Paul's post that you might
> want to consider dispensing with the threads table - and instead have
> a structure like this:
>
> post(post_id*,parent_id,subject,message)
>
> in this scenario a thread is simply a post whose parent_id is NULL. It
> also means that responders can change the Subject as the thread
> evolves - just like I just did.


ok thanks for you suggestion
but I still need to keep some extra information about thread, like how
many thread has been displayed, how many posts in thread and when last
post has been added... thats why i needed thread table