Yii2 Query Relation Manager: Run queries to model objects using filters
| Recommend this page to a friend! |
 Author |
 |
|
Innovation award Nominee: 5x | ||||||||||
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.
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.
Details
yii2-query-relation-manager
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.
| Classes of Smoren Freelight | > | Yii2 Query Relation Manager | > |  Download .zip .tar.gz |
> |  Support forum |
> |  Blog (1) |
> |   Latest changes |
|
|||||||||||||||
| Groups | Applications |  Files |
| Groups |
 Databases |
Database management, accessing and searching | View top rated classes |
| Libraries |
Frameworks and libraries of cooperating classes | View top rated classes |
| Design Patterns |
Implementations of well known design patterns | View top rated classes |
| PHP 7 |
Classes using PHP 7 specific features | View top rated classes |
| Applications that use this package |
No pages of applications that use this class were specified.
If you know an application of this package, send a message to the author to add a link here.
| Files |
| File | Role | Description | ||
|---|---|---|---|---|
| .github (1 directory) |
||||
| src (4 files) |
||||
| tests (3 files, 3 directories) |
||||
 codeception.yml |
Data | Auxiliary data | ||
| composer.json |
Data | Auxiliary data | ||
| LICENSE |
Lic. | License text | ||
| README.md |
Doc. | Read me | ||
| Files |
/ | src |
| File | Role | Description |
|---|---|---|
| ActiveRecordTrait.php |
Class | Class source |
| QueryRelationDataProvider.php |
Class | Class source |
| QueryRelationManager.php |
Class | Class source |
| QueryWrapper.php |
Class | Class source |
| Files |
/ | tests |
| File | Role | Description | ||
|---|---|---|---|---|
| scripts (1 file) |
||||
| unit (4 files, 2 directories) |
||||
| _support (1 file) |
||||
| coding_standard.xml |
Data | Auxiliary data | ||
| unit.suite.yml |
Data | Auxiliary data | ||
| _bootstrap.php |
Aux. | Auxiliary script | ||
| Files |
/ | tests | / | unit |
| File | Role | Description | ||
|---|---|---|---|---|
| Config (2 files) |
||||
| Models (4 files, 1 directory) |
||||
| CommonUsageTest.php |
Class | Class source | ||
| DataProviderTest.php |
Class | Class source | ||
| ErrorsTest.php |
Class | Class source | ||
| WithSyntaxTest.php |
Class | Class source | ||
| Files |
/ | tests | / | unit | / | Models |
| File | Role | Description | ||
|---|---|---|---|---|
| Bad (1 file) |
||||
| Address.php |
Class | Class source | ||
| City.php |
Class | Class source | ||
| Comment.php |
Class | Class source | ||
| Place.php |
Class | Class source | ||
| Files |
/ | tests | / | unit | / | Models | / | Bad |
| File | Role | Description |
|---|---|---|
| NonActiveRecordClass.php |
Class | Class source |
| Download all files: yii2-query-relation-.tar.gz yii2-query-relation-.zip NOTICE: if you are using a download manager program like 'GetRight', please Login before trying to download this archive.
|
| Files |
| File | Role | Description | ||
|---|---|---|---|---|
| .github (1 directory) |
||||
| src (4 files) |
||||
| tests (3 files, 3 directories) |
||||
| codeception.yml |
Data | Auxiliary data | ||
| composer.json |
Data | Auxiliary data | ||
| LICENSE |
Lic. | License text | ||
| README.md |
Doc. | Read me | ||
| Files |
/ | src |
| File | Role | Description |
|---|---|---|
| ActiveRecordTrait.php |
Class | Class source |
| QueryRelationDataProvider.php |
Class | Class source |
| QueryRelationManager.php |
Class | Class source |
| QueryWrapper.php |
Class | Class source |
| Files |
/ | tests |
| File | Role | Description | ||
|---|---|---|---|---|
| scripts (1 file) |
||||
| unit (4 files, 2 directories) |
||||
| _support (1 file) |
||||
| coding_standard.xml |
Data | Auxiliary data | ||
| unit.suite.yml |
Data | Auxiliary data | ||
| _bootstrap.php |
Aux. | Auxiliary script | ||
| Files |
/ | tests | / | unit |
| File | Role | Description | ||
|---|---|---|---|---|
| Config (2 files) |
||||
| Models (4 files, 1 directory) |
||||
| CommonUsageTest.php |
Class | Class source | ||
| DataProviderTest.php |
Class | Class source | ||
| ErrorsTest.php |
Class | Class source | ||
| WithSyntaxTest.php |
Class | Class source | ||
| Files |
/ | tests | / | unit | / | Models |
| File | Role | Description | ||
|---|---|---|---|---|
| Bad (1 file) |
||||
| Address.php |
Class | Class source | ||
| City.php |
Class | Class source | ||
| Comment.php |
Class | Class source | ||
| Place.php |
Class | Class source | ||
| Files |
/ | tests | / | unit | / | Models | / | Bad |
| File | Role | Description |
|---|---|---|
| NonActiveRecordClass.php |
Class | Class source |
| Download all files: yii2-query-relation-.tar.gz yii2-query-relation-.zip NOTICE: if you are using a download manager program like 'GetRight', please Login before trying to download this archive.
|
