How to create a dynamic query from a list of fields?

Question

I am trying to build a dynamic query using PHP and MySQL.

What I have done is created a table (ie. field_relations) This field has 5 column

1. field_name (the name of a field "ie. account_id, account_name....")
2. display_label (The way how the field should be displaced to the use "ie. Account Id, Name")
3. table_name (the table where this field belong to "ie. accounts")
4. related_to (the field relation to a different table "if any." The default value is NULL)
5. related_to_field (the field where it points to "if any." The default value is NULL)

Here is a sample data field_name display_label table_name related_to related_to_field account_id Account ID accounts NULL NULL account_name Name accounts NULL NULL first_name First Name contacts NULL NULL last_name Last Name contacts NULL NULL contact_id Contact ID contacts NULL NULL account_id Account ID contacts accounts account_id task_id Task ID tasks NULL NULL subject Subject tasks NULL NULL owner_id Assigned To contacts contacts contact_id daily_sales Sales transactions accounts account_id sold_on Sold On transactions NULL NULL

So if I create a HTML form with 3 seconds

1. Pick column to display
2. Add formula to columns (optional)
3. Pick Condition clause (optional)
4. "Display Results" button.

The first part of ths form will display all values that are list in the display_label column.

If a user picked Name, First Name, Last Name

Then the query will need to look like this

SELECT accounts.account_name, contacts.first_name, contacts.last_name
FROM accounts 
INNER JOIN contacts ON contacts.account_id = accounts.account_id

after the query is done it will be executed.

Or, if the user selected "Name, Sales." Then the user want to apply SUM function on column daily_sales. And finally the user selected a filter for Sold On between '2014-01-01 00:00:00' AND '2014-10-01 00:00:00'

Then the query will need to look like this

SELECT accounts.account_name, SUM(daily_sales) AS daily_sales
FROM accounts 
LEFT JOIN sales ON sales.account_id = accounts.account_id
WHERE sales.sold_on BETWEEN '2014-01-01 00:00:00' AND '2014-10-01 00:00:00'
GROUP BY accounts.account_name

after the query is done it will be executed.

How can I generate such a query? do I need to add more column to the field_relations table?

I am not worried on how to build the PHP form to capture the user specs but I am trying to figure out how to generate they MySQL query correctly?

Thank you in advance for your help and time.

Answer 1

Why don't you use a ORM? Doctrine in this case is very useful:

http://docs.doctrine-project.org/projects/doctrine-orm/en/latest/tutorials/getting-started.html

You can add entity and fields dinamically and join them together: easy to learn and to implement.

Answer 2

First of all, maybe you'd better give a look at any one of several ORM (Object Relationship Management) systems available for PHP/MySQL.

But it's relatively easy to reinvent the wheel, provided you keep it small (which means that you can only resolve very straightforward queries).

