CODEWITHSUNDEEP
mysqlsql
bjk116
bjk116

Reputation: 529

What is the easiest way to make a MySQL function that variably creates the where clause?

In the program I am working on there is a query that runs on a hierarchical structure of tables. I have companies table, each company has locations (which has its own table), each location has areas, each area has assets, and each asset has projects. All of these (company, location, area, asset, project) has an active boolean field.

When users go to a screen, what is displayed is based on their personal filter, they can choose to view only active companies/locations/areas/assets/projects, or only inactive, or they have have neither selected and then it is both.

This is currently done by crafting a the query string with a bunch of if statements and the query itself has a large return so it can be slow, also it makes the code ugly and a little less easy to maintain/change. I want to speed this up by using a MySQL function.

Right now the only thing I can think is to make a specific case for each scenario. Someone doesn't have any filters - case 1. Someone wants to filter by only active/inactive companies - case 2. Only by locations - case 3 ... only projects - case 6. Then I'd have to deal with each case of each combination and now we're talking a lot of cases.

I figure my knowledge of SQL must be limited and there is probably a better way to do this. Any ideas?

What I was thinking is that the MySQL function declaration will be something like

FUNCTION `getData` (
    companyActive integer, 
    locationActive integer, 
    areaActive integer, 
    assetActive integer, 
    projectActive integer
)

where each integer would be :

So if companyActive = -1, there is no where clause for it, if it's 1 or 0, there is company.active = 1/0 depending.

EDIT: After some testing, this query was NOT the one slowing down my program so this was unnecessary haha, however I did learn something new today so thank you guys.

Upvotes: 0

Views: 57

Answers (1)

GMB
GMB

Reputation: 222432

It is possible to generate dynamic SQL from a function, but the logic probably will be quite similar to what you already developped in php.

One solution for your use case could be to juste write a SQL statement whose WHERE clause would take all your parameters and handle all cases at once. You would then have a single statement to execute, no matter which options were selected by the user.

SELECT
    ...
FROM
    company 
    INNER JOIN location ON ...
    INNER JOIN area ON ...
WHERE
    1 = 1
    AND ( :companyActive  = -1 OR company.active  = :companyActive )
    AND ( :locationActive = -1 OR location.active = :locationActive )
    AND ( :areaActive     = -1 OR area.active     = :areaActive )
    ...

Upvotes: 2

Related Questions