Re: tree ordering with varbit

Joe Conway wrote:
> Scott Lamb wrote:
>> - I could't see a way to do multi-item sorts, like by score descending
>> and then post time ascending, for example.
>
> If I understand what you're saying correctly, I think you can just join
> back to the real table from the table function by the primary key. Then
> you can sort on anything you want.

I'd like to do this but hierarchically also. I.e., sorting based on its
deepest ancestors score then post time, second deepest's score then post
time, ..., its score then post time. So I guess in your function's
language, I'd need to be able to put a couple of columns into the branch
text.

>> - I couldn't see a way to filter for "messageroot_id = ?" first, so it
>> would probably be unnecesarily slow as the number of messages in this
>> discussion / the number of messages total ratio becomes small.
>
> Do you mean "where messageroot_id = any_value"? Yeah, I couldn't think
> of a good, flexible way to do this. The problem is that you can either
> run the function for every value in the table (too slow and doesn't make
> sense anyway), or you can somehow try to determine which records are
> root nodes. I suppose we could allow a NULL be passed in for
> "start_with" to indicate a wildcard, and then iterate internally over
> all records that have "parent_id" IS NULL. Still won't work for every
> case, but perhaps better than not having the option at all? Any other
> suggestions?

Hmm, not at the moment anyway.

>> - Also performance - I can't cache the results as a row in the table,
>> which I could see being important at some point. Kind of a pain to
>> keep in sync, like all denormalized things, but I could see this being
>> an expensive operation.
>
>
> Why not? Just SELECT the results of the connectby() function (optionally
> joined with other tables) into a table (temp or otherwise).

Good point. I could do that.

Thanks,
Scott