API-3685

old_crap.sql

--- QUERY1 : Prospector newsfeed scheduled email filter

SELECT source_id, newsfeed_item_id FROM "newsfeed_item" WHERE "newsfeed_item"."newsfeed_id" = 9390420 AND (0 < (select count(*) from newsfeed_item_event e where e.newsfeed_item_id = newsfeed_item.newsfeed_item_id and event in (1,7,4,2,0,5) and e.event_created_at >= '2015-12-15 19:39:34.940333')) ORDER BY (select event_created_at from newsfeed_item_event where newsfeed_item.newsfeed_item_id = newsfeed_item_event.newsfeed_item_id order by event_created_at desc limit 1) desc nulls last, newsfeed_item.last_event_at desc nulls last, newsfeed_item.newsfeed_item_id desc LIMIT 50;


--- QUERY2 : Ordering... subscribers? 
SELECT subscriber_id, max ( GREATEST ( last_event_at, event_created_at ) ) AS last_event_at FROM "newsfeed_item" LEFT OUTER JOIN "newsfeed_item_event" ON "newsfeed_item_event"."newsfeed_item_id" = "newsfeed_item"."newsfeed_item_id" WHERE "newsfeed_item"."source_type" = '' AND "newsfeed_item"."subscriber_id" IN ( ... ) AND "newsfeed_item"."notification_sent" = '' GROUP BY subscriber_id;

--- QUERY3 : Grouping subscriber counts
SELECT count ( DISTINCT source_id ) AS event_count, subscriber_id FROM "newsfeed_item" WHERE "newsfeed_item"."source_type" = '' AND "newsfeed_item"."subscriber_id" IN ( ... ) AND "newsfeed_item"."newsfeed_item_approved" = '' AND "newsfeed_item"."notification_sent" = '' GROUP BY subscriber_id;

--- recent example timeout
--- [INFO]  Started GET "/v1/contacts/20150623231104072663000000/newsfeeds/events?ApiUser=20150409153352835025000000&_pagination=1&_page=1&_filter=NotificationSent%20Eq%20true%20And%20Approved%20Eq%20true&_select=ListingKey%2CPhotos.Primary&_limit=200&RoleOverride=public" for 192.168.201.100 at 2015-12-28 09:46:32 -0600
SELECT  newsfeed_item_id FROM "newsfeed_item"  WHERE (newsfeed_item_id IN (select distinct on (source_id) newsfeed_item_id from newsfeed_item  where newsfeed_item.subscriber_id='20150623231104072663000000' AND ("notification_sent" = 't' AND "newsfeed_item_approved" = 't') order by source_id, (select event_created_at from newsfeed_item_event where newsfeed_item.newsfeed_item_id = newsfeed_item_event.newsfeed_item_id order by event_created_at desc limit 1) desc nulls last, last_event_at desc nulls last)) ORDER BY (select event_created_at from newsfeed_item_event where newsfeed_item.newsfeed_item_id = newsfeed_item_event.newsfeed_item_id order by event_created_at desc limit 1) desc nulls last, newsfeed_item.last_event_at desc nulls last, newsfeed_item.newsfeed_item_id desc LIMIT 200 OFFSET 0

pimped_out.sql

--- QUERY1 : Prospector newsfeed scheduled email filter

--- This is super simple and effective
--- No wade, that's not the same search!
SELECT source_id, newsfeed_item_id FROM "newsfeed_item" WHERE "newsfeed_item"."newsfeed_id" = 9390420 AND (0 < (select count(*) from newsfeed_item_event e where e.newsfeed_item_id = newsfeed_item.newsfeed_item_id and event in (1,7,4,2,0,5) and e.event_created_at >= '2015-12-15 19:39:34.940333') and newsfeed_item_created_at >= '2015-12-15 19:39:34.940333') ORDER BY (select event_created_at from newsfeed_item_event where newsfeed_item.newsfeed_item_id = newsfeed_item_event.newsfeed_item_id order by event_created_at desc limit 1) desc nulls last, newsfeed_item.last_event_at desc nulls last, newsfeed_item.newsfeed_item_id desc LIMIT 50
;

--- This might be a little cleaner code wise
SELECT i.source_id, i.newsfeed_item_id, max(e.event_created_at) as event_time FROM "newsfeed_item" i left outer join newsfeed_item_event e on i.newsfeed_item_id = e.newsfeed_item_id and newsfeed_id = 9390420 WHERE event in (1,7,4,2,0,5) and e.event_created_at >= '2015-12-15 19:39:34.940333' group by i.newsfeed_item_id, i.source_id ORDER BY event_time desc, i.newsfeed_item_id desc LIMIT 50
;

--- joys solution (i think) from chs-160
SELECT newsfeed_item_event.source_id,                                                                                        newsfeed_item_event.newsfeed_item_id,                                                                                                    MAX(newsfeed_item_event.event_created_at) AS last_eventFROM  newsfeed_item
JOIN  newsfeed_item_event
  ON  newsfeed_item_event.newsfeed_item_id = newsfeed_item.newsfeed_item_id
  AND newsfeed_item_event.source_id = newsfeed_item.source_id
WHERE newsfeed_item.newsfeed_id = 9390420
  AND (exists
         (SELECT 1
          FROM newsfeed_item_event e
          WHERE e.newsfeed_item_id = newsfeed_item.newsfeed_item_id
            AND e.source_id = newsfeed_item.source_id
            AND event IN (5,
                          1,
                          4,
                          0,
                          2,
                          7)
            AND e.event_created_at >= '2015-12-15 19:39:34.940333'))
GROUP BY
  newsfeed_item_event.source_id,
  newsfeed_item_event.newsfeed_item_id
ORDER BY
  last_event DESC,
  newsfeed_item_event.newsfeed_item_id DESC LIMIT 50;
  


--- QUERY2 : Prospector newsfeed scheduled email filter

zarchiving.sql

create table  newsfeed_item_event_all ( LIKE public.newsfeed_item_event)  ;
create table  newsfeed_item_event_archive () inherits  ( newsfeed_item_event_all)  ;
alter table  public.newsfeed_item_event inherit newsfeed_item_event_all;

create schema archive_2015_04;
create table archive_2015_04.newsfeed_item_event (
  check(event_created_at >= '2015-04-01 00:00:00'::timestamp and event_created_at < '2015-05-01 00:00:00'::timestamp)
) inherits (newsfeed_item_event_archive);
insert into archive_2015_04.newsfeed_item_event select * from newsfeed_item_event where event_created_at >= '2015-04-01 00:00:00'::timestamp and event_created_at < '2015-05-01 00:00:00'::timestamp;
delete from newsfeed_item_event where event_created_at >= '2015-04-01 00:00:00'::timestamp and event_created_at < '2015-05-01 00:00:00'::timestamp;
create schema archive_2015_05;
create table archive_2015_05.newsfeed_item_event (
  check(event_created_at >= '2015-05-01 00:00:00'::timestamp and event_created_at < '2015-06-01 00:00:00'::timestamp)
) inherits (newsfeed_item_event_archive);
insert into archive_2015_05.newsfeed_item_event select * from newsfeed_item_event where event_created_at >= '2015-05-01 00:00:00'::timestamp and event_created_at < '2015-06-01 00:00:00'::timestamp;
delete from newsfeed_item_event where event_created_at >= '2015-05-01 00:00:00'::timestamp and event_created_at < '2015-06-01 00:00:00'::timestamp;
create schema archive_2015_06;
create table archive_2015_06.newsfeed_item_event (
  check(event_created_at >= '2015-06-01 00:00:00'::timestamp and event_created_at < '2015-07-01 00:00:00'::timestamp)
) inherits (newsfeed_item_event_archive);
insert into archive_2015_06.newsfeed_item_event select * from newsfeed_item_event where event_created_at >= '2015-06-01 00:00:00'::timestamp and event_created_at < '2015-07-01 00:00:00'::timestamp;
delete from newsfeed_item_event where event_created_at >= '2015-06-01 00:00:00'::timestamp and event_created_at < '2015-07-01 00:00:00'::timestamp;
create schema archive_2015_07;
create table archive_2015_07.newsfeed_item_event (
  check(event_created_at >= '2015-07-01 00:00:00'::timestamp and event_created_at < '2015-08-01 00:00:00'::timestamp)
) inherits (newsfeed_item_event_archive);
insert into archive_2015_07.newsfeed_item_event select * from newsfeed_item_event where event_created_at >= '2015-07-01 00:00:00'::timestamp and event_created_at < '2015-08-01 00:00:00'::timestamp;
delete from newsfeed_item_event where event_created_at >= '2015-07-01 00:00:00'::timestamp and event_created_at < '2015-08-01 00:00:00'::timestamp;
create schema archive_2015_08;
create table archive_2015_08.newsfeed_item_event (
  check(event_created_at >= '2015-08-01 00:00:00'::timestamp and event_created_at < '2015-09-01 00:00:00'::timestamp)
) inherits (newsfeed_item_event_archive);
insert into archive_2015_08.newsfeed_item_event select * from newsfeed_item_event where event_created_at >= '2015-08-01 00:00:00'::timestamp and event_created_at < '2015-09-01 00:00:00'::timestamp;
delete from newsfeed_item_event where event_created_at >= '2015-08-01 00:00:00'::timestamp and event_created_at < '2015-09-01 00:00:00'::timestamp;
create schema archive_2015_09;
create table archive_2015_09.newsfeed_item_event (
  check(event_created_at >= '2015-09-01 00:00:00'::timestamp and event_created_at < '2015-10-01 00:00:00'::timestamp)
) inherits (newsfeed_item_event_archive);
insert into archive_2015_09.newsfeed_item_event select * from newsfeed_item_event where event_created_at >= '2015-09-01 00:00:00'::timestamp and event_created_at < '2015-10-01 00:00:00'::timestamp;
delete from newsfeed_item_event where event_created_at >= '2015-09-01 00:00:00'::timestamp and event_created_at < '2015-10-01 00:00:00'::timestamp;
create schema archive_2015_10;
create table archive_2015_10.newsfeed_item_event (
  check(event_created_at >= '2015-10-01 00:00:00'::timestamp and event_created_at < '2015-11-01 00:00:00'::timestamp)
) inherits (newsfeed_item_event_archive);
insert into archive_2015_10.newsfeed_item_event select * from newsfeed_item_event where event_created_at >= '2015-10-01 00:00:00'::timestamp and event_created_at < '2015-11-01 00:00:00'::timestamp;
delete from newsfeed_item_event where event_created_at >= '2015-10-01 00:00:00'::timestamp and event_created_at < '2015-11-01 00:00:00'::timestamp;
create schema archive_2015_11;
create table archive_2015_11.newsfeed_item_event (
  check(event_created_at >= '2015-11-01 00:00:00'::timestamp and event_created_at < '2015-12-01 00:00:00'::timestamp)
) inherits (newsfeed_item_event_archive);
insert into archive_2015_11.newsfeed_item_event select * from newsfeed_item_event where event_created_at >= '2015-11-01 00:00:00'::timestamp and event_created_at < '2015-12-01 00:00:00'::timestamp;
delete from newsfeed_item_event where event_created_at >= '2015-11-01 00:00:00'::timestamp and event_created_at < '2015-12-01 00:00:00'::timestamp;
create schema archive_2015_12;
create table archive_2015_12.newsfeed_item_event (
  check(event_created_at >= '2015-12-01 00:00:00'::timestamp and event_created_at < '2016-01-01 00:00:00'::timestamp)
) inherits (newsfeed_item_event_archive);
insert into archive_2015_12.newsfeed_item_event select * from newsfeed_item_event where event_created_at >= '2015-12-01 00:00:00'::timestamp and event_created_at < '2016-01-01 00:00:00'::timestamp;
delete from newsfeed_item_event where event_created_at >= '2015-12-01 00:00:00'::timestamp and event_created_at < '2016-01-01 00:00:00'::timestamp;