title: Full-text search with related tables in PostgreSQL
body: <p>Task:
  Implement full text search in multiple tables in PostgreSQL.</p>
  
  <p>For example, a table of projects - projects:
  There are two fields in which need to search: title and description.
  Create an index on this table:</p>
  
  <pre><code>ALTER TABLE public.projects ADD COLUMN search_fts tsvector;
  UPDATE public.projects SET search_fts =
  to_tsvector(
      coalesce(title, '') ||' '||
      coalesce(description, '') || ' '
  );
  CREATE INDEX in_projects_idx ON public.projects
    USING gin (search_fts);
  </code></pre>
  
  <p>Now search is simple:</p>
  
  <pre><code>SELECT
      DISTINCT p.id,
      p.title,
      ts_rank(
          p.search_fts,
          to_tsquery('word1 | word2')
      ) as rank
  FROM
      projects p
  WHERE
      p.search_fts @@ to_tsquery('word1 | word2')
  ORDER BY rank DESC;
  </code></pre>
  
  <p>Sophisticate. Now we need to look at the table and project categories - project_categories.
  Similar to create tsvector and for the table on the field name.
  Search query now is:</p>
  
  <pre><code>SELECT
      DISTINCT p.id,
      p.title,
      category.name as categoryName,
      ts_rank(
          (
              coalesce(p.search_fts, '') ||' '||
              coalesce(category.search_fts, '')
          ),
          to_tsquery('word1 | word2 | categoryName')
      ) as rank
  FROM
      projects p
  LEFT JOIN project_categories category
      ON p.category_id = category.category_id
  WHERE
      (
          coalesce(p.search_fts, '') ||' '||
          coalesce(category.search_fts, '')
      ) @@ to_tsquery('word1 | word2 | categoryName')
  ORDER BY rank DESC;
  </code></pre>
  
  <p>More sophisticate. Now the search has to work on dozens of tables related to projects as a one-to-many and many-to-many.</p>
  
  <p>Number join's growing. Combine to a growing number of fields.</p>
  
  <p>Actually the question the right way I'm going? How would you solve this problem?</p>
  
  <p>I also think about the version with view.
  For example, built on such a request:</p>
  
  <pre><code>(
    SELECT 
        p.id as project_id,
        p.search_fts
    FROM projects p
  ) UNION ALL (
    SELECT 
        p.id as project_id,
        category.search_fts
    FROM projects p
    JOIN project_categories category
        ON p.category_id = category.category_id
  )
  </code></pre>
  
  <p>Finally, we obtain the overall index. By which to search.
  But in the view have to combine a dozen requests for various communication options.
  Interested in your views on this option.</p>
  
  <p>Sorry for my english.</p>