PHP Classes

Yii2 Query Relation Manager: Run queries to model objects using filters

Recommend this page to a friend!
     
  Info   Example   View files Files   Install with Composer Install with Composer   Download Download   Reputation   Support forum   Blog    
Ratings Unique User Downloads Download Rankings
Not yet rated by the usersTotal: 29 All time: 11,146 This week: 455Up
Version License PHP version Categories
yii2-query-relation- 1.0MIT/X Consortium ...7.2Databases, Libraries, Design Patterns, P...
Description 

Author

This package can run queries to model objects using filters.

It provides a trait that can be used with YII2 with model classes that use the ActiveRecord design pattern to add a query relation manager.

The package allows developers to compose and execute queries to model class objects.

The queries can use relations between model classes and filters to define conditions for the objects that the execution of the queries returns as results.

Innovation Award
PHP Programming Innovation award nominee
March 2023
Number 3
ActiveRecord is a popular design pattern implemented by many frameworks based on the Model-View-Controller (MVC) design pattern to store and retrieve objects stored, for instance, in SQL databases.

Applications that use the MVC design pattern usually need to perform queries to retrieve certain model class objects that match certain conditions.

This package uses a query relation manager trait to add the ability to model classes to elaborate complex queries on model objects that match conditions that may include filters on the model object properties and relations with other model class objects.

Manuel Lemos
Picture of Smoren  Freelight
  Performance   Level  
Name: Smoren Freelight <contact>
Classes: 38 packages by
Country: Russian Federation Russian Federation
Age: 35
All time rank: 280778 in Russian Federation Russian Federation
Week rank: 10 Up1 in Russian Federation Russian Federation Up
Innovation award
Innovation award
Nominee: 16x

Example

<?php

$config
= require __DIR__.'/../unit/Config/db.php';

$dbh = new PDO(
   
$config['dsn'],
   
$config['username'],
   
$config['password'],
    [
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION]
);

echo
"Connected. Creating tables...\n";

$dbh->query(
   
'CREATE TABLE `city` (
        `id` int NOT NULL AUTO_INCREMENT,
        `name` varchar(255) NOT NULL,
        PRIMARY KEY (`id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb3;'
);

$dbh->query(
   
'CREATE TABLE `address` (
        `id` int NOT NULL AUTO_INCREMENT,
        `city_id` int NOT NULL,
        `name` varchar(255) NOT NULL,
        PRIMARY KEY (`id`),
        KEY `idx-address-city_id` (`city_id`),
        CONSTRAINT `fk-address-city_id-city-id` FOREIGN KEY (`city_id`) REFERENCES `city` (`id`) ON DELETE CASCADE
    ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb3;'
);

$dbh->query(
   
'CREATE TABLE `place` (
        `id` int NOT NULL AUTO_INCREMENT,
        `address_id` int NOT NULL,
        `name` varchar(255) NOT NULL,
        PRIMARY KEY (`id`),
        KEY `idx-place-address_id` (`address_id`),
        CONSTRAINT `fk-place-address_id-address-id` FOREIGN KEY (`address_id`) REFERENCES `address` (`id`) ON DELETE CASCADE
    ) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb3;'
);

$dbh->query(
   
'CREATE TABLE `comment` (
        `id` int NOT NULL AUTO_INCREMENT,
        `place_id` int NOT NULL,
        `username` varchar(255) NOT NULL,
        `mark` tinyint NOT NULL,
        `text` text NOT NULL,
        PRIMARY KEY (`id`),
        KEY `idx-comment-place_id` (`place_id`),
        CONSTRAINT `fk-comment-place_id-place-id` FOREIGN KEY (`place_id`) REFERENCES `place` (`id`) ON DELETE CASCADE
    ) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb3;'
);

echo
"Inserting data...\n";

$dbh->query(
   
"INSERT INTO city (id,name) VALUES
         (1,'Moscow'),
         (2,'St. Petersburg'),
         (3,'Samara'),
         (4,'Barnaul'),
         (5,'Ivanovo');"
);

$dbh->query(
   
"INSERT INTO address (id,city_id,name) VALUES
         (1,1,'Tverskaya st., 7'),
         (2,1,'Schipok st., 1'),
         (3,2,'Mayakovskogo st., 12'),
         (4,2,'Galernaya st., 3');"
);

$dbh->query(
   
"INSERT INTO place (id,address_id,name) VALUES
         (1,1,'TC Tverskoy'),
         (2,1,'Tverskaya cafe'),
         (3,2,'Stasova music school'),
         (4,3,'Hostel on Mayakovskaya'),
         (5,3,'Mayakovskiy Store'),
         (6,4,'Cafe on Galernaya');"
);

$dbh->query(
   
"INSERT INTO comment (id,place_id,username,mark,`text`) VALUES
         (1,1,'Ivan Mustafaevich',3,'Not bad, not good'),
         (2,1,'Peter',5,'Good place'),
         (3,1,'Mark',1,'Bad place'),
         (4,3,'Ann',5,'The best music school!'),
         (5,5,'Stas',4,'Rather good place'),
         (6,6,'Stas',3,'Small menu, long wait');"
);

echo
"Migration complete!\n";


Details

yii2-query-relation-manager

Packagist PHP Version Support Scrutinizer Code Quality Coverage Status Build and test License: MIT

Implements the functionality of getting tree data from a database with one-to-one and one-to-many relationships using only one select-query to the database with flexible conditions configuration.

How to install to your project

composer require smoren/yii2-query-relation-manager

Usage examples

Let's say we have these tables in DB with such columns:

- city (id, name) - address (id, city_id, name) - place (id, address_id, name) - comment (id, place_id, username, mark, text)

and their corresponding ActiveRecord model classes: - app\models\\City - app\models\\Address - app\models\\Place - app\models\\Comment

<?php

use Smoren\QueryRelationManager\Yii2\QueryRelationManager;
use Smoren\QueryRelationManager\Yii2\QueryRelationDataProvider;
use app\models\City;
use app\models\Address;
use app\models\Place;
use app\models\Comment;

// Let's select addresses with theirs relations: city, places and comments about places
$result = QueryRelationManager::select(Address::class, 'a')
    ->withSingle('city', City::class, 'c', 'a', ['id' => 'city_id'])
    ->withMultiple('places', Place::class, 'p', 'a', ['address_id' => 'id'])
    ->withMultiple('comments', Comment::class, 'cm', 'p', ['place_id' => 'id'])
    ->all();

print_r($result);
/*Array
(
    [0] => Array
        (
            [id] => 1
            [city_id] => 1
            [name] => Tverskaya st., 7
            [city] => Array
                (
                    [id] => 1
                    [name] => Moscow
                )

            [places] => Array
                (
                    [0] => Array
                        (
                            [id] => 1
                            [address_id] => 1
                            [name] => TC Tverskoy
                            [comments] => Array
                                (
                                    [0] => Array
                                        (
                                            [id] => 1
                                            [place_id] => 1
                                            [username] => Ivan Mustafaevich
                                            [mark] => 3
                                            [text] => Not bad, not good
                                        )

                                    [1] => Array
                                        (
                                            [id] => 2
                                            [place_id] => 1
                                            [username] => Peter
                                            [mark] => 5
                                            [text] => Good place
                                        )

                                    [2] => Array
                                        (
                                            [id] => 3
                                            [place_id] => 1
                                            [username] => Mark
                                            [mark] => 1
                                            [text] => Bad place
                                        )

                                )

                        )

                    [1] => Array
                        (
                            [id] => 2
                            [address_id] => 1
                            [name] => Tverskaya cafe
                            [comments] => Array
                                (
                                )

                        )

                )

        )

    [1] => Array
        (
            [id] => 2
            [city_id] => 1
            [name] => Schipok st., 1
            [city] => Array
                (
                    [id] => 1
                    [name] => Moscow
                )

            [places] => Array
                (
                    [0] => Array
                        (
                            [id] => 3
                            [address_id] => 2
                            [name] => Stasova music school
                            [comments] => Array
                                (
                                    [0] => Array
                                        (
                                            [id] => 4
                                            [place_id] => 3
                                            [username] => Ann
                                            [mark] => 5
                                            [text] => The best music school!
                                        )

                                )

                        )

                )

        )

    [2] => Array
        (
            [id] => 3
            [city_id] => 2
            [name] => Mayakovskogo st., 12
            [city] => Array
                (
                    [id] => 2
                    [name] => St. Petersburg
                )

            [places] => Array
                (
                    [0] => Array
                        (
                            [id] => 4
                            [address_id] => 3
                            [name] => Hostel on Mayakovskaya
                            [comments] => Array
                                (
                                )

                        )

                    [1] => Array
                        (
                            [id] => 5
                            [address_id] => 3
                            [name] => Mayakovskiy Store
                            [comments] => Array
                                (
                                    [0] => Array
                                        (
                                            [id] => 5
                                            [place_id] => 5
                                            [username] => Stas
                                            [mark] => 4
                                            [text] => Rather good place
                                        )

                                )

                        )

                )

        )

    [3] => Array
        (
            [id] => 4
            [city_id] => 2
            [name] => Galernaya st., 3
            [city] => Array
                (
                    [id] => 2
                    [name] => St. Petersburg
                )

            [places] => Array
                (
                    [0] => Array
                        (
                            [id] => 6
                            [address_id] => 4
                            [name] => Cafe on Galernaya
                            [comments] => Array
                                (
                                    [0] => Array
                                        (
                                            [id] => 6
                                            [place_id] => 6
                                            [username] => Stas
                                            [mark] => 3
                                            [text] => Small menu, long wait
                                        )

                                )

                        )

                )

        )

)*/


// Now let's select places with it's relations: address, city and comments, and with next conditions
// - comments are rated at least 3
// - if there are no suitable comments, the place is not included in the selection (inner join)
// - for each place we count the number of comments, the number of ratings "5" and the average rating among the ratings is not lower than 3
$result = QueryRelationManager::select(Place::class, 'p')
    ->withSingle('address', Address::class, 'a', 'p', ['id' => 'address_id'])
    ->withSingle('city', City::class, 'c', 'a', ['id' => 'city_id'])
    ->withMultiple('comments', Comment::class, 'cm', 'p', ['place_id' => 'id'],
        'inner', 'and cm.mark >= :mark', [':mark' => 3])
    ->modify('p', function(array &$place) {
        $place['comments_count'] = count($place['comments']);
        $place['mark_five_count'] = 0;
        $place['mark_average'] = 0;
    
        foreach($place['comments'] as $comment) {
            $place['mark_average'] += $comment['mark'];
            if($comment['mark'] == 5) {
                $place['mark_five_count']++;
            }
        }
    
        $place['mark_average'] /= $place['comments_count'];
    })
    ->all();

print_r($result);
/*Array
(
    [0] => Array
        (
            [id] => 1
            [address_id] => 1
            [name] => TC Tverskoy
            [address] => Array
                (
                    [id] => 1
                    [city_id] => 1
                    [name] => Tverskaya st., 7
                    [city] => Array
                        (
                            [id] => 1
                            [name] => Moscow
                        )

                )

            [comments] => Array
                (
                    [0] => Array
                        (
                            [id] => 1
                            [place_id] => 1
                            [username] => Ivan Mustafaevich
                            [mark] => 3
                            [text] => Not bad, not good
                        )

                    [1] => Array
                        (
                            [id] => 2
                            [place_id] => 1
                            [username] => Peter
                            [mark] => 5
                            [text] => Good place
                        )

                )

            [comments_count] => 2
            [mark_five_count] => 1
            [mark_average] => 4
        )

    [1] => Array
        (
            [id] => 3
            [address_id] => 2
            [name] => Stasova music school
            [address] => Array
                (
                    [id] => 2
                    [city_id] => 1
                    [name] => Schipok st., 1
                    [city] => Array
                        (
                            [id] => 1
                            [name] => Moscow
                        )

                )

            [comments] => Array
                (
                    [0] => Array
                        (
                            [id] => 4
                            [place_id] => 3
                            [username] => Ann
                            [mark] => 5
                            [text] => The best music school!
                        )

                )

            [comments_count] => 1
            [mark_five_count] => 1
            [mark_average] => 5
        )

    [2] => Array
        (
            [id] => 5
            [address_id] => 3
            [name] => Mayakovskiy Store
            [address] => Array
                (
                    [id] => 3
                    [city_id] => 2
                    [name] => Mayakovskogo st., 12
                    [city] => Array
                        (
                            [id] => 2
                            [name] => St. Petersburg
                        )

                )

            [comments] => Array
                (
                    [0] => Array
                        (
                            [id] => 5
                            [place_id] => 5
                            [username] => Stas
                            [mark] => 4
                            [text] => Rather good place
                        )

                )

            [comments_count] => 1
            [mark_five_count] => 0
            [mark_average] => 4
        )

    [3] => Array
        (
            [id] => 6
            [address_id] => 4
            [name] => Cafe on Galernaya
            [address] => Array
                (
                    [id] => 4
                    [city_id] => 2
                    [name] => Galernaya st., 3
                    [city] => Array
                        (
                            [id] => 2
                            [name] => St. Petersburg
                        )

                )

            [comments] => Array
                (
                    [0] => Array
                        (
                            [id] => 6
                            [place_id] => 6
                            [username] => Stas
                            [mark] => 3
                            [text] => Small menu, long wait
                        )

                )

            [comments_count] => 1
            [mark_five_count] => 0
            [mark_average] => 3
        )

)*/


// Let's select cities with their addresses by the list of city ids
$cityIds = City::find()->limit(2)->offset(1)->select('id')->column();
$result = QueryRelationManager::select(City::class, 'c')
    ->withMultiple('addresses', Address::class, 'a', 'c', ['city_id' => 'id'])
    ->filter(function(Query $q) use ($cityIds) {
        $q->andWhere(['c.id' => $cityIds])->orderBy(['a.id' => SORT_ASC]);
    })
    ->all();

print_r($result);
/*Array
(
    [0] => Array
        (
            [id] => 3
            [name] => Samara
            [addresses] => Array
                (
                )

        )

    [1] => Array
        (
            [id] => 2
            [name] => St. Petersburg
            [addresses] => Array
                (
                    [0] => Array
                        (
                            [id] => 3
                            [city_id] => 2
                            [name] => Mayakovskogo st., 12
                        )

                    [1] => Array
                        (
                            [id] => 4
                            [city_id] => 2
                            [name] => Galernaya st., 3
                        )

                )

        )

)*/


// Let's use QueryRelationDataProvider for pagination
$qrm = QueryRelationManager::select(City::class, 'c')
    ->withMultiple('addresses', Address::class, 'a', 'c', ['city_id' => 'id']);

$dataProvider = new QueryRelationDataProvider([
    'queryRelationManager' => $qrm,
    'pagination' => [
        'pageSize' => 2,
        'page' => 0,
    ],
]);

print_r($dataProvider->getModels());
/*Array
(
    [0] => Array
        (
            [id] => 1
            [name] => Moscow
            [addresses] => Array
                (
                    [0] => Array
                        (
                            [id] => 2
                            [city_id] => 1
                            [name] => Schipok st., 1
                        )

                    [1] => Array
                        (
                            [id] => 1
                            [city_id] => 1
                            [name] => Tverskaya st., 7
                        )

                )

        )

    [1] => Array
        (
            [id] => 2
            [name] => St. Petersburg
            [addresses] => Array
                (
                    [0] => Array
                        (
                            [id] => 4
                            [city_id] => 2
                            [name] => Galernaya st., 3
                        )

                    [1] => Array
                        (
                            [id] => 3
                            [city_id] => 2
                            [name] => Mayakovskogo st., 12
                        )

                )

        )

)*/


// Let's use a simplified syntax for building queries
// We select addresses with their relations: city, places and their comments which rated at least 3
// City:select() method added to City model by using ActiveRecordTrait
$result = Address::select('a')
    ->with('city', 'c')
    ->with('places', 'p')
    ->with(
        'comments', 'cm', 'p',
        'left', 'and cm.mark >= :mark', [':mark' => 3]
    )
    ->all();

print_r($result);
/*Array
(
    [0] => Array
        (
            [id] => 1
            [city_id] => 1
            [name] => Tverskaya st., 7
            [city] => Array
                (
                    [id] => 1
                    [name] => Moscow
                )

            [places] => Array
                (
                    [0] => Array
                        (
                            [id] => 1
                            [address_id] => 1
                            [name] => TC Tverskoy
                            [comments] => Array
                                (
                                    [0] => Array
                                        (
                                            [id] => 1
                                            [place_id] => 1
                                            [username] => Ivan Mustafaevich
                                            [mark] => 3
                                            [text] => Not bad, not good
                                        )

                                    [1] => Array
                                        (
                                            [id] => 2
                                            [place_id] => 1
                                            [username] => Peter
                                            [mark] => 5
                                            [text] => Good place
                                        )

                                )

                        )

                    [1] => Array
                        (
                            [id] => 2
                            [address_id] => 1
                            [name] => Tverskaya cafe
                            [comments] => Array
                                (
                                )

                        )

                )

        )

    [1] => Array
        (
            [id] => 2
            [city_id] => 1
            [name] => Schipok st., 1
            [city] => Array
                (
                    [id] => 1
                    [name] => Moscow
                )

            [places] => Array
                (
                    [0] => Array
                        (
                            [id] => 3
                            [address_id] => 2
                            [name] => Stasova music school
                            [comments] => Array
                                (
                                    [0] => Array
                                        (
                                            [id] => 4
                                            [place_id] => 3
                                            [username] => Ann
                                            [mark] => 5
                                            [text] => The best music school!
                                        )

                                )

                        )

                )

        )

    [2] => Array
        (
            [id] => 3
            [city_id] => 2
            [name] => Mayakovskogo st., 12
            [city] => Array
                (
                    [id] => 2
                    [name] => St. Petersburg
                )

            [places] => Array
                (
                    [0] => Array
                        (
                            [id] => 4
                            [address_id] => 3
                            [name] => Hostel on Mayakovskaya
                            [comments] => Array
                                (
                                )

                        )

                    [1] => Array
                        (
                            [id] => 5
                            [address_id] => 3
                            [name] => Mayakovskiy Store
                            [comments] => Array
                                (
                                    [0] => Array
                                        (
                                            [id] => 5
                                            [place_id] => 5
                                            [username] => Stas
                                            [mark] => 4
                                            [text] => Rather good place
                                        )

                                )

                        )

                )

        )

    [3] => Array
        (
            [id] => 4
            [city_id] => 2
            [name] => Galernaya st., 3
            [city] => Array
                (
                    [id] => 2
                    [name] => St. Petersburg
                )

            [places] => Array
                (
                    [0] => Array
                        (
                            [id] => 6
                            [address_id] => 4
                            [name] => Cafe on Galernaya
                            [comments] => Array
                                (
                                    [0] => Array
                                        (
                                            [id] => 6
                                            [place_id] => 6
                                            [username] => Stas
                                            [mark] => 3
                                            [text] => Small menu, long wait
                                        )

                                )

                        )

                )

        )

)*/

For demo see this repo.


  Files folder image Files (25)  
File Role Description
Files folder image.github (1 directory)
Files folder imagesrc (4 files)
Files folder imagetests (3 files, 3 directories)
Accessible without login Plain text file codeception.yml Data Auxiliary data
Accessible without login Plain text file composer.json Data Auxiliary data
Accessible without login Plain text file LICENSE Lic. License text
Accessible without login Plain text file README.md Doc. Read me

  Files folder image Files (25)  /  .github  
File Role Description
Files folder imageworkflows (1 file)

  Files folder image Files (25)  /  .github  /  workflows  
File Role Description
  Accessible without login Plain text file test_master.yml Data Auxiliary data

  Files folder image Files (25)  /  src  
File Role Description
  Plain text file ActiveRecordTrait.php Class Class source
  Plain text file QueryRelationDataProvider.php Class Class source
  Plain text file QueryRelationManager.php Class Class source
  Plain text file QueryWrapper.php Class Class source

  Files folder image Files (25)  /  tests  
File Role Description
Files folder imagescripts (1 file)
Files folder imageunit (4 files, 2 directories)
Files folder image_support (1 file)
  Accessible without login Plain text file coding_standard.xml Data Auxiliary data
  Accessible without login Plain text file unit.suite.yml Data Auxiliary data
  Accessible without login Plain text file _bootstrap.php Aux. Auxiliary script

  Files folder image Files (25)  /  tests  /  scripts  
File Role Description
  Accessible without login Plain text file migrate.php Example Example script

  Files folder image Files (25)  /  tests  /  unit  
File Role Description
Files folder imageConfig (2 files)
Files folder imageModels (4 files, 1 directory)
  Plain text file CommonUsageTest.php Class Class source
  Plain text file DataProviderTest.php Class Class source
  Plain text file ErrorsTest.php Class Class source
  Plain text file WithSyntaxTest.php Class Class source

  Files folder image Files (25)  /  tests  /  unit  /  Config  
File Role Description
  Accessible without login Plain text file db.php Aux. Auxiliary script
  Accessible without login Plain text file test.php Aux. Auxiliary script

  Files folder image Files (25)  /  tests  /  unit  /  Models  
File Role Description
Files folder imageBad (1 file)
  Plain text file Address.php Class Class source
  Plain text file City.php Class Class source
  Plain text file Comment.php Class Class source
  Plain text file Place.php Class Class source

  Files folder image Files (25)  /  tests  /  unit  /  Models  /  Bad  
File Role Description
  Plain text file NonActiveRecordClass.php Class Class source

  Files folder image Files (25)  /  tests  /  _support  
File Role Description
  Plain text file UnitTester.php Class Class source

The PHP Classes site has supported package installation using the Composer tool since 2013, as you may verify by reading this instructions page.
Install with Composer Install with Composer
 Version Control Unique User Downloads Download Rankings  
 100%
Total:29
This week:0
All time:11,146
This week:455Up