How to dynamically generate SQL query based on user's selections?

Question

I need to create a GUI, using which users can select several attributes which will be used to query the database to find suitable persons. I'm looking for ideas how to dynamically generate the database query according to user's choices.

Query will contain several fields, but to get the idea I will include only three of those below as an example:

• Occupation - there can be 0 to n occupation strings. If occupation strings are given, one of them have to match.

• Age - age can be given as:

  1. exact match (30)
  2. range (e.g. 30-40)
  3. less than a value (-40)
  4. more than a value (30-)

Age parameter is optional in the query. In addition, user can specify whether age is a required parameter. If it's not required, and a person does not have age is his/her profile, age criteria is ignored for this person.

• Height - similar as age

Example queries:

No criteria has been given:

select * from persons

Only occupation has been given:

select * from persons where occupation = 'dentist'

Several occupations have been given:

select * from persons where (occupation = 'dentist' or occupation = 'engineer')

Age has been given as a greater than value, and it's required to exist on person's profile:

select * from persons where age >= 30

Height has been given as a range, and it's not required to exist on person's profile:

select * from persons where (height is null or (height >= 30 and height <= 40))

Combination of different criteria:

select * from persons where occupation = 'dentist' and age >= 30 and (height is null or (height >= 30 and height <= 40))

I have already implemented code which is capable of generating queries as strings, but it certainly is not too pretty. I'm looking for ideas what would be the most efficient and prettiest way to achieve this.

Answer 1

This will not directly answer your question, but here is a little tip for you, instead of writing your query like this:

select * from persons where (occupation = 'dentist' or occupation = 'engineer')

Try this:

select * from persons where occupation IN ('dentist','engineer')

This is easier to read and easy for your PHP script to generate.

Answer 2

It looks liek you have a finite number of queries that could be generated, so I suggest writing these all with parameters, then using the selection logic to work out which to call and set up the parameter values.

Answer 3

Try something like Zend_Db_Select. It provides a (fluent) interface for generating queries and handles the syntax creation for you, e.g.

$select = $db->select();
$select->from( /* ...specify table and columns... */ )
       ->where( /* ...specify search criteria... */ )
       ->where( /* ...specify other criteria... */ )
       ->order( /* ...specify sorting criteria... */ );

I had a similar requirement in one my recent projects where users have a configuration file containing filter criteria that are automatically applied to certain tables in the database, thus limiting what the users are allowed to see, e.g, limit Products by fixed Customer but still allow dynamic filtering through GUI, e.g. for a Product category.

I solved it by having my model return a base query and then running this base query through a decorator that would apply all criteria the user has in his config, so for instance (faux code).

    request = Request->getParams()            // selection criteria set from GUI
    sql     = Products->getBaseQuery(request) // basic query for requested View
    sql     = Decorator->applyUserConfig(sql) // custom fixed user filter
    results = sql->execute()