Enhance search by upgrading city2tour

[martinheppner]

SELECT t.id, c2t.provider, c2t.hashed_url, c2t.city_slug, c2t.reachable_from_country, c2t.min_connection_duration, u.min_connection_no_of_transfers, t.url, t.title, t.description, t.ascent, t.descent, t.difficulty, t.duration, t.distance, t.type, t.number_of_days, t.traverse, t.country, t.state, t.range_slug, t.range, t.quality_rating, t.search_column, t.text_lang, CASE WHEN t.text_lang='de' THEN 1 ELSE 0 END AS order_lang_de, CASE WHEN t.text_lang='en' THEN 1 ELSE 0 END AS order_lang_en, CASE WHEN t.text_lang='fr' THEN 1 ELSE 0 END AS order_lang_fr, CASE WHEN t.text_lang='sl' THEN 1 ELSE 0 END AS order_lang_sl, CASE WHEN t.text_lang='it' THEN 1 ELSE 0 END AS order_lang_it FROM city2tour AS c2t INNER JOIN tour AS t ON c2t.tour_id=t.id INNER JOIN (SELECT hashed_url, city_slug, MIN(connection_no_of_transfers) AS min_connection_no_of_transfers FROM fahrplan GROUP BY hashed_url, city_slug ) AS u ON c2t.hashed_url=u.hashed_url AND c2t.city_slug=u.city_slug WHERE c2t.reachable_from_country='SI' AND c2t.city_slug='ljubljana' AND t.search_column @@ websearch_to_tsquery('simple', 'triglav') ORDER BY order_lang_sl DESC, COALESCE(ts_rank(COALESCE(t.search_column, ''), COALESCE(websearch_to_tsquery('simple', 'triglav'), '')), 0) DESC, u.min_connection_no_of_transfers ASC, t.traverse DESC, c2t.min_connection_duration ASC, t.quality_rating DESC

[martinheppner]

SELECT 
t.id, 
t.provider, 
t.hashed_url, 
t.url, 
t.title, 
t.description, 
t.type, 
t.country, 
t.state, 
t.range_slug, 
t.range, 
t.search_column, 
t.text_lang, 
CASE WHEN t.text_lang='de' THEN 1 ELSE 0 END AS order_lang_de, 
CASE WHEN t.text_lang='en' THEN 1 ELSE 0 END AS order_lang_en, 
CASE WHEN t.text_lang='fr' THEN 1 ELSE 0 END AS order_lang_fr, 
CASE WHEN t.text_lang='sl' THEN 1 ELSE 0 END AS order_lang_sl, 
CASE WHEN t.text_lang='it' THEN 1 ELSE 0 END AS order_lang_it,
min(c2t.min_connection_duration) AS min_connection_duration, 
min(c2t.min_connection_no_of_transfers) AS min_connection_no_of_transfers, 
min(t.ascent) AS ascent, 
min(t.descent) AS descent, 
min(t.difficulty) AS difficulty, 
min(t.duration) AS duration, 
min(t.distance) AS distance, 
min(t.number_of_days) AS number_of_days, 
min(t.traverse) AS traverse, 
avg(t.quality_rating) AS quality_rating
FROM city2tour AS c2t 
INNER JOIN tour AS t 
ON c2t.tour_id=t.id 
WHERE c2t.reachable_from_country='SI' 
-- AND c2t.city_slug='ljubljana' 
-- AND t.search_column @@ websearch_to_tsquery('simple', 'triglav')
GROUP BY t.id, 
t.provider, 
t.hashed_url, 
t.url, 
t.title, 
t.description, 
t.type, 
t.country, 
t.state, 
t.range_slug, 
t.range, 
t.search_column, 
t.text_lang, 
CASE WHEN t.text_lang='de' THEN 1 ELSE 0 END, 
CASE WHEN t.text_lang='en' THEN 1 ELSE 0 END, 
CASE WHEN t.text_lang='fr' THEN 1 ELSE 0 END, 
CASE WHEN t.text_lang='sl' THEN 1 ELSE 0 END, 
CASE WHEN t.text_lang='it' THEN 1 ELSE 0 END 
ORDER BY order_lang_sl DESC,
-- COALESCE(ts_rank(COALESCE(t.search_column, ''), COALESCE(websearch_to_tsquery('simple', 'triglav'), '')), 0) DESC, 
min(c2t.min_connection_no_of_transfers) ASC, t.traverse DESC, ROUND(min(c2t.min_connection_duration) / 30, 0) ASC, t.quality_rating DESC