То, какие маркеры могут быть параметризованы в PDO, подготовило операторы?

@ Josh Leitzel

That thinking is very restrictive (and is in my opinion just an excuse for being too lazy to implement a robust solution), especially for dynamic tree structures expressed in a database.

Consider the following example:

My project has a logical structure:

A company hierarchy is expressed in terms of entities. Each entity can treated in the general case of being a member of the hierarchy or as a member of a specific level of the hierarchy. The hierarchy itself is defined in a table as a single tree branch as follows:

entity_structure (
   id
   name
   parent_entity_structure_id
);

and the entities themselves are expressed as:

entities (
   id
   name
   entity_structure_id
   parent_id
);

For ease of use I've built an algorithm that creates a flat view of the tree. The following concrete example illustrates what I mean:

SELECT * FROM entity_structure;

id      | name               | entity_structure_parent_id
-----------------------------------------------------------
1       | Company            | null    (special one that always exists)
2       | Division           | 1
3       | Area               | 2
4       | Store              | 3

This would result in the following flat representation being produced:

entity_tree (
   entity_id
   division_id
   area_id
   store_id
)

Entities that are at the division level would have division_id, area_id and store_id as NULL, An area area_id and store_id as NULL, etc.

The nice thing about this is it lets you query all the children of a division using a statement similar to the follow:

SELECT * FROM entity_tree WHERE division_id = :division_id;

However this assumes that I know the structure level of the entity I'm querying. It would be nice to do:

SELECT * FROM entity_tree WHERE :structure = :entity_id;

I know it's not hard to figure out the structure level of a single entity, but assume I'm looping through a collection of entities that may not all be at the same level. As it is now I have to build a separate query for each level of the hierarchy, but if I could parameterize fields I could do the following:

$children = array();
$stmt = $pdo->prepare('SELECT entity_id FROM entity_tree WHERE :structure = :entityId');
foreach ($entities AS $entity) {
   $stmt->execute(array(
      ':structure' = $entity->getEntityStructureId(),
      ':entityId'  = $entity->getId()
   ));

   $children[$entity->getId()] = $stmt->fetchAll(PDO::FETCH_COLUMN);
}

resulting in cleaner code and only one prepared statement.

The entire example uses no user input whatsoever.

Just something to consider.

Вы также не можете параметризовать что-либо в предложении IN .