Save typing effort for complex MySQL queries with custom variables or procedures

To save time and because it’s not that smart to repeatedly retype the same things multiple times, you can user custom variables in MySQL like that:

SET
@store := 1,
@name_id := 71,
@short_desc_id := 72,
@desc_id := 73;

SELECT
   CPE.sku      AS sku,
   CPEVN.value  AS name,
   CPEVSD.value AS short_description,
   CPEVD.value  AS description
FROM catalog_product_entity AS CPE
  LEFT JOIN catalog_product_entity_varchar AS CPEVN
    ON (CPE.entity_id = CPEVN.entity_id
        AND CPEVN.attribute_id = @name_id)
  LEFT JOIN catalog_product_entity_text AS CPEVSD
    ON (CPE.entity_id = CPEVSD.entity_id)
       AND CPEVSD.attribute_id = @short_desc_id
  LEFT JOIN catalog_product_entity_text AS CPEVD
    ON (CPE.entity_id = CPEVD.entity_id
        AND CPEVD.attribute_id = @desc_id);

You first have to run the SET query. Afterwards the variables are available for the whole session. Then you can fire the SELECT with the custom variables.

Another possibility would be the usage of a MySQL procedure:

DELIMITER //
DROP PROCEDURE IF EXISTS getTexts //
CREATE PROCEDURE getTexts(nameId INT, shortDescId INT, descId INT)
  BEGIN
    SELECT
      CPE.sku      AS sku,
      CPEVN.value  AS name,
      CPEVSD.value AS short_description,
      CPEVD.value  AS description
    FROM catalog_product_entity AS CPE
      LEFT JOIN catalog_product_entity_varchar AS CPEVN
        ON (CPE.entity_id = CPEVN.entity_id
            AND CPEVN.attribute_id = nameId)
      LEFT JOIN catalog_product_entity_text AS CPEVSD
        ON (CPE.entity_id = CPEVSD.entity_id
            AND CPEVSD.attribute_id = shortDescId)
      LEFT JOIN catalog_product_entity_text AS CPEVD
        ON (CPE.entity_id = CPEVD.entity_id
            AND CPEVD.attribute_id = descId);
  END
//
DELIMITER ;

This procedure could be called like that:

CALL getTexts(71, 72, 73);

By the way, these queries are getting the product name, the short description and the description out of a Magento database.

Leave a Reply

Your email address will not be published.

 

This site uses Akismet to reduce spam. Learn how your comment data is processed.