Создание запроса на выборку из базы yii2
  • 4292

Формирование запросов для выборки из БД в yii2

Автор: admin | 07 мая (Пн.) 2018г. в 16ч.18м.

Сборник методов для формирования запросов к БД в yii2.

Здесь я привел список с функциями, которые будут наиболее полезными для выборки (и не только) данные из базы данных в yii2.
Рассмотрены примеры построения запросов для выборки из базы с использованием ActiveRecord и  yii\db\ActiveQuery
Каждый метод (функция) представлена в виде ссылки на якорь на этой же странице с более подробным разъяснением и практическим примером.

Вот всё, что мы имеем в арсенале, ну или почти всё:
findOne()
findAll()
find()
findBySql()

select()
addSelect()
selectOption()
where()
andWhere()
orWhere()

createCommand()
addParams()
prepare()

join()
innerJoin()
leftJoin()
rightJoin()
union() 

joinWith()
innerJoinWith()
with()
count()
sum()
average()
min()
max()
scalar()

distinct()


onCondition()
orOnCondition()
andOnCondition()
andFilterCompare()
filterHaving()
filterWhere()
column()
exists()
each()
orderBy()
addOrderBy()
addGroupBy()
groupBy()
having()
andHaving()
indexBy()
limit()
offset()

Примеры использования методов. 

findOne()
Возвращает один экземпляр активной активной записи с помощью первичного ключа или массива значений
столбцов.Этот метод автоматически вызывает метод one () и возвращет экземпляр ActiveRecord.

Найти записи, где id равен 10 или null - если ничего нет.
$model = BlogArticles::findOne(10);
//или
$model = BlogArticles::findOne(['id' => 10]);
//Аналогичный запрос
$model = BlogArticles::find()->where(['id' => 10])->one();​

Найти записи, где id равен 10, 11 или 12. Вернет одну первую найденную запись или null - если ничего нет.
$model = BlogArticles::findOne([10, 11, 12]);
//Аналогичный запрос
$model = BlogArticles::::find()->where(['id' => [10, 11, 12]])->one();​

Найти записи, где idCategory равен 3 и status = 1. Вернет одну первую найденную запись или null - если ничего нет.
$model = BlogArticles::findOne(['idCategory' => 3, 'status' => 1]);
//Аналогичный запрос
$model = BlogArticles::::find()->where(['idCategory' => 3, 'status' => 1])->one();​

findAll()

Возвращает список ActiveRecord записей, которые соответствуют указанным значениям первичного ключа
или набора значений столбца.Этот метод будет автоматически вызывать метод all () и возвращать массив
экземпляров ActiveRecord.
Поскольку это только короткий метод, использование более сложных условий, таких как ['! =', 'Id', 1] ,
не будет работать. Если вам нужно указать более сложные условия, используйте find() в сочетании с
 where() .

Найти все записи, где id равен 10 или null - если ничего нет.
$model = BlogArticles::findAll(10);
//или то же самое
$model = BlogArticles::findAll(['id' => 10]);
//Аналогичный запрос
$model = BlogArticles::find()->where(['id' => 10])->all();​

Найти записи, где id равен 10, 11 или 12. Вернет все найденные или null - если ничего нет.
$model = BlogArticles::findAll([10, 11, 12]);
//Аналогичный запрос
$model = BlogArticles::find()->where(['id' => [10, 11, 12]])->all();​

Найти записи, где idCategory равен 3 и status = 1. Вернет одну первую найденную запись или null - если ничего нет.
$model = BlogArticles::findAll(['idCategory' => 3, 'status' => 1]);
//Аналогичный запрос
$model = BlogArticles::find()->where(['idCategory' => 3, 'status' => 1])->all();​

find()

Рассмотрим работу с find() , который возвращает объект yii\db\ActiveQuery.
Выборка одной записи. Как результат - объект ActiveRecord
$res = BlogArticles::find()->where(['title' => 'My page'])->one();
$title = $res->title;//получение значения title​

Выборка всех по условию. Как результат - массив объектов ActiveRecord
$res = BlogArticles::find()->where(['status' => 1])->all();
foreach($res as $r){
echo $r->title;//вывести значения title у всех найденных
}​

findBySql() - сюда помещается sql запрос в привычной форме "SELECT * FROM `someTable`"
Обратите внимание, что поскольку оператор SQL уже указан, вызовы дополнительных методов модификации запроса (например, where () , order () ) в созданном экземпляре yii \ db \ ActiveQuery не будут иметь никакого эффекта.
Однако вызов with() , asArray() или indexBy() по-прежнему можно использовать.

count(): возвращает количество записей
В методе по умолчанию count( $q = '*', $db = null )
 COUNT(*) оптимизирован для возврата очень быстро, если мы извлекаем данные из одной таблицы,
