Re: Complicated query... is there a simpler way? - Mailing list pgsql-general

The problem, of course, is your database schema.

SQL really sucks for processing of recursive queries, you should reference
beginning of the thread in your posts table, and search by that.

On Wed, 27 Jun 2001, Joshua Adam Ginsberg wrote:

> I've got a nasty looking search query, and I'm afraid with a large table
> it's going to be horribly inefficient, and I was wondering if anybody
> could think of a way to slim this one down.
>
> I'll simplify my situation as much as I can.
>
> I'm working with two tables here. The first is a user table:
>
> create table users(
> userid integer not null primary key,
> firstnames text not null,
> lastname text not null
> );
>
> The second is a message board table:
>
> create table posts(
> postid integer not null primary key,
> reply_to integer references posts(postid),
> author integer not null references users(userid),
> subject text not null,
> body text not null
> );
>
> I think the only columns which need explaining are postid and reply_to.
> When a new thread is created, a row is inserted into posts with a unique
> postid and a null reply_to. Any subsequent responses to this original
> post are also given unique postid's but their reply_to field references
> the start of the thread.
>
> I'm writing a search function. It would be useful to search by thread,
> and not by individual post. So I've created an aggregate function
> concat(text). I've also created a scoring function that takes in three
> text variables. The first should be the query (which I'll represent as
> $query, since it's provided by the webserver). The second should be the
> subject to search (subject is scored differently than the body) and the
> third should be the body to search.
>
> Here's the query:
>
> select lastname, firstnames, subject, threadid from posts, users,
> (select threadid, concat(body) as thread_body from (select postid as
> threadid, body from posts where reply_to is null union select reply_to
> as threadid, body from posts where reply_to is not null) as
> inner_subquery group by threadid) as outer_subquery where users.userid =
> posts.author and threadid = postid and
> score_search('$query',subject,thread_body) > 0 order by
> score_search('$query',subject,thread_body);
>
> Forgive me if I have any syntactic errors. I'm translating from my real
> query. My real query's a little larger since my actualy scenario's a
> little more complicated. But that's  four actual select calls to produce
> this. Is there a prettier way anybody can think of?
>
> Thanks!
>
> -jag
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>
>