Re: newsfeed type query

On 4/28/15 6:57 PM, Jonathan Vanasco wrote:
> The relevant structure is:
>
> posting:
> id
> timestamp_publish
> group_id__in
> user_id__author
> friends:
> user_id__a
> user_id__b
>
> memberships:
> user_id
> group_id
> role_id

Try this...

SELECT ...
, f.user_id__b IS NOT NULL AS in_friends
, m.user_id IS NOT NULL AS in_group
FROM posting p
LEFT JOIN friends f ON( f.user_id__b = p.user_id__author )
LEFT JOIN memberships m ON( m.group_id = p.group_id__in )
WHERE is_published AND timestamp_publish ...
AND (
f.user_id__a = 57
OR ( m.user_id = 57 AND m.group_id IN (1,2,3) )
)

I'm not sure how fast it'll be though. I suspect your best bet is to put
the UNION approach inside a set returning function; that way the ugly is
contained in one place.
--
Jim Nasby, Data Architect, Blue Treble Consulting
Data in Trouble? Get it in Treble! http://BlueTreble.com