How to make complex queries using repositories and joining different tables?

Question

PHP

Greetings!
I was taught to work with domain driven design. As part of this approach, I was asked to share data access across repositories. That is, for one database table in our company, it is proposed to use one repository. With simple queries, everything is clear.

How to use data from several tables within this approach? At the database level, this is solved simply - join and subqueries. At the repository level, it's not clear to me how to do this. I see two options. Use requests with join and subqueries to get all the necessary data as a special case of some task, make a separate method in the repository that will be able to do this. The second option is to receive data with different requests and combine them at the service level with some kind of mapping.
Mappings in both cases will have to be used because, for example, if we want to send a list of orders with goods via the API, then the order object will have an array field with a collection of goods objects of the corresponding type.
All of this is slow.
Probably I do not understand how to properly design such an application. Please help me to solve this simple problem. Thanks in advance to all who respond.

Answer 1

solution to this simple problem.

I respect that kind of optimism.
This problem is not new. It is called object-relational impedance mismatch and many people consider it unsolvable in principle , comparing it with the US lost war in Vietnam .
So you can hope for anything, but not for a simple solution. But first you need to understand the problem . That the mapping of objects to a relational base, which is called object-relational mapping, ORM for short, is never simple .
Separately, the terminology adds sharpness to this topic. Ask 10 different developers what they mean by repository and mapping, and you'll get 20 different opinions. So you should use beautiful words with great care.
For example, the "unsolvability" of the problem with impedance mismatch refers to attempts to make a universal ORM that receives the name of any class as input, and a collection of objects as output. You can really forget about this option (hello, eloquent-eloquent - and in production!). But here's a semi-automatic solution is quite possible to hack. The main thing is to always remember the problem, and as soon as automatic mappingceases to work - right there from it to appear in favor of manual kolupaniye with requests. The main thing is not to be afraid of this and not to drive yourself into a cage with the words "repository", "one object-one table", etc. You have a task - to instantiate an object or a collection of objects from the database. Okay, you write methods that do it in an optimal way, it doesn't matter - one table is used there, 10 or plus 2 caches and a database to boot.
Do you need to store an object or a collection of objects in the database? Okay, write a method that does this in an optimal way. Yes, it's a lot of hard work. But then you will have pure domain logic (which has nothing to do with the database or "repositories" at all).
Separately, I advertise Cycle ORM. I myself am not a real welder, but adult guys say that it is best suited for normally implemented object mapping on the database. Better than The Doctrine or sorry-God Elokvent. I look forward to the author's report at PHPRash.

Answer 2

Tip on the forehead. Following OOP, you describe the objects of the system as objects from the outside world. And everything is great with you until you start normalizing the database schema. Apparently this worries you?
But look at it all from the point of view of the principle of closure. What the repository does inside should not bother anyone. It must have an interface for the overlying layers, and that's it.
And for repositories, do not create them for each table, this is superfluous.
There are business models, for example Group and User
In addition to the obvious 2 tables, 3 UserInGroup is added
You obviously need only 2 repositories, 3 repositories will be superfluous and will only confuse

Answer 3

You can use views from the database side, or storage factories from the code side.
It's fashionable to just statically bake into a separate class first if that's really the bottleneck. If the pattern shows itself, then it can be issued in the form of a factory.
Look in C # in the Entity Framework, almost as you said, only there the request is constructed from collections of classes that are able to map a specific subject area. But there is already a full-fledged ORM with optimizations such as a deferred query to the database: the Linq-To-Entity technology implements relational logic, so it is fashionable to postpone a cumbersome SQL query until the very last moment.