и нет условий WHERE .
$resCount = BlogArticles::find()->where(['status' => 1])->count();//получить кол-во записей по условию

//То же, но быстрее на порядок, чем пример выше. 
//SELECT COUNT(`id`) FROM `blogArticles`
$resCount = BlogArticles::find()->where(['status' => 1])->count('[[id]]');


$resCount = BlogArticles::find()->where(['status' => 1])->count('[[id]]');​

Посчитать кол-во авторов статей
//SELECT COUNT(DISTINCT `createdBy`) FROM `blogArticles`
$authorsCount = BlogArticles::find()->count('DISTINCT [[createdBy]]');

//Или аналогично делаем так - 
$expression = new \yii\db\Expression('DISTINCT [[createdBy]]');
$model = BlogArticles::find()->count($expression);​

sum()
: возвращает сумму по указанному столбцу.
//Сумма просмотров всех страниц блога
$model = BlogArticles::find()->sum('[[viewCount]]');​

average(): возвращает среднее значение по указанному столбцу.
//Среднее значение просмотров всех страниц блога
$model = BlogArticles::find()->average('[[viewCount]]');​

min(): возвращает минимальное значение по указанному столбцу.
//Минимальное кол-во просмотров у страниц блога
$model = BlogArticles::find()->min('[[viewCount]]');​

max(): возвращает максимальное значение по указанному столбцу.
//Максимальное кол-во просмотров у страниц блога
$model = BlogArticles::find()->max('[[viewCount]]');​

scalar(): возвращает значение первого столбца в первой строке результата запроса.
Если искать все статьи, то результатом будет у первого найденного первый по порядку
расположения элемент в таблице. Если есть id, text, data, то результатом будет id первой найденной записи.
//Выбрали id страницы по ее алиасу
$id = BlogArticles::find()->where(['alias' => 'my-alias'])->scalar();​

column(): возвращает значение первого столбца в результате запроса.
Если есть id, text, data, то результатом будет массив id всех найденных записей
//Выбрали первые строки у всех найденных. В данном случае массив всех id
$first = BlogArticles::find()->column();​


exists(): возвращает (true или false) значение, указывающее, имеет ли результат запроса данные или нет.
//true или false
$hasArticle = BlogArticles::find()->where(['alias' => 'my-alias'])->exists();​

Поскольку ActiveQuery наследует yii\db\Query, можно использовать методы запросов,
такие как where() , orderBy() и другие из реализации yii\db\Query для настройки параметров запроса.
Также доступны методы из yii\db\QueryTrait и yii\db\ActiveQueryTrait

where(): Формирует условие where. Принимает как строку, так и массив
//с массивом
$res = BlogArticles::find()->where(['alias' => 'my-alias'])->one();

//со строкой
$res = BlogArticles::find()->where('alias=:alias', [':alias' => 'my-alias'])->one();

//Так же в формате опператоров. Подробнее про формат операторов в отдельном обзоре
//в формате операторов
$res = BlogArticles::find()->where(['like', 'alias', 'my-alias'])->all();​

andWhere() - Добавляет дополнительное условие WHERE к существующему.
//добавлен 'AND flagActive = 1' в условие
$res = BlogArticles::find()->where(['>=', 'viewCount', 1])->andWhere(['flagActive' => 1])->all();​


orWhere() - Добавляет дополнительное условие WHERE к существующему.
//добавлен 'OR flagActive = 1' в условие
$res = BlogArticles::find()->where(['>=', 'viewCount', 1])->orWhere(['flagActive' => 1])->all();​

Сортировка 'ORDER BY'


orderBy() - массив, оприделяюий как отсортировать результаты запроса.
$res = BlogArticles::find()->all();
$res->orderBy([
'id' => SORT_ASC,
'title' => SORT_DESC,
]);​

Обратите внимание, что SORT_ASC и SORT_DESC - это константы, поэтому их не нужно брать в кавычки.

Вот добавление обычного запроса сортировки как строки
$res->orderBy('id ASC, title DESC');​


addOrderBy() - Добавляет в запрос дополнительные столбцы ORDER BY.
$res->orderBy('id ASC');
//условие добавления еще одного параметра сортировки
if(true){
$res->addOrderBy('title DESC');//то же что и предидущий
}​

addGroupBy() - Добавляет в запрос дополнительные столбцы GROUP BY.
$res->groupBy('id');
//условие добавления еще одного параметра GROUP BY
if(true){
$res->addGroupBy('status');//то же что и предидущий
}

$res->all();//получаем результат​

groupBy() - Как сгруппировать результаты запроса.
// ... GROUP BY `id`, `status`
$res = BlogArticles::find()->groupBy(['id', 'status'])->all();​

или строку ->groupBy('id, status') , если названия столбцов простые

