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.
Best Answer
Seems simple. Build the
VIEW
without the last 5 lines. Then use those 5 lines when you use theVIEW
as if it were aTABLE
.If those
JOINs
are not "many:one", you will get an inflatedCOUNT(*)
.