Κατασκεύη SEO friendly page title (meta title) μέσω MySQL (1.7.x)

[aramonkg]

Ένας τρόπος για να φτίαξετε meta titles της μορφής, [Shop Name] [Category Tree] [Product Name], για όλα τα προϊόντα που υπάρχουν ήδη.

Βήμα 1: Δημιουργία πίνακα με τα αριθμητικά ID των κατηγοριών που ανήκει κάθε προϊόν (εξαιρώντας το Home και μόνο για τη γλώσσα που μας ενδιαφέρει).

CREATE TABLE ps_category_product_exp (id INT AUTO_INCREMENT PRIMARY KEY) AS
	SELECT id_product,name FROM `ps_category_lang` JOIN ps_category_product ON `ps_category_lang`.`id_category`=`ps_category_product`.`id_category`
    		WHERE name!='Home' AND id_lang=2 
        		ORDER BY `ps_category_product`.`id_category` ASC 

 Βήμα 2: Δημιουργία πίνακα με τις αλφαριθμητικές τιμές των κατηγοριών από τα αριθμητικά ID σε ένα column.

CREATE TABLE ps_category_product_str AS 
	SELECT id_product,GROUP_CONCAT(`name` ORDER BY `id` ASC SEPARATOR ' | ') AS category_name FROM `ps_category_product_exp` 
    		GROUP BY id_product

Βήμα 3: Αλλαγή του meta title για κάθε προϊόν με βάση τον πίνακα του βήματος 2.

UPDATE `ps_product_lang`,`ps_category_product_str` 
	SET `ps_product_lang`.`meta_title`= CONCAT_WS(' | ','Your Shop Name',`ps_category_product_str`.`category_name`,`ps_product_lang`.`name`) 
		WHERE `ps_category_product_str`.`id_product`=`ps_product_lang`.`id_product` AND `ps_product_lang`.`id_lang`=2

 

[sanctusmob]

Δεν χρειάζεται να φτιάξεις extra πίνακες.

Γίνετε και με ένα query.

 

[aramonkg]

3 hours ago, sanctusmob said:

Δεν χρειάζεται να φτιάξεις extra πίνακες.

Γίνετε και με ένα query.

 

Για αυτό έγραψα ότι είναι ένας τρόπος.  

Ο στόχος ήταν να το κάνω σε ένα query αλλά είχα μια δυσκολία στο να μετατρέψω τα results του SELECT σε strings που δέχεται το CONCAT...

¨Οπως κατάλαβες δεν είμαι guru της MySQL ή σωστότερα της MariaDB.

[sanctusmob]

Αν και δεν συμφωνώ με τα περιεχόμενα που διάλεξες για να μπούνε στο Meta title (με το full category path). Ορίστε το query

SELECT 
	info.ProductID, 
	info.id_lang,
	LEFT(CONCAT(info.ManufacturerName," ", info.ProductName, " ",info.reference," ", info.CategoryPath),128) AS MetaTitle
FROM 
	(
		SELECT 
			p.id_product AS ProductID,
			pl.NAME AS ProductName, 
			m.NAME AS ManufacturerName,
			p.reference,
			pl.id_lang,
			(
				SELECT GROUP_CONCAT(cl.name ORDER BY c2.level_depth ASC SEPARATOR ' > ')
				FROM ps_category_lang AS cl
				JOIN ps_category AS c2 ON c2.id_category = cl.id_category
				WHERE c2.nleft <= c.nleft AND c2.nright >= c.nright AND c2.id_category > '2'
			) AS CategoryPath
		FROM ps_product_lang AS pl
			LEFT JOIN ps_product AS p ON p.id_product = pl.id_product
			LEFT JOIN ps_manufacturer AS m ON m.id_manufacturer = p.id_manufacturer
			LEFT JOIN ps_category_lang AS cl ON cl.id_category = p.id_category_default
			LEFT JOIN ps_category AS c ON c.id_category = cl.id_category
		WHERE pl.id_lang = cl.id_lang
	) AS info

Απλός πρόσθεσα το όνομα του κατασκευαστή και το reference σε περίπτωση που δεν είναι μέσα στο Product Name (και έτσι πρέπει).

Από εδώ και πέρα ο καθένας μπορεί εύκολα να το αλλάξει το παραπάνω select (π.χ. αν θέλει να προσθέσει το ean13 ή να αφαιρέσει το Manufacturer Name) και μετά να κάνει το update με join το παραπάνω query.

UPDATE ps_product_lang AS ppl
JOIN 
(
	SELECT 
		info.ProductID, 
		info.id_lang,
		LEFT(CONCAT(info.ManufacturerName," ", info.ProductName, " ",info.reference," ", info.CategoryPath),128) AS MetaTitle
	FROM 
		(
			SELECT 
				p.id_product AS ProductID,
				pl.NAME AS ProductName, 
				m.NAME AS ManufacturerName,
				p.reference,
				pl.id_lang,
				(
					SELECT GROUP_CONCAT(cl.name ORDER BY c2.level_depth ASC SEPARATOR ' > ')
					FROM ps_category_lang AS cl
					JOIN ps_category AS c2 ON c2.id_category = cl.id_category
					WHERE c2.nleft <= c.nleft AND c2.nright >= c.nright AND c2.id_category > '2'
				) AS CategoryPath
			FROM ps_product_lang AS pl
				LEFT JOIN ps_product AS p ON p.id_product = pl.id_product
				LEFT JOIN ps_manufacturer AS m ON m.id_manufacturer = p.id_manufacturer
				LEFT JOIN ps_category_lang AS cl ON cl.id_category = p.id_category_default
				LEFT JOIN ps_category AS c ON c.id_category = cl.id_category
			WHERE pl.id_lang = cl.id_lang
		) AS info
) AS MetaInfo ON MetaInfo.ProductID = ppl.id_product SET ppl.meta_title = MetaInfo.MetaTitle
WHERE ppl.id_lang = MetaInfo.id_lang

 

Edited March 26, 2019 by sanctusmob
SQL format to be more readable (see edit history)

[aramonkg]

3 hours ago, sanctusmob said:

Αν και δεν συμφωνώ με τα περιεχόμενα που διάλεξες για να μπούνε στο Meta title (με το full category path). Ορίστε το query

SELECT 
	info.ProductID, 
	info.id_lang,
LEFT(CONCAT(info.ManufacturerName," ", info.ProductName, " ",info.reference," ", info.CategoryPath),128) AS MetaTitle
FROM 
	(
SELECT 
		p.id_product AS ProductID,
		pl.NAME AS ProductName, 
		m.NAME AS ManufacturerName,
		p.reference,
		pl.id_lang,
		(
SELECT GROUP_CONCAT(cl.name
ORDER BY c2.level_depth ASC SEPARATOR ' > ')
FROM ps_category_lang AS cl
JOIN ps_category AS c2 ON c2.id_category = cl.id_category
WHERE c2.nleft <= c.nleft AND c2.nright >= c.nright AND c2.id_category > '2') AS CategoryPath
FROM ps_product_lang AS pl
LEFT JOIN ps_product AS p ON p.id_product = pl.id_product
LEFT JOIN ps_manufacturer AS m ON m.id_manufacturer = p.id_manufacturer
LEFT JOIN ps_category_lang AS cl ON cl.id_category = p.id_category_default
LEFT JOIN ps_category AS c ON c.id_category = cl.id_category
WHERE pl.id_lang = cl.id_lang) AS info

Απλός πρόσθεσα το όνομα του κατασκευαστή και το reference σε περίπτωση που δεν είναι μέσα στο Product Name (και έτσι πρέπει).

Από εδώ και πέρα ο καθένας μπορεί εύκολα να το αλλάξει το παραπάνω select (π.χ. αν θέλει να προσθέσει το ean13 ή να αφαιρέσει το Manufacturer Name) και μετά να κάνει το update με join το παραπάνω query.

UPDATE ps_product_lang AS ppl
JOIN 
(
SELECT 
	info.ProductID, 
	info.id_lang,
LEFT(CONCAT(info.ManufacturerName," ", info.ProductName, " ",info.reference," ", info.CategoryPath),128) AS MetaTitle
FROM 
	(
SELECT 
		p.id_product AS ProductID,
		pl.NAME AS ProductName, 
		m.NAME AS ManufacturerName,
		p.reference,
		pl.id_lang,
		(
SELECT GROUP_CONCAT(cl.name
ORDER BY c2.level_depth ASC SEPARATOR ' > ')
FROM ps_category_lang AS cl
JOIN ps_category AS c2 ON c2.id_category = cl.id_category
WHERE c2.nleft <= c.nleft AND c2.nright >= c.nright AND c2.id_category > '2') AS CategoryPath
FROM ps_product_lang AS pl
LEFT JOIN ps_product AS p ON p.id_product = pl.id_product
LEFT JOIN ps_manufacturer AS m ON m.id_manufacturer = p.id_manufacturer
LEFT JOIN ps_category_lang AS cl ON cl.id_category = p.id_category_default
LEFT JOIN ps_category AS c ON c.id_category = cl.id_category
WHERE pl.id_lang = cl.id_lang) AS info) AS MetaInfo ON MetaInfo.ProductID = ppl.id_product SET ppl.meta_title = MetaInfo.MetaTitle
WHERE ppl.id_lang = MetaInfo.id_lang

 

Υλικό για μελέτη.

[aramonkg]

Δοκίμασα τα queries, άλλαξα βέβαια κάποια για το Metatitle, αλλά αντιμετώπισα κάποια θέματα με το CategoryPath...

Αρχικά το query επαναλάμβανε τις κατηγορίες, π.χ. κατ1>κατ1>υποκατ2>υποκατ2, αυτό το έλυσα με DISTINCT

...GROUP_CONCAT(DISTINCT cl.name)...

ώστόσο για κάποιο λόγο μου και ενώ το CategoryPath ειναι σωστό μου προσθέτει στα δεξιά του άσχετη κατηγορία, πχ. κατ1(σωστή)>υποκατ2(σωστή)>υποκατ3(λάθος).

Τι σημαίνουν αυτά τα nleft και nright; Τα είδα στη βάση αλλά δε έβγαλα νοήμα...