If that's the case, let's say we only need inner joins and they are all of the one-to-many type (actually that's not a strict requirement, as we shall see). We can build a DIY ORM (I suspect a more apt name would be something like 'Diy Orm Cthulhu Fhtagn')

First step is to store this information somewhere, such as an array. One entry for all possible JOINs. Also you would need to describe your table to the system. You could also have the system query MySQL to retrieve tables and field names, possibly once in a while from a separate utility that would generate the PHP code.

// This just maps what fields are in what tables
$tables = array(
    'contacts' => array(
        'first_name' => true /* or an array of information such as SQL type, etc. */
    );
);

// This maps all the JOINs
$orm = array(
    'contacts' => array(
        'accounts' => array(
            'on'    => array( 'account_id', 'account_id' ),
            'type'  => 'LEFT JOIN', //
        )
    )
);

So you start with a list of $selectFields. You copy these fields into $unresolvedFields, and start examining them one after the other. Your target is to resolve all fields.

The pseudo code (actually not so pseudo):

while (!empty($unresolvedFields)) {
    $changes = false;
    // Try to resolve one of them.
    foreach ($unresolvedFields as $i => $field) {
        // Try to resolve it.
        list($tableName, $fieldName) = explode('.', $field);

        // Did we already select from this table?
        if (array_key_exists($tableName, $selectedTables)) {
            // Yes, so this field has been resolved for free.
            $changes = true;
            $resolvedFields[] = $field;
            array_push($selectedTables[$tableName], $fieldName);
            unset($unresolvedFields[$i];
            // On to the next field.
            continue;
        }
        // This is the first time we see this table.
        // Is this the VERY FIRST table (assume it's the "lead" table --
        // it's not necessary but it simplifies the code)?
        if (empty($selectedTables)) {
            // Yes. We need do no more.
            $selectedTables[$tableName] = array( $fieldName );
            $changes = true; //-//
            $resolvedFields[] = $field; //-//
            unset($unresolvedFields[$i]; //-//
            // On to the next field. //--//
            continue; //--//
        } // We could also put this check before the last. If we did, the
        // lines above marked //-// could be left out; those with //--// should.
        // And we would need $selectedTables[$tableName] = array(/*EMPTY*/); 

        // We did not see this table before, and it's not the first.
        // So we need a way to join THIS table with SOME of those already used.

        // Again we suppose there're no ambiguities here. This table HAS a
        // link to some other. So we just need ask, "WHICH other? And do we have it?"
        $links = $orm[$tableName];

        $useful = array_intersect_keys($orm[$tableName], $selectedTables);

        // $useful contains an entry 'someTable' => ( 'on' => ... )
        // for each table that we can use to reference $tableName.
        // THERE MUST BE ONLY ONE, or there will be an ambiguity.
        // Of course most of the time we will find none.
        // And go on with the next field...
        if (empty($useful)) {
            continue;
        }
        // TODO: check count($useful) is really 1.
        $changes = true;
        $selectedTables[$tableName] = array( $fieldName );
        list($joinWith, $info) = each($useful[0]);
        // We write SQL directly in here. We actually shouldn't, but it's faster
        // to do it now instead of saving the necessary info.
        // $info could actually also contain the JOIN type, additional conditions...
        $joins[] = "INNER JOIN {$joinWith} ON ( {$tableName}.{$info['on'][0]}
                      = {$joinWith}.{$info['on'][1]} )";
        unset($unresolvedFields[$i];
    }
    // If something changed, we need to repeat, because a later field could have
    // supplied some table that could have made joinable an earlier field which we
    // had given up on, before.
    if (!$changes) {
        // But if nothing has changed there's no purpose in continuing.
        // Either we resolved all the fields or we didn't.
        break;
    }
}
// Now, if there're still unresolved fields after the situation stabilized,
// we can't make this query. Not enough information. Actually we COULD, but
// it would spew off a Cartesian product of the groups of unjoined tables -
// almost surely not what we wanted. So, unresolveds cause an error.
if (!empty($unresolvedFields)) {
    throw new \Exception("SOL");
}

// Else we can build the query: the first table leads the SELECT and all the
// others are joined.

$query = "SELECT " . implode(', ', $selectedFields)
       . ' FROM ' . array_shift($selectedTables) . "\n";
// Now for each $selectedTables remaining
foreach ($selectedTables as $table) {
    $query .= $joins[$table] . "\n";

// Now we could add any WHEREs, ORDER BY, LIMIT and so on.
...

If a user picked Name, First Name, Last Name

You would also need a "translation" between the human-readable "Name" and "accounts.account_name". Once you did, however, the algorithm above would find those records:

Name        ... fields = [ accounts.account_name ], tables = [ accounts ], joins = [ ]
First Name  ... fields = [ a.ac_name, co.first ], tables = [ ac, co ], joins = [ co ]
Last Name   ... contacts is already in tables, so fields = [ 3 fields ], rest unchanged

Answer 3

Think of building the sql query as just a simple string building excercise. The values posted from the form on the previous page will, when you refer to your field_relations table, identify which columns and tables you want in the query.

The first posted value will identify a field in one of the tables, telling you you need a FROM clause. Then as soon as you meet a field from a second table, that tells you to add a INNER JOIN contacts (or sales) ON contacts.account_id = accounts.account_id clause. If you later meet a field from the third table, then you have to add another JOIN clause.

You should not need the related_to and related_to_field columns at all in field_relations, since the column name posted from the form, when referred to the field_relations table tells you which table a field comes from.