<commit_before> -- Modify Materialized view and index for English searches

DROP MATERIALIZED VIEW search_index_en;

 CREATE MATERIALIZED VIEW search_index_en AS

 WITH allauthors AS (
   SELECT
     things.id thingid,
     string_agg(users.name, ' ') authorstring
   FROM users, authors, things
   WHERE
    authors.user_id = users.id AND
    authors.thingid = things.id
   GROUP BY things.id
),
alltags as (
  SELECT
	thingid,
	string_agg(tags, ' ') tagstring
  FROM
  	(SELECT things.id thingid, unnest(things.tags) tags FROM things) subtags
  GROUP BY
  	thingid
)

SELECT
		things.id,
		things.type,
    things.location,
		localized_texts.title,
    localized_texts.body,
		things.lead_image,
		things.updated_date,
    setweight(to_tsvector('english'::regconfig, localized_texts.title), 'A') ||
    setweight(to_tsvector('english'::regconfig, localized_texts.body), 'A') ||
    setweight(to_tsvector('english'::regconfig, alltags.tagstring), 'A') ||
    setweight(to_tsvector('english'::regconfig, allauthors.authorstring), 'A') ||
    setweight(to_tsvector('english'::regconfig, (things.location).city), 'A') ||
    setweight(to_tsvector('english'::regconfig, (things.location).country), 'A') AS document
	FROM
		things<commit_msg>whitespace cleanup<commit_after> -- Modify Materialized view and index for English searches

DROP MATERIALIZED VIEW search_index_en;

 CREATE MATERIALIZED VIEW search_index_en AS

 WITH allauthors AS (
   SELECT
     things.id thingid,
     string_agg(users.name, ' ') authorstring
   FROM users, authors, things
   WHERE
    authors.user_id = users.id AND
    authors.thingid = things.id
   GROUP BY things.id
),
alltags as (
  SELECT
	thingid,
	string_agg(tags, ' ') tagstring
  FROM
  	(SELECT things.id thingid, unnest(things.tags) tags FROM things) subtags
  GROUP BY
  	thingid
)

SELECT
		things.id,
    things.type,
    things.location,
    localized_texts.title,
    localized_texts.body,
    things.lead_image,
    things.updated_date,
    setweight(to_tsvector('english'::regconfig, localized_texts.title), 'A') ||
    setweight(to_tsvector('english'::regconfig, localized_texts.body), 'A') ||
    setweight(to_tsvector('english'::regconfig, alltags.tagstring), 'A') ||
    setweight(to_tsvector('english'::regconfig, allauthors.authorstring), 'A') ||
    setweight(to_tsvector('english'::regconfig, (things.location).city), 'A') ||
    setweight(to_tsvector('english'::regconfig, (things.location).country), 'A') AS document
	FROM
		things
{'fraction_non_alphanumeric': 0.11972679791080755, 'fraction_numerical': 0.0, 'mean_word_length': 4.4605263157894735, 'pattern_counts': {'":': 0, '<': 3, '<?xml version=': 0, '>': 3, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '897991', 'n_tokens_mistral': 897, 'n_tokens_neox': 835, 'n_words': 174}