3

I am working in a PHP application and some "complex" queries are starting to appear in the code. Because of the complexity I am not able to use any ORM and the only resource I have is a plain SQL and PHP MySQL native functions which I don't like.

Without more here is one of the queries I want to convert into a view:

SELECT
  COUNT(*) AS 'rec',
  CONCAT(
      IF(agreement_list.ActiveFlag, '', 'Agreement is Inactive.'),
      IF(agreement_type.ActiveFlag, '', 'Agreement Type is Inactive.'),
      IF(distributor.ActiveFlag, '', 'License Distributor is InActive.'),
      IF(agreement_distributor.ActiveFlag, '', 'Agreement Distributor is InActive.'),
      IF(customer.ActiveFlag, '', 'Customer is Inactive.'),
      IF(cf_program_level.ActiveFlag, '', 'Program Level is Inactive.')
  ) AS errormessage,
  IF((agreement_list.ActiveFlag + agreement_type.ActiveFlag + distributor.ActiveFlag + agreement_distributor.ActiveFlag + customer.ActiveFlag + cf_program_level.ActiveFlag) < 6, 1, 0 ) AS error
FROM
  license
  JOIN agreement_list ON (agreement_list.AgreementTypeID = license.AgreementTypeID AND agreement_list.CustomerSiteID = license.CustomerSiteID AND agreement_list.Source = license.Source)
  JOIN customer ON (customer.id = license.CustomerSiteID AND license.source = customer.Source)
  JOIN distributor ON (distributor.DistributorID = license.DistributorID AND license.source = distributor.Source)
  JOIN distributor AS agreement_distributor ON (agreement_distributor.DistributorID = agreement_list.DistributorID AND agreement_list.source = agreement_distributor.Source)
  JOIN agreement_type ON (agreement_type.AgreementTypeID = license.AgreementTypeID AND license.source = agreement_type.Source)
  JOIN cf_program_level ON (cf_program_level.CFProgramLevelID = '{$CFProgramLevelID}' AND license.source = cf_program_level.Source)
WHERE
  license.AgreementTypeID = '{$AgreementTypeID}'
  AND license.CustomerSiteID = '{$CustomerSiteID}'
  AND license.Source = '{$Source}'

$CFProgramLevelID, $AgreementTypeID, $CustomerSiteID, $Source are parameters coming from PHP and that's my only problem. How do I pass a parameter to a view if it's possible?

I am using MySQL 5.6 at the moment.

  • What you are asking for is a function returning a table and AFAIK, MySQL does not yet support this. If you want to use a view you must rephrase it so that the parameters are exposed in the select list. However, in my experience, MySQL does not do a great job of pushing predicates inside views, so the performance will likely suffer. AFAIK, your best shot would be a procedure. – Lennart Sep 11 '17 at 5:46
2

Seems simple. Build the VIEW without the last 5 lines. Then use those 5 lines when you use the VIEW as if it were a TABLE.

If those JOINs are not "many:one", you will get an inflated COUNT(*).

  • 1
    You'd also need to define the view to GROUP BY the columns of interest, so that you could invoke it with an external WHERE, and this won't perform well... using aggregate functions forces a view to use the TEMPTABLE algorithm instead of MERGE, rendering the entire view then filtering for the one row you really wanted. A stored proc with parameters seems like the way to go. – Michael - sqlbot Sep 9 '17 at 18:55
2

Better yet, you can pass parameters to your views in a simple manner by creating a Function to GET your values from Session Variables.
See www.stackoverflow.com/questions/14511760 for the technique. This is a copy of my create function you may wish to pattern after.

DELIMITER //

CREATE FUNCTION fn_getcase_id()    
RETURNS MEDIUMINT(11)
DETERMINISTIC NO SQL
BEGIN
# see stackoverflow.com/questions/14511760 and read ALL the info TWICE or MORE.  wh 04/13/2017
    RETURN @sv_case_id;
END//

DELIMITER ;

You will need to create 4 similar FN (one for each variable).

0

No can do. At least not directly. Use a procedure that populates a temporary table and reference the table in your code.

0

A functional alternative would be to encapsulate the view query in a procedure, with parameter passing. This is a simple solution to improve the problem of slow VIEWS with multiple joins queries between multiple tables.

DELIMITER ;;
CREATE PROCEDURE `SP_QUERY_VIEW_WITH_PARAMETERS`(IN p_having VARCHAR(300))
    COMMENT 'Executes the statement'
BEGIN
  SET @v_having = p_having;
  SET @v_sql=CONCAT('SELECT 
                    id    AS id_emp  ,
                    user  AS emp_name,
                    .
                    .
                    .
                    FROM table1
                    UNION ALL
                    SELECT 
                    idtifier_us    AS id_emp  ,
                    description    AS emp_name,
                    .
                    .
                    .
                    FROM table2');
SET @v_sql2 = CONCAT(@v_sql,@v_having);
  PREPARE stmt FROM @v_sql2;
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt;
END ;;
DELIMITER ;


CALL `SP_QUERY_VIEW_WITH_PARAMETERS`('having id_emp=63 and emp_name like ''VANDERLEI%'' and created_at between ''2019-05-01'' and ''2019-05-17''  ')

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.