What Layer Does a Query Builder Belong In?

design-patternsobject-oriented-designPHP

In my project I have specified 5 sublayers for my DBAL:

  1. Database Layer ( the database itself)
  2. Database Connection Layer ( \PDO and a class that handles the database connections)
  3. Database Request Layer( classes that actually query the database )
  4. Database Model Layer ( All classes that represent 1 Database table each and a Factory class that gives easy access to all Model class)
  5. Model Layer ( classes that combines several Database Model class t make them work together )

Now I have set the restriction that each layer SHOULD only have access to classes of its own layer or classes directly below (Layer 4 MAY access Layer 4 and 3 but MUST NOT access Layer 5 and SHOULD NOT access layers 1 and 2).
But when it comes to a Query Builder ( I've decided to use https://github.com/nilportugues/php-sql-query-builder as it does not require a database connection) I wonder what layer this should be assigned to and where I should use it.

Clearly the resulting query should be passed to Layer 3, so the Query Builder should be either 3 or 4, but then in most cases I seem to more likely need to build a query in layer 5 when I want to create some Layer 4 objects.

Currently my code that uses the Query Builder looks like this (in Layer 5):

$builder = $this->entityFactory->getDbRequest()->getQueryBuilder();
// build the $query
$entity = $this->entityFactory->makeEntityByQuery( Entityname::class, $query);

So currently I located the QueryBuilder in Layer 3 but that forces me access Layer 3 from Layer 5 which I'd like to avoid. I could move it to layer 4 but then I'd either have to make it a dependency for most Classes of the Model Layer or make it accessible directly from that EntityFactory. But then again the EntityFactory would have 2 responsibilities, which violates SRP.

In which layer do you or would you use the Query Builder?

Best Answer

It feels like Layer 3, the Database Request layer needs this. First, to be honest, the layering could use fixing. If you have "layers" and the question "which layer does this go in?" is not easily answered then you either don't have enough layers, or don't have the right layers.

What you have:

  1. Database layer

  2. Database connection layer

  3. Database Request Layer

  4. (this is your problem) Database Model Layer

  5. (and so is this) Model Layer

Layer 3, the Database Request Layer is where your Query Builder belongs. Nothing outside of that layer should be doing ad hoc queries. Really, Layer 3 is more commonly referred to as the Data Access Layer (DAL) or Repository Layer. This is the layer that knows about the database. It should even know about the factories required to generated your entity objects.

Think of the DAL or Repository as a coordinating layer between two sub layers: The Database Request and Entity Factory layers.

What you really need:

         +----------+
         | Database |
         +----------+
              /\
              ||
              ||
              \/
+----------------------------+    +---------------+    +----------------+    +--------+
| Database Request (Gateway) |    | Query Builder |    | Entity Factory |    | Entity |
+----------------------------+    +---------------+    +----------------+    +--------+
              /\                      /\                       /\                /\
              ||                      ||                       ||                ||
              ||                      ||                       ||                ||
              ||                      ||      ++===============++                ||
              ||                      ||      ||                                 ||
              ||                      ||      ||                                 ||
              ||                      \/      \/                                 ||
              ||                    +------------+                               ||
              ++==================> | Repository | <=============================++
                                    +------------+
                                          /\
                                          ||
                                          ||
                                          \/
                                 +------------------+
                                 | Your Application |
                                 +------------------+

Your entity classes should have absolutely no knowledge about persistence. It's the Repository/DAL that coordinates between multiple layers underneath - including your query builder.

Nothing outside the Repository/DAL should have anything to do with the query builder. This is a utility for making the construction of SQL queries easier. Instead, you can provide a Data Transfer Object (DTO) that will contain all the possible criteria values if you need to perform a search. The Repository/DAL should have a method that accepts this criteria DTO as an argument, and then uses the DTO to build a query using the Query Builder.

From that, something else has to translate the query builder object into a request to the database. You can create another class called a Query Executor which translates the Query Builder and creates a DB request, or just pass the query builder directly to the DB request object. Yeah you "break the separation of layers" but how much coupling have you really introduced? It's up to you.

Once your application reaches the complexity where you have a question like this, you need to invest your time in learning and configuring a good Object Relational Mapper (ORM).


So I Inject those repository classes to my Application classes?

Yes, but generally only your controllers and service classes will need access to repository objects.

Are those repository classes manually created in the controller (or the DI-Container respectively)?

A little be "Yes" and a little bit "No".

If you use Dependency Injection then the DI container or a factory object that churns out controllers will instantiate the repository.

If you introduce interfaces for your repository, you can have both:

interface IFooRepository
{
    public function find($id);
}

class FooRepository implements IFooRepository
{

}

class FoosController
{
    public function __construct(IFooRepository $repository = null) {
        if (!isset($repository)) {
            $repository = new FooRepository();
        }

        $this->repository = $repository;
    }
}

The constructor for the controller accepts an IFooRepository as an argument. If null it defaults to the FooRepository concrete implementation. Now, you can actually unit test your controllers by providing a mock or stub of the IFooRepository interface.

Related Topic