Search Postgresql Archives

Re: newsfeed type query

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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@xxxxxxxx> 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@xxxxxxxxxxxxxx)
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.


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux