How to aggregate 2 procuderes into one in MySQL?-CodePudding

I have 2 procedures that look almost the same. One receives a location and a price and do stuff and the other one receives the experience and the price.

First one:

-- Returns: service providers in given location and price
DELIMITER &&  
CREATE PROCEDURE get_service_providers_location_price (IN id_location INT,IN price DOUBLE,IN limite INT, IN inicio INT)  
BEGIN  
    SELECT user.idUser, user.name,user.lastActivity,user.active,serviceprovider.description,location.name AS location, location.cordsX, location.cordsY, file.image FROM user
    INNER JOIN location ON user.idLocation = location.idLocation
    INNER JOIN file ON user.idUser = file.idUser
    INNER JOIN serviceprovider ON user.idUser = serviceprovider.idSP 
    INNER JOIN category_has_serviceprovider ON serviceprovider.idSP = category_has_serviceprovider.idServiceProvider 
        WHERE user.type = 3 AND user.idLocation = id_location  
            AND (category_has_serviceprovider.price <= price OR category_has_serviceprovider.price IS NULL) and serviceprovider.idSubscription != 1 
    ORDER BY CASE WHEN serviceprovider.idSubscription in (5,6,7) then 1 else 2 end, serviceprovider.endSub ASC LIMIT limite OFFSET inicio;
END &&  
DELIMITER ;

Second one:

-- Returns: service providers in given experience and price
DELIMITER &&  
CREATE PROCEDURE get_service_providers_experience_price (IN experience INT, IN price DOUBLE,IN limite INT, IN inicio INT)  
BEGIN  
    SELECT user.idUser, user.name,user.lastActivity,user.active,serviceprovider.description,location.name AS location, location.cordsX, location.cordsY, file.image FROM user
    INNER JOIN location ON user.idLocation = location.idLocation
    INNER JOIN file ON user.idUser = file.idUser
    INNER JOIN serviceprovider ON user.idUser = serviceprovider.idSP 
    INNER JOIN category_has_serviceprovider ON serviceprovider.idSP = category_has_serviceprovider.idServiceProvider 
        WHERE user.type = 3
            AND (category_has_serviceprovider.price <= price OR category_has_serviceprovider.price IS NULL) AND category_has_serviceprovider.experience >= experience and serviceprovider.idSubscription != 1 
    ORDER BY CASE WHEN serviceprovider.idSubscription in (5,6,7) then 1 else 2 end, serviceprovider.endSub ASC LIMIT limite OFFSET inicio;
END &&  
DELIMITER ;

As you can see, only the WHERE clause changes. Is it possible, in MySQL, to aggregate this 2 procedures into one? Because I have like 5 procedures that look the same but it only changes the WHERE clause and I find it annoying doing a separate procedure for each case.

CodePudding user response：

For example, you may use this:

CREATE PROCEDURE get_service_providers_price (IN experience INT,IN id_location INT,IN price DOUBLE,IN limite INT, IN inicio INT)  
BEGIN  
SELECT user.idUser, user.name,user.lastActivity,user.active,serviceprovider.description,location.name AS location, location.cordsX, location.cordsY, file.image FROM user
INNER JOIN location ON user.idLocation = location.idLocation
INNER JOIN file ON user.idUser = file.idUser
INNER JOIN serviceprovider ON user.idUser = serviceprovider.idSP 
INNER JOIN category_has_serviceprovider ON serviceprovider.idSP = category_has_serviceprovider.idServiceProvider 
WHERE user.type = 3 
  AND (category_has_serviceprovider.price <= price OR category_has_serviceprovider.price IS NULL) 
  and serviceprovider.idSubscription != 1 
  AND CASE WHEN experience IS NOT NULL
           THEN category_has_serviceprovider.experience >= experience
           ELSE user.idLocation = id_location  
           END
ORDER BY CASE WHEN serviceprovider.idSubscription in (5,6,7) then 1 else 2 end, serviceprovider.endSub ASC LIMIT limite OFFSET inicio;

If provided IN experience INT is set to some value then the condition by it is applied. If you provide NULL for this parameter then the condition by IN id_location INT is applied.

Pay attention - now your SP have 5 parameters, not 4.

PS. Your SP contains one SQL statement - hence BEGIN-END and DELIMITER not needed.

PPS. Using similar method you may create SP which may apply both one of the conditions and both of them at the same time or none. For example, it can be:

AND CASE WHEN experience IS NOT NULL AND id_location IS NOT NULL  -- apply both parameters filtering
         THEN category_has_serviceprovider.experience >= experience AND user.idLocation = id_location
         WHEN experience IS NOT NULL   -- apply filtering by experience  only
         THEN category_has_serviceprovider.experience >= experience
         WHEN id_location IS NOT NULL   -- apply filtering by location only
         THEN user.idLocation = id_location
         ELSE 1    -- not filter, return all rows
         END

PPPS. If you want to have 2 separate functions but one copy of the code (for example, these functions names are already used in a bunch of code) then you may do this:

CREATE PROCEDURE get_service_providers_location_price (IN id_location INT,IN price DOUBLE,IN limite INT, IN inicio INT)
CALL get_service_providers_price (NULL, id_location, price, limite, inicio);

CREATE PROCEDURE get_service_providers_experience_price (IN experience INT, IN price DOUBLE,IN limite INT, IN inicio INT)
CALL get_service_providers_price (experience, NULL, price, limite, inicio);

CodePudding user response：

You could use IFNULL. Either pass the experience or the id_location value and use NULL for another.

It's also a good practice to have naming scheme (here in_-prefix) for the parameters so that the parameters are disticnt from the column names.

DELIMITER &&  
CREATE PROCEDURE get_service_providers_experience_price (
in_experience INT, 
in_id_location INT, 
in_price DOUBLE,
in_limite INT, 
in_inicio INT
)  
BEGIN  
SELECT 
  user.idUser, 
  user.name,
  user.lastActivity,
  user.active,
  serviceprovider.description,
  location.name AS location, 
  location.cordsX, 
  location.cordsY, 
  file.image 
FROM user
  INNER JOIN location ON user.idLocation = location.idLocation
  INNER JOIN file ON user.idUser = file.idUser
  INNER JOIN serviceprovider ON user.idUser = serviceprovider.idSP 
  INNER JOIN category_has_serviceprovider ON serviceprovider.idSP = category_has_serviceprovider.idServiceProvider 
WHERE user.type = 3
  AND (category_has_serviceprovider.price <= in_price OR category_has_serviceprovider.price IS NULL) 
  AND category_has_serviceprovider.experience >= IFNULL(in_experience, category_has_serviceprovider.experience)
  AND user.idLocation = IFNULL(id_location, user.idLocation)
  AND serviceprovider.idSubscription != 1 
ORDER BY CASE WHEN serviceprovider.idSubscription in (5,6,7) then 1 else 2 end, serviceprovider.endSub ASC 
LIMIT in_limite OFFSET in_inicio;

END 
&&  
DELIMITER ;

CodePudding user response：

If dynamic sql is a possibility you could simplify your code for example

DROP PROCEDURE P;   
DELIMITER &&  
CREATE PROCEDURE P(IN ANALTYPE VARCHAR(20), IN INVALUE INT,IN price DOUBLE,IN limite INT, IN inicio INT)  
BEGIN  
SET @SQL = CONCAT('    SELECT user.idUser, user.name,user.lastActivity,user.active,serviceprovider.description,location.name AS location, location.cordsX, location.cordsY, file.image FROM user
    INNER JOIN location ON user.idLocation = location.idLocation
    INNER JOIN file ON user.idUser = file.idUser
    INNER JOIN serviceprovider ON user.idUser = serviceprovider.idSP 
    INNER JOIN category_has_serviceprovider ON serviceprovider.idSP = category_has_serviceprovider.idServiceProvider 
    WHERE ',
     CASE WHEN ANALTYPE =  'EXPERIENCE' THEN    'category_has_serviceprovider.experience >= '
          WHEN ANALTYPE  = 'LOCATION'   THEN  'user.idLocation = '
     END
     ,
     INVALUE,
            '   AND user.type = 3
            AND (category_has_serviceprovider.price <= ' ,PRICE ,' OR category_has_serviceprovider.price IS NULL) 
                and serviceprovider.idSubscription != 1 
    ORDER BY CASE WHEN serviceprovider.idSubscription in (5,6,7) then 1 else 2 end, serviceprovider.endSub ASC 
     LIMIT ', limite,' OFFSET ', inicio,';'
    );
    SELECT @SQL;
END &&  
DELIMITER ;

The bulk of the code is the same and the analtype decides the condition to be used in the code build

CALL P('EXPERIENCE', 100, 200,300,400)

Produces this sql statement.

SELECT user.idUser, user.name,user.lastActivity,user.active,serviceprovider.description,location.name AS location, location.cordsX, location.cordsY, file.image FROM user
    INNER JOIN location ON user.idLocation = location.idLocation
    INNER JOIN file ON user.idUser = file.idUser
    INNER JOIN serviceprovider ON user.idUser = serviceprovider.idSP
    INNER JOIN category_has_serviceprovider ON serviceprovider.idSP = category_has_serviceprovider.idServiceProvider
    WHERE category_has_serviceprovider.experience >= 100        AND user.type = 3
            AND (category_has_serviceprovider.price <= 200 OR category_has_serviceprovider.price IS NULL)
                        and serviceprovider.idSubscription != 1
    ORDER BY CASE WHEN serviceprovider.idSubscription in (5,6,7) then 1 else 2 end, serviceprovider.endSub ASC
         LIMIT 300 OFFSET 400;

and CALL P('LOCATION', 100, 200,300,400);

SELECT user.idUser, user.name,user.lastActivity,user.active,serviceprovider.description,location.name AS location, location.cordsX, location.cordsY, file.image FROM user
    INNER JOIN location ON user.idLocation = location.idLocation
    INNER JOIN file ON user.idUser = file.idUser
    INNER JOIN serviceprovider ON user.idUser = serviceprovider.idSP
    INNER JOIN category_has_serviceprovider ON serviceprovider.idSP = category_has_serviceprovider.idServiceProvider
    WHERE user.idLocation = 100 AND user.type = 3
            AND (category_has_serviceprovider.price <= 200 OR category_has_serviceprovider.price IS NULL)
                        and serviceprovider.idSubscription != 1
    ORDER BY CASE WHEN serviceprovider.idSubscription in (5,6,7) then 1 else 2 end, serviceprovider.endSub ASC
         LIMIT 300 OFFSET 400;

you can then submit these using process defined here - https://dev.mysql.com/doc/refman/8.0/en/sql-prepared-statements.html