I'm trying to set up a full text search in PostgreSQL but coming across the problem that the more tables I join to the query the fewer the results I get back.
The tables I am trying to join are:
There is a many-to-many relationship between
article and all the other tables.
I am basing my query on that found here and at a loss as to why it's failing. The query so far looks like this:
SELECT title FROM ( SELECT article_title AS title, to_tsvector(article.article_title) || to_tsvector(string_agg(keyword.keyword, '')) || to_tsvector(string_agg(organisation.org_name, '')) || to_tsvector(string_agg(author.name, '')) AS document FROM article JOIN article_keywords on article.id = article_keywords.article_id JOIN keyword on keyword.id = article_keywords.keyword_id JOIN article_authors on article.id = article_authors.article_id JOIN author on author.id = article_authors.author_id JOIN article_organisations on article.id = article_organisations.article_id JOIN organisation on organisation.id = article_organisations.organisation_id GROUP BY article.article_title) AS p_search WHERE p_search.document @@ to_tsquery('David & Feeney');
In the example above, I want to search across all tables for mentions of 'David Feeney'. There are two entries in the author table for him but so far none are returned.