diff options
Diffstat (limited to 'p2pvr/lib/sql/Schedules_GetCandidates.sql')
| -rw-r--r-- | p2pvr/lib/sql/Schedules_GetCandidates.sql | 20 |
1 files changed, 20 insertions, 0 deletions
diff --git a/p2pvr/lib/sql/Schedules_GetCandidates.sql b/p2pvr/lib/sql/Schedules_GetCandidates.sql new file mode 100644 index 0000000..1b9441b --- /dev/null +++ b/p2pvr/lib/sql/Schedules_GetCandidates.sql @@ -0,0 +1,20 @@ +select (e.title, e.subtitle, e.description)::text what, e.serviceid, e.eventid, sv.transportstreamid, + e.starttime - max(s.early), e.stoptime - interval '1 second' + max(s.late), + max(s.priority) +from services sv, events e, schedules s +where (s.serviceid is null or s.serviceid = e.serviceid) +and (s.title is null or lower(s.title) = lower(e.title)) +and (s.eventid is null or s.eventid = e.eventid) +and (s.search is null or event_tsvector(e) @@ plainto_tsquery(s.search)) +and sv.serviceid = e.serviceid +and e.starttime > now() +and not exists ( + select 1 + from seen + where lower(e.title) = lower(seen.title) + and coalesce(lower(e.subtitle), '') = coalesce(lower(seen.subtitle), '') + and ts_rank(to_tsvector(e.description), plainto_tsquery(seen.description)) + + ts_rank(to_tsvector(seen.description), plainto_tsquery(e.description)) > 1) +group by e.serviceid, e.eventid, sv.serviceid +order by max(s.priority) desc, e.title, e.subtitle, e.description, sv.transportstreamid, e.starttime + |