I'm trying to do a full text search across two columns of two separate tables in PostgreSQL 9.4.2. The tables and columns are:
Where there is a many-to-many relationship between the two. I think I've got most of the way following this blog post. However, I am struggling to formulate the query across the two columns. This query is returning results:
SELECT distinct title, pub_date, web_id, city_points, city_name FROM ( SELECT article.article_title as title, article.pub_date as pub_date, article.web_id as web_id, string_agg(st_asText(city.geom), ', ') as city_points, string_agg(city.city, ', ') as city_name, article.title_tsv || keyword.keyword_tsv as document from article, article_keywords, keyword, article_cities, city where article.id = article_keywords.article_id and keyword.id = article_keywords.keyword_id and article.id = article_cities.article_id and city.id = article_cities.city_id GROUP BY article.id, keyword.id) p_search WHERE p_search.document @@ to_tsquery('putin');
But I think the query suffers many of the same deficiencies from a previous question I asked here.
I am also trying to return the following columns in the query:
I am pretty new to full text search - and SQL in general.
Let me know if you need any more information.