addGroupBy() string|array - Добавляет дополнительные столбцы к существующим для группировки
$res = BlogArticles::find()->groupBy(['id'])->addGroupBy('status')->all();​


having() - Условие, которое должно применяться в предложении GROUP BY.Это может быть строка или массив.
HAVING применяется для фильтрации функций и столбцов сгруппированных при помощи GROUP BY
указанных в SELECT.Полезен для условий применимых для агрегатных функций (COUNT(), MAX() ...)
Запрос может быть написан по тем же принципам как и в методе where()
$model = BlogArticles::find()->select(['*', 'cnt' => 'COUNT(*)'])->groupBy('id')->having(['<=','cnt', 1])->all();​

andHaving() - Добавляет дополнительное условие HAVING к существующему.
$query->having(['<=','cnt', 1])
->andHaving(['status' => 1]);​

indexBy() - Имя столбца, по которому должны быть проиндексированы результаты запроса.

all() dозвращает массив объектов с ключами в виде числовых индексов.
А indexBy() позволяет назначить как индекс значение любого столбца для записи
Это свойство используется только для all() .
$model = BlogArticles::find()->indexBy('title')->all();​

Вернет
array (size=7)
'Это тайтл' => 
object(frontend\models\blog\articles\BlogArticles)[143]
...​

limit() и offset() соответствуют LIMIT и OFFSET SQL запроса.
$model = BlogArticles::find()->limit(10)->offset(20)->all();​

join() - объединение таблиц. Определяет фрагмент JOIN
Каждый элемент массива представляет спецификацию одного соединения, которое имеет следующую структуру:
 [$joinType, $tableName, $joinCondition]
Например,
[
['INNER JOIN', 'user', 'user.id = author_id'],
['LEFT JOIN', 'team', 'team.id = team_id'],
]​

innerJoin() - Добавляет часть INNER JOIN к запросу.
leftJoin() - Добавляет часть LEFT OUTER JOIN к запросу.
rightJoin() - Добавляет часть запроса RIGHT OUTER JOIN в запрос.

Запросы выполняются по одинаковому принципу
BlogArticles::find()
->leftJoin('blogCategories', 'blogCategories.id = idCategory')
->groupBy([BlogArticles::tableName().'.id'])
->all();​

union() - Добавляет оператор SQL с помощью оператора UNION.
$query1 = BlogArticles::find()
->select('id, alias, title')
->limit(10);

$query2 = (new \yii\db\Query())
->select('id, alias, title')
->from('blogCategories')
->limit(10);

$model=$query1->union($query2)->all();​

select() - Выбранные столбцы для SELECT
Выбранные столбцы. Например, ['id', 'name']. Это используется для построения предложения SELECT в инструкции SQL. Если не задано, это означает выбор всех столбцов.
$query = BlogArticles::find()
->select('id, alias, title')
->all();//выбрать 'id, alias, title'​

addSelect() - Добавьте столбцы в SELECT-часть запроса.
$model = BlogArticles::find()
->select(['createdBy'])
->distinct()
->addSelect('title')
->all();​
 
selectOption() - Дополнительный параметр, который следует добавить к ключевому слову «SELECT».

->selectOption('SQL_CALC_FOUND_ROWS')

from() - Таблица (ы), из которой следует выбирать.
$query = (new \yii\db\Query())
->select('id, alias, title')
->from('blogCategories')
->all();​

distinct() - Следует ли выбирать только отдельные строки данных.
Оператор SELECT DISTINCT используется для возврата только отдельных (разных) значений.
$query = BlogArticles::find()
->select(['createdBy'])
->distinct()
->all();​

createCommand() - Создает команду DB yii\db\ActiveQuery, которая может использоваться для выполнения этого запроса.
$model = \Yii::$app->db->createCommand('SELECT * FROM blogArticles')->queryAll();​

addParams() - Добавляет дополнительные параметры для привязки к запросу.
$model = (new \yii\db\Query())->select('*')->from('blogArticles')->where('flagActive=:flagActive');
$model = $model->addParams([':flagActive' => 1]);
$model = $model->all();​

prepare() - подготовка данных к запросу. Очень полезная возможность для подготовки запросов
Вот так можно посмотреть строку, которую формирует в качестве запроса yii\db\ActiveRecord и не обязатьельно
искать запрос в понели отладки для yii2
$model = BlogArticles::find()->where(['like', 'alias', 'fffffff']);
var_dump($model->prepare(\Yii::$app->db->queryBuilder)->createCommand()->rawSql);
//покажет результат 
//string 'SELECT * FROM `blogArticles` WHERE `alias` LIKE '%fffffff%'' (length=59)​

Методы фильтрации andFilter - позволяют добавлять условия, в том случае, если значения для условий не пустые.
Такое поведение позволяет добавлять условия к запросам без условий if{} else{}.

Значение считается «пустым», если выполнено одно из следующих условий:
-он равен нулю,
-пустую строку (''),
-строка, содержащая только пробельные символы,
-или пустой массив.

andFilterCompare() - Добавляет условие фильтрации для определенного столбца и
позволяет пользователю выбирать операторы фильтра.Формирует andFilterWhere([$operator, $name, $value]);
$query->andFilterCompare('value', '<=100');
//Соответствует:
$query->andFilterWhere(['<=', 'value', 100]);

$query->andFilterCompare('name', 'Doe', 'like');
//Соответствует:
$query->andFilterWhere(['like', 'name', 'Doe']);​

andFilterWhere() - Добавляет дополнительное условие WHERE к существующему, но игнорирует пустые операнды.
$query->andFilterWhere(['>=', 'price', $this->price]);
$query->andFilterWhere(['<=', 'price', $this->price]);
$query->andFilterWhere(['between', 'price', 10000, 20000]);​

orFilterWhere() - Добавляет дополнительное OR в условие WHERE
$query->orFilterWhere(['like', 'name', $this->globalSearch])
->orFilterWhere(['like', 'content', $this->globalSearch])
->orFilterWhere(['like', 'address', $this->globalSearch]);​

andFilterHaving() - Добавляет дополнительное условие HAVING к существующему, но игнорирует пустые операнды.
Применяются аналогично, но не добавляют AND, а устанавливают условия.

filterHaving() - Устанавливает часть запроса HAVING, но игнорирует пустые операнды.
filterWhere() - Устанавливает часть WHERE запроса, но игнорирует пустые операнды.


each() - Запускает пакетный запрос и извлекает данные по строкам.
Этот метод похож на batch(), за исключением того, что на каждой итерации результата возвращается только одна строка данных.
$model = BlogArticles::find();

foreach ($model->each() as $row) {
echo $row->alias;
} ​

joinWith() - Соединяется с указанными отношениями.
//В модели BlogArticles есть связь
public function getCategory()
{
return $this->hasOne(BlogCategories::className(), ['id' => 'idCategory']);
}

//Выборка всех записей по id категории связанной таблицы
$articles = BlogArticles::find()
->joinWith('category')//соответствует getCategory()
->where([BlogCategories::tableName().'.id' => 1])
->all();

$articles = BlogArticles::find()->joinWith([
'category' => function ($query) {
$query->andWhere(['>', 'viewCount', 100]);
},
])->all(); ​

В данном примере в $query попадает результат связи getCategory() yii\db\ActiveQuery и условия $query->andWhere применяются
к модели категорий. Соответственно, все методы yii\db\ActiveQuery применимы к $query
$articles = BlogArticles::find()->joinWith([
'category' => function ($query) {
$query->onCondition(['id' => 10]);
},
])->all();​

onCondition() - Устанавливает условие ON для реляционного запроса.
Условие будет использоваться в части ON, когда вызывается yii\db\ActiveQuery :: joinWith().
В противном случае условие будет использоваться в части WHERE запроса.
Используйте этот метод для указания дополнительных условий при объявлении отношения в классе yii\db\ActiveRecord:
public function getActiveUsers()
{
return $this->hasMany(User::className(), ['id' => 'user_id'])
->onCondition(['active' => true]);
}​

Также есть orOnCondition() и andOnCondition() . Работают они аналогично, с разницей в формировании условия
OR и AND


innerJoinWith() - Внутренние соединения с указанными отношениями.
$model = BlogArticles::find()->innerjoinWith('category')->all();​

Так реализован данный метод. Поэтому примеры можно посмотреть в joinWith()
<?php
public function innerJoinWith($with, $eagerLoading = true){
   return $this->joinWith($with, $eagerLoading, 'INNER JOIN');
}
​


with() - Определяет отношения, с которыми должен выполняться этот запрос.
$articles = BlogArticles::find()
->with('category')//соответствует getCategory() из связи, объявленной в BlogArticles
->all(); ​

$articles = BlogArticles::find()->with([
'category' => function ($query) {
$query->andWhere(['>', 'viewCount', 100]);
},
])->all();​

Вы можете вызывать с with() несколько раз. Каждый вызов добавит отношения к существующим.
Например, следующие два утверждения эквивалентны:
BlogArticles::find()->with('category', 'tags')->all();
BlogArticles::find()->with('category')->with('tags')->all(); ​

P.S.

Вот видео по теме запросов к базе данных в yii2. Видео не мое, но возможно Вы почерпнете из него что-то интересное. Если видео было полезным, то просьба писать в комментариях...

Приветствую!

Меня зовут Сергей. Я - автор этого блога.

Если Вам был полезен материал на моем сайте, поддержите пожалуйста мой проект, чтобы о нем узнали другие люди - кликните plizz :) на иконку в соц. сети, чтобы поделиться материалом с другими.