Re: newsfeed type query

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 better ways 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_id from 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 subquery is 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 the 2 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