Re: newsfeed type query

Since you very nicely DID NOT provide the pg version, O/S or table
structure(s), which is what you should do REGARDLESS of the
type of question (it's just the smart and polite thing to do when asking
for help) The best I can suggest is:
SELECT
CASE WHEN context = 'friend' THEN p.junka
WHEN context = 'group' THEN p.junkb
WHEN context = 'both' THEN p.junka || ' ' || p.junkb
END
FROM posting p
where p.author_id in (SELECT f.friend_id
FROM friends f
WHERE f.user_id = ?)
OR p.group_id in (SELECT m.group_id
FROM memberships m
WHERE m.user_id = ?);

On Tue, Apr 28, 2015 at 6:26 PM, Jonathan Vanasco <postgres(at)2xlp(dot)com> 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?
>
>
>
>
>
>
>
>
>
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

--
*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.