How to organize and implement Separation of Concern and good OOP with a growing list of queries throughout my app?

Question

Where to put a growing list of queries (mostly all "SELECT" queries) and how to properly access them throughout my app?
I'm looking to re-factor my app which currently has queries sprinkled all throughout services in my app. I'm starting to encounter DRY issues where I have small and big queries that are similar to other queries I've already written. I also have places where I'm re-writing the same formatting on those queries over and over again. Although I'm not worried about replacing my choice of database, I do worry about later optimizing my queries and wonder if I've made that near impossible by not organizing all my queries better and keeping them in a central place somewhere. I want to follow good OOP and SoC practices.

An example of where I currently have a query in my app
For example, I have a service that provides a PDF (or the content rather) of all the staff at a certain event. The service has a method that accepts some input (like "sort", "filter", "pagination limit") and will perform a query with 10 table joins (the query slices fairly well down to a manageable number of rows before most of the JOINs work on it) and format the results in the desired manner for the PDF.

What I have planned so far
I'm having trouble trying to think perfectly about my database needs in terms of objects. I do a lot of flexible querying, not all completely ad-hoc, but a wide-range of SELECTs with many JOINs and WHEREs, but I'm trying my best:

1. Domain Objects: I will create classes like usersEntity, eventsEntity (each match tables in my database) and setup properties in them like id,name,phone,etc. that match the database table fields. I may create getters/setters so I can mutate (format things like dates, etc or perform validation). I might create entities that don't really represent a single table in my database, but perhaps represent a common collection of data that I find I re-use a lot throughout my app. Maybe call it usersCollection or usersEvents.

2. Data Mapper: So I put all the CUD (Create Update Delete...not Read...except maybe a findById) operations and stored them in classes called usersMapper, eventsMapper, and so on. The mappers accept only 1 type of domain object (see #1 above) and will handle the persistence (and maybe a small set of very basic Reads/SELECTs, like findById($id) or findByEvent($eventId)).

Now this leaves me with a growing list of Reads (SELECT queries) and where to put them?

The options that I'm aware of, but fall short. Maybe I'm misunderstanding them.

Let's say I have a query like this

SELECT users.first_name, users.last_name, events.name, events.date, venues.name,
venue_types.name, GROUP_CONCAT(user_friends.last_name) as 'friends'
FROM users 
JOIN events ON(events.id = users.event_id AND events.date = :date)
JOIN etc....

So what I see in blogs throughout the PHP community is 3 choices:

1. Replace my query calls with something like Doctrine2 calls. Their DQL doesn't seem to make the call reusable? And things seems to be connected in a way that it would return like 5 entities (based on JOINs) with all properties filled for users, events, venues, venue types, friends. That's a lot of fields I don't need, I just wanted 1 column from each as you can see in my query above. Also, I don't plan on saving any entities after a query like this.

2. Replace all my query calls with Repository classes that have methods like: $someUserRepo-> findNameAndEventNameAndEventDateAndVenueNameAndVenueTypeNameAndFriends(). Besides the really crazy method name, I'm not sure what I pass here (conditions like: sort, limit, a few where's?) or what I return (arrays? objects?).

3. Replace all my query calls with ORM calls (like Laravel's Eloquent) using their query builders. I don't see how I gained anything by replacing my SQL query with please->ORM->build->this->for->me->where->andWhere->andWhere->I->dont->know->SQL. By the way, I'm almost convinced that ORM's are an anti-pattern?

I suspect this is a just a very complicated problem throughout all of the PHP community. Or what am I missing here?

Answer 1

Using a repository is the only sane option you considered.

Here's what you could do.

Define a repository interface

This interface will describe how you query certain entities.

interface Events
{
    /**
     * @return Event[]
     */
    public function findUserEvents(User $user, $sort, $limit, array $filters = []);
}

or perhaps:

interface Events
{
    /**
     * @return Event[]
     */
    public function findUserEvents(User $user, Query $query);
}

class Query
{
    private $sort;
    private $limit;
    private $filters = [];

    // methods
}

Implement the interface

Use whatever you like to implement the interface - Doctrine, Eloquent, plain SQL - whatever you feel comfortable with.

use Doctrine\ORM\EntityRepository;

class DoctrineEvents implements Events
{
    private $entityRepository;

    public function __construct(EntityRepository $entityRepository)
    {
        $this->entityRepository = $entityRepository;
    }

    /**
     * @return Event[]
     */
    public function findUserEvents(User $user, $sort, $limit, array $filters = [])
    {
        // query $this->entityRepostory and return a collection of Event objects
    }
}

Alternatively, you can inject the EntityManager instead of the EntityRepository.

Only to show you how easy it is to provide alternative implementations, here's an SQL implementation:

class SqlEvents implements Events
{
    private $pdo;

    public function __construct(\PDO $pdo)
    {
        $this->pdo = $pdo;
    }

    /**
     * @return Event[]
     */
    public function findUserEvents(User $user, $sort, $limit, array $filters = [])
    {
        // use $this->pdo to query the database
        // you'll need to convert results into a collection of Event objects 
    }
}

Easy. For tests you could provide an in-memory implementation.

Rely on the interface

Whenever you need your shiny new repository, always use the interface type hint. This will allow you to replace the implementation whenever you feel the one that you chose on the beginning no longer does the job.

class MyController
{
    private $events;
    private $view;

    public function __construct(Events $events, View $view)
    {
        $this->events = $events;
    }

    public function listAction(Request $request)
    {
        $user = // ...

        $events = $this->events->findUserEvents($user, 'ASC', 10);

        return $this->view->render('list.html', ['events' => $events]);
    }
}

In this example, MyController can work with any implementation of Events interface. It doesn't care if it's Doctrine, eloquent or sql.

Choose the implementation

My private preference would be to use Doctrine. It will take care of lots of things like hydrating objects or caching. Shall you ever need to write an SQL query manually for a case when you need a better performance you can always do that for this particular query. Not everything needs to be implemented in a single way, and since you decoupled from the implementation it is easy to change.