Advertisement
Guest User

Search where headline doesn't match

a guest
Feb 21st, 2017
100
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. select
  2.   o.id,
  3.   o.dbid,
  4.   o.type,
  5.   o.name,
  6.   o.display_name,
  7.   o.short_label,
  8.   o.full_label,
  9.   o.description,
  10.   o.data_source_description,
  11.   o.sourcelink,
  12.   o.query,
  13.   o.all_words,
  14.   o.rank,
  15.   array_agg(distinct(o.words)) words
  16. from (
  17.   select
  18.     i.id,
  19.     i.dbid,
  20.     i.type,
  21.     i.name,
  22.     i.display_name,
  23.     i.short_label,
  24.     i.full_label,
  25.     i.description,
  26.     i.data_source_description,
  27.     i.sourcelink,
  28.     i.query,
  29.     i.all_words,
  30.     i.rank,
  31.     (regexp_matches(ts_headline(i.all_words, i.query, 'HighlightAll=true'), '<b>(.*?)</b>', 'g'))[1] words
  32.   from (
  33.     select
  34.       case
  35.         when column_id is not null then column_id
  36.         when dataset_id is not null then dataset_id
  37.         when set_id is not null then set_id
  38.       end as id
  39.       , id as dbid
  40.       , type
  41.       , coalesce (name, '') as name
  42.       , coalesce (display_name, '') as display_name
  43.       , coalesce (short_label, '') as short_label
  44.       , coalesce (full_label, '') as full_label
  45.       , coalesce (description, '') as description
  46.       , coalesce (data_source_description, '') as data_source_description
  47.       , coalesce (link, '') as sourcelink
  48.       , query
  49.       , all_words
  50.       , ts_rank_cd (search, query) as rank
  51.     from
  52.       dataset_search ds
  53.       , to_tsquery (:query) query
  54.     where
  55.       search @@ query
  56.       and product_ids && :product_id
  57.       and group_ids && :group_ids
  58.       and type = any(:types)
  59.     order by
  60.       rank desc
  61.     limit :limit
  62.     offset :offset
  63.   ) i
  64. ) o
  65. group by
  66.   o.id,
  67.   o.dbid,
  68.   o.type,
  69.   o.name,
  70.   o.display_name,
  71.   o.short_label,
  72.   o.full_label,
  73.   o.description,
  74.   o.data_source_description,
  75.   o.sourcelink,
  76.   o.query,
  77.   o.all_words,
  78.   o.rank
  79. order by
  80.   o.rank desc
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement