Is there an ORM in PHP that allows you to take data with a complex structure from the database?

Question

PHP

Let me explain what I mean.
Let's say there is a table with ads. Each ad has data associated with it:
1) Tags
1.1) Tag category
2) Phone numbers
3) Images
4) Author

The structure is like this

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for advert
-- ----------------------------
DROP TABLE IF EXISTS `advert`;
CREATE TABLE `advert` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `title` varchar(255) NOT NULL,
  `author_id` int(10) unsigned NOT NULL,
  PRIMARY KEY (`id`),
  KEY `author_id` (`author_id`),
  CONSTRAINT `advert_ibfk_1` FOREIGN KEY (`author_id`) REFERENCES `author` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Table structure for author
-- ----------------------------
DROP TABLE IF EXISTS `author`;
CREATE TABLE `author` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `login` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Table structure for category
-- ----------------------------
DROP TABLE IF EXISTS `category`;
CREATE TABLE `category` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Table structure for image
-- ----------------------------
DROP TABLE IF EXISTS `image`;
CREATE TABLE `image` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `advert_id` int(10) unsigned NOT NULL,
  PRIMARY KEY (`id`),
  KEY `advert_id` (`advert_id`),
  CONSTRAINT `image_ibfk_1` FOREIGN KEY (`advert_id`) REFERENCES `advert` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Table structure for link_advert_phone
-- ----------------------------
DROP TABLE IF EXISTS `link_advert_phone`;
CREATE TABLE `link_advert_phone` (
  `phone_id` int(10) unsigned NOT NULL,
  `advert_id` int(10) unsigned NOT NULL,
  PRIMARY KEY (`advert_id`,`phone_id`),
  KEY `phone_id` (`phone_id`),
  CONSTRAINT `link_advert_phone_ibfk_1` FOREIGN KEY (`phone_id`) REFERENCES `phone` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `link_advert_phone_ibfk_2` FOREIGN KEY (`advert_id`) REFERENCES `advert` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Table structure for link_advert_tag
-- ----------------------------
DROP TABLE IF EXISTS `link_advert_tag`;
CREATE TABLE `link_advert_tag` (
  `tag_id` int(10) unsigned NOT NULL,
  `advert_id` int(10) unsigned NOT NULL,
  PRIMARY KEY (`tag_id`,`advert_id`),
  KEY `advert_id` (`advert_id`),
  CONSTRAINT `link_advert_tag_ibfk_1` FOREIGN KEY (`tag_id`) REFERENCES `tag` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `link_advert_tag_ibfk_2` FOREIGN KEY (`advert_id`) REFERENCES `advert` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Table structure for phone
-- ----------------------------
DROP TABLE IF EXISTS `phone`;
CREATE TABLE `phone` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `value` varchar(16) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Table structure for tag
-- ----------------------------
DROP TABLE IF EXISTS `tag`;
CREATE TABLE `tag` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  `category_id` int(10) unsigned NOT NULL,
  PRIMARY KEY (`id`),
  KEY `category_id` (`category_id`),
  CONSTRAINT `tag_ibfk_1` FOREIGN KEY (`category_id`) REFERENCES `category` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
SET FOREIGN_KEY_CHECKS=1;

It is necessary to pull out all data related to ads.
The base weighs about 4GB. Now the whole thing is pulling out a samopis that builds a query based on the structure of the database. It happens like this:

$result = $db->getTable('advert')
  ->select()
  ->limit(180)
  ->offset(0)
  ->cache(5)
  ->link('author')
  ->link('image')
  ->link('link_advert_phone.phone')
  ->link('link_advert_tag.tag.category')
  ->query()
;

The result is an array like this:

$result = [
  [
    'id' => 1,
    'title' => '....',
    'author_id' => 1,
    'author_id.author.id' => [
      'id' => 1,
      'login' => 'user name',
    ],
    'id.image.advert_id' => [
      [
        'id' => 1,
        'advert_id' => 1,
      ],
      [
        'id' => 2,
        'advert_id' => 1,
      ],
      // ....
    ],
    'id.link_advert_phone.advert_id' => [
      [
        'advert_id' => 1,
        'phone_id' => 1,
        'phone_id.phone.id' => [
          'id' => 1,
          'value' => '+996......',
        ]
      ],
      // ....
    ],
    'id.link_advert_tag.advert_id' => [
      [
        'advert_id' => 1,
        'tag_id' => 1,
        'tag_id.tag.id' => [
          'id' => 1,
          'name' => 'Тег',
          'category_id' => 1,
          'category_id.category.id' => [
            'id' => 1,
            'name' => 'Категоряия',
          ]
        ]
      ],
      // ....
    ],
  ],
  // ...
];

Samopis, if necessary, builds temporary tables with keys and composes complex queries that pull out such structures of arbitrary nesting. Plus, you can write conditions to nested tables that will affect the result. It is possible to write such queries manually, but it is very problematic and difficult. Now there are plans to rewrite the entire site and I would like to replace this samopis with a third-party solution. I tried doctrine, it increases the time from 200 ms to about 3 seconds, plus the complexity of writing queries increases by an order of magnitude.

Answer 1

It's not a complex structure. What's more, with an ORM, it's easier: there won't be separate entities for your link_ tables.
The two most popular options are Eloquent (based on the Active Record anti-pattern) and Doctrine (Data Mapper).

Tried doctrine, it increases the time from 200ms to about 3 seconds,

Request time? If so, then you made a mistake somewhere: the most common requests are generated there.
Most likely you didn't force joining the related data you want to get.

plus the complexity of writing queries increases by an order of magnitude.

The complexity of entry increases, it is more difficult for beginners. The complexity of daily work falls. And the complexity of support (work with long-term projects) falls by several orders of magnitude.

Answer 2

propel2

Answer 3

Try the Yii2 framework - it has an ORM. Ready-made models that work through functions, everything is simple, write in the controller what you want to see in the view through the action, and you're done! Displays information of any complexity. I still do not regret that I chose this framework. It is flexible, able to implement many different tasks. Many different data processing solutions are written under Yii2. Save development time. Such questions will disappear. There are many lessons on youtube, if you know php, MySQL, sql, then you can easily configure, install and package everything. The box contains basic solutions, you can add third-party developments for free. And not only.
And the number of tables can be reduced php.net/manual/ru/function.implode.php