smoren/yii2-query-relation-manager

Class for building queries and making result array with relations (single and multiple)

v3.0.0 2022-09-01 21:11 UTC

Requires

• php: >=7.4
• ext-pdo: *
• smoren/query-relation-manager: ^3.0.0
• yiisoft/yii2: ^2.0.0

Requires (Dev)

• codeception/codeception: ^4.2.1
• codeception/module-asserts: ^2.0
• php-coveralls/php-coveralls: ^2.0
• phpstan/phpstan: ^1.8
• squizlabs/php_codesniffer: 3.*

MIT

• Smoren

• query
• extension
• activerecord
• builder
• relation
• yii2

README

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.