Canonical Tour

[martinheppner]

Every tour detail page (like https://www.zuugle.at/tour/462/wien) needs in the header a line like this:

<link rel="canonical" href="https://www.zuugle.at/tour/462/" hreflang="de-at"/>
<link rel="alternate" href="https://www.zuugle.si/tour/462/" hreflang="sl-si"/>

The correct URL is determined by this sql, which has to be implemented on the backend.

SELECT
tour_id,
city_slug,
url,
CASE WHEN ranking=1 THEN 'canonical' ELSE 'alternate' END AS canonical,
hreflang,
source_url
FROM (
	SELECT 
	RANK() OVER(PARTITION BY c2t.tour_id ORDER BY c2t.min_connection_no_of_transfers ASC, c2t.min_connection_duration ASC, c2t.stop_selector DESC, c2t.city_slug ASC) AS ranking,
	c2t.tour_id,
	c2t.city_slug,
	CONCAT('www.zuugle.', LOWER(c2t.reachable_from_country), '/tour/', c2t.tour_id, '/', c2t.city_slug) AS url,
	CASE WHEN c2t.reachable_from_country='SI' THEN 'sl-si' 
		  WHEN c2t.reachable_from_country='DE' THEN 'de-de'
		  WHEN c2t.reachable_from_country='IT' THEN 'it-it'
		  WHEN c2t.reachable_from_country='CH' THEN 'de-ch'
		  WHEN c2t.reachable_from_country='LI' THEN 'de-li'
		  WHEN c2t.reachable_from_country='FR' THEN 'fr-fr'
		  ELSE 'de-at' END AS hreflang,
	t.url AS source_url	
	FROM city2tour AS c2t
	INNER JOIN tour AS t
	ON t.id=c2t.tour_id
	WHERE c2t.tour_id=${id}
) AS a
ORDER BY 1 ASC, 4 DESC, city_slug ASC

[martinheppner]

E-Mail von Google dazu: