SQL manager - it's an amazing time to be alive!

[the.rampage.rado]

(this post will be shorter as I once wrote it to the end and mis-clicked, exiting the page and I don't have the nerves to write it once again... 😄 )

In general despite using PS for over 10 years and thirty bees since the start of the project I only discovered how powerful the SQL manager is couple months back. Using it you can make very custom queries for almost every part of your shop. Currently I use it mostly for data normalization for my products catalog but you can think of many other useful queries to load and use on daily basis.

Keep in mind that those queries should be read only (I believe). Or could they be writing to the table also? 🙂

I use the following examples almost daily to speed up my manual work with the catalog. The queries could be very powerful for small/medium shops that don't do automatic import of items and all data is entered by hand to normalize the data and improve your shop appearance.

Please, keep in mind that every query should be adjusted with your table prefix (in this case tb_, it's not the real one in my case, don't try to hack me! 😛) and your personal parameters - id_lang, id_category, etc.

1. Pull out all products without WHS price - if you started your shop not populating this field and later discovered that Stats module relies on it to calculate profit use this query to sift through all your products.

SELECT p.id_product, pl.name
FROM tb_product p
LEFT JOIN tb_product_lang pl ON p.id_product = pl.id_product
WHERE (p.wholesale_price IS NULL OR p.wholesale_price = 0)
AND pl.id_lang = 2 -- change language here
GROUP BY p.id_product;

 

2. Pull products associated with Home - if you don't use Featured products module to display set of products in your homepage you don't need to associate products with home category (id 2 by default). If home is the default category for products this messes up the navigation of customers and does not display the proper breadcrumbs. Better remove the association.

SELECT p.id_product, p.reference, pl.name
FROM tb_product p
JOIN tb_category_product cp ON p.id_product = cp.id_product
JOIN tb_category c ON cp.id_category = c.id_category
JOIN tb_product_lang pl ON p.id_product = pl.id_product
JOIN tb_product_shop ps ON p.id_product = ps.id_product
WHERE c.id_category = 2 -- by default id.category for Home is 2
AND pl.id_lang = 2 -- change according to your preferred language
GROUP BY p.id_product;

 

3. Cyrillic letters in EN language - when your default lang is Cyrillic and you forget to enter EN (or other non-Cyrillic) title of the product it is written with the same value in both languages in title/meta/images, which is not good.

SELECT p.id_product, pl.name, pl.description, pl.description_short, 
       pl.meta_title, pl.meta_description, pl.meta_keywords, 
       pl.link_rewrite AS friendly_url, il.legend AS image_description
FROM tb_product p
JOIN tb_product_lang pl ON p.id_product = pl.id_product
LEFT JOIN tb_image i ON p.id_product = i.id_product
LEFT JOIN tb_image_lang il ON i.id_image = il.id_image AND il.id_lang = pl.id_lang
WHERE pl.id_lang = 4 -- adjust to your id_lang
  AND (pl.name REGEXP '[А-Яа-яЁё]' 
       OR pl.description REGEXP '[А-Яа-яЁё]' 
       OR pl.description_short REGEXP '[А-Яа-яЁё]'
       OR pl.meta_title REGEXP '[А-Яа-яЁё]' 
       OR pl.meta_description REGEXP '[А-Яа-яЁё]' 
       OR pl.meta_keywords REGEXP '[А-Яа-яЁё]'
       OR il.legend REGEXP '[А-Яа-яЁё]')
GROUP BY p.id_product;

 

4. < 50 characters in description

SELECT pl.id_product, pl.id_lang, pl.description, LENGTH(pl.description) as description_length
FROM tb_product_lang pl
WHERE LENGTH(pl.description) < 50 -- adjust to your liking

 

5. < 50 characters in short description

SELECT pl.id_product, pl.id_lang, pl.description_short, LENGTH(pl.description_short) as short_description_length
FROM tb_product_lang pl
WHERE LENGTH(pl.description_short) < 50 -- adjust to your liking

 

6. Products associated with a category - useful when you want to move products from one category

SELECT p.id_product, p.reference, pl.name
FROM tb_product p
JOIN tb_category_product cp ON p.id_product = cp.id_product
JOIN tb_category c ON cp.id_category = c.id_category
JOIN tb_product_lang pl ON p.id_product = pl.id_product
JOIN tb_product_shop ps ON p.id_product = ps.id_product
WHERE c.id_category = 35 AND pl.id_lang = 2 -- adjust id_category and id_lang
GROUP BY p.id_product;

 

7. Products without attachment files - your product must have attachment files (catalog, data sheet, some other stuff) but you forget to associate it on time?

SELECT pl.id_product, pl.name
FROM tb_product_lang pl
LEFT JOIN tb_product_attachment pa ON pl.id_product = pa.id_product
WHERE pl.id_lang = 4 -- adjust display id lang
AND pa.id_attachment IS NULL
GROUP BY pl.id_product;

 

8. Custom modules queries - in this case products that don't have associated size chart from the Warehouse size chart module:

SELECT pl.id_product, pl.name
FROM tb_product_lang pl
LEFT JOIN tb_iqitsizeguide_product sg ON pl.id_product = sg.id_product
WHERE pl.id_lang = 4 -- adjust id_lang 
AND sg.id_guide IS NULL
GROUP BY pl.id_product;

 

9. Description contains XYZ string - I used it to change some table formatting that was applicable to only few products

SELECT pl.id_product, pl.id_lang, pl.description, pl.description_short
FROM tb_product_lang pl
WHERE pl.id_lang IN (2, 4) -- change id_lang to search in the lang you need
  AND pl.description LIKE '%XYZ%' -- change XYZ to the string you are searching for;

...But what does the military have?! 🙂

Can you think of other useful queries? Write them down as a comment!