Re: newsfeed type query - Mailing list pgsql-general

From Ladislav Lenart
Subject Re: newsfeed type query
Date
Msg-id 5540B17A.2080609@volny.cz
Whole thread Raw
In response to newsfeed type query  (Jonathan Vanasco <postgres@2xlp.com>)
List pgsql-general
Hello.


On 29.4.2015 00:26, Jonathan Vanasco wrote:
>
> I'm trying to upgrade some code that powers a newfeed type stream, and hoping someone can offer some insight on
betterways to structure some parts of the query 
>
> The part that has me stumped right now...
>
> There are several criteria for why something could appear in a stream.  for example, here are 2 handling a posting:
>
>     * a posting by a friend
>     * a posting in a group
>
> the general way I've handled this so far has been simple:
>
>     select * from posting where author_id in (select friend_id from friends where user_id = ?) or group_id in (select
group_idfrom memberships where user_id = ?); 
>
> now i need to pull in the context of the match (friend, group, both), but I can't figure out how to do this cleanly.
>
> 1. if i just add 'case' statements to the select to note the origin, those subselects run again.   (ie, the same
subqueryis executed twice) 
> 2. if i structure this as a union (and note the origin with a string), it takes a lot more work to integrate and sort
the2 separate selects ( eg "select id, timestamp, 'by-friend'" unioned with "in-group") 
>
> does anyone have ideas on other approaches to structuring this?

Dunno if this is optimal (most probably not), but it might be of some help
(WARNING: not tested at all):

with
posting_ids as (
    select
        t.posting_id,
        bool_or(t.from_friend) as from_friend,
        bool_or(t.grom_group) as from_group
    from (
        select
            posting.id as posting_id,
            true as from_friend,
            false as from_group
        from posting
        where posting.author_id in (
            select friend_id from friends where user_id = ?
        )
        union all
        select
            posting.id as posting_id,
            false as from_friend,
            true as from_group
        from posting
        where group_id in (
            select group_id from memberships where user_id = ?
        )
    ) t
    group by t.posting_id
)
select
    posting.*,
    posting_ids.from_friend,
    posting_ids.from_group,
    posting_ids.from_friend or posting_ids.from_group as from_any,
    posting_ids.from_friend and posting_ids.from_group as from_both
from
    posting
    join posting_ids on posting.id = posting_ids.posting_id


Ladislav Lenart




pgsql-general by date:

Previous
From: Job
Date:
Subject: Pg_bulkload and speed
Next
From: Ladislav Lenart
Date:
Subject: Re: newsfeed type query