SQL один ко многим с исключением
SQL один ко многим с исключением
Есть 2 таблицы
id | ФИО
id | Оценки
Нужно выбрать все ФИО у которых есть 4 или 5, но нет 2
Вася - 5,5,4,4,3 - подходит
Петя - 5,5,2,4,3 - НЕ подходит
Понятно что можно двумя запросами, выбрать тех кто имеет 4,5, потом тех у кого есть хоть одна 2, а потом, исключить вторых из первых.
Но все-же хотелось бы одним запросом и на yii
id | ФИО
id | Оценки
Нужно выбрать все ФИО у которых есть 4 или 5, но нет 2
Вася - 5,5,4,4,3 - подходит
Петя - 5,5,2,4,3 - НЕ подходит
Понятно что можно двумя запросами, выбрать тех кто имеет 4,5, потом тех у кого есть хоть одна 2, а потом, исключить вторых из первых.
Но все-же хотелось бы одним запросом и на yii
- proctoleha
- Сообщения: 298
- Зарегистрирован: 2016.07.10, 19:00
Re: SQL один ко многим с исключением
Код: Выделить всё
$query = (new Query())->select(['student_id'])->from(RatingStudent::tableName())->where(['rating' => 2]);
$result = Student::find()
->alias('s')
->joinWith('ratingStudents r')
->where(['or', ['r.rating' => 4], ['r.rating' => 5]])
->andWhere(['not in', 's.id', $query])
->groupBy('s.id')
->all();
Вот за что я не люблю линукс, так это за свои кривые, временами, руки
Re: SQL один ко многим с исключением
При этом не учитывается, что у учеников может быть и единица?
А где же троечка? Проходной балл
Можно было бы и через (in) сделать, вместо (or). На любителя
Код: Выделить всё
$query = (new Query())->select(['student_id'])->from(RatingStudent::tableName())->where(['or', ['rating' => 1], ['rating' => 2]]);
$result = Student::find()
->alias('s')
->joinWith('ratingStudents r')
->where(['or', ['r.rating' => 3], ['r.rating' => 4], ['r.rating' => 5]])
->andWhere(['not in', 's.id', $query])
->groupBy('s.id')
->all();
Последний раз редактировалось unknownby 2019.11.16, 11:03, всего редактировалось 1 раз.
- proctoleha
- Сообщения: 298
- Зарегистрирован: 2016.07.10, 19:00
Re: SQL один ко многим с исключением
Где в условии задачи сказано про 1?
Вот за что я не люблю линукс, так это за свои кривые, временами, руки
Re: SQL один ко многим с исключением
Это сказано в жизни Жизненный опыт подсказывает мне
Либо 10-ти бальная система, либо 5-ти бальная система, подразумевает то, что оценки ученикам ставятся от 1 до 5/10.
По твоему решению, если у ученика будут оценки от 3 до 5, он подходит, однако если будет 1 и от 3 до 5, он тоже подходит
Re: SQL один ко многим с исключением
Иллюстрация с оценками выбрана для простоты, на самом деле задача сложнее
Вот адаптированная/упрощенная версия запроса на чистом SQL
--------------------------------------------------------------------------------------------------------
Вот реальный запрос который нужно провести, он рабочий
Вот так я пытаюсь его запрограммировать
А вот результат
Как видите разница в том что ий выносит условия из ON в WHERE
Вот адаптированная/упрощенная версия запроса на чистом SQL
Код: Выделить всё
SELECT st.fio FROM student st
INNER JOIN points p1 ON st.id=p1.id AND (p1.num=4 OR p1.num=5)
LEFT JOIN points p2 ON st.id=p2.id AND (p2.num=2)
WHERE p2.num IS NULL
Вот реальный запрос который нужно провести, он рабочий
Код: Выделить всё
SELECT * FROM `page`
INNER JOIN `categorylinks` `cl0` ON `page`.`page_id` = `cl0`.`cl_from` AND ((`cl0`.`cl_to`='Тип_новости')OR (`cl0`.`cl_to`='Тип_статья_в_журнале'))
LEFT JOIN `categorylinks` `cl1` ON `page`.`page_id` = `cl1`.`cl_from` AND (`cl1`.`cl_to`='Скрытая')
WHERE (`page_namespace`=0) AND (`page_is_redirect`=0) AND (cl1.cl_to IS NULL);
Код: Выделить всё
$rr = Page::find()
->with('revision')
->innerJoinWith(['categoryLinks cl0' => function ($q) {
$q->where(['or',
['cl0.cl_to' => 'Тип_новости'],
['cl0.cl_to' => 'Тип_статья_в_журнале']]);
}])
->joinWith(['categoryLinks cl1' => function ($q) {
$q->where(['cl1.cl_to' => 'Скрытая']);
}])
->andWhere(['page_namespace' => 0])
->andWhere(['page_is_redirect' => 0])
->andWhere('cl1.cl_to IS NULL')
->createCommand()->getRawSql()
Код: Выделить всё
SELECT `page`.* FROM `page`
INNER JOIN `categorylinks` `cl0` ON `page`.`page_id` = `cl0`.`cl_from`
LEFT JOIN `categorylinks` `cl1` ON `page`.`page_id` = `cl1`.`cl_from`
WHERE (`page_namespace`=0) AND (`page_is_redirect`=0)
AND ((`cl0`.`cl_to`='Тип_новости') OR (`cl0`.`cl_to`='Тип_статья_в_журнале'))
AND (`cl1`.`cl_to`='Скрытая')
AND (cl1.cl_to IS NULL);
Последний раз редактировалось Arnowt 2019.11.16, 14:00, всего редактировалось 4 раза.
- proctoleha
- Сообщения: 298
- Зарегистрирован: 2016.07.10, 19:00
Re: SQL один ко многим с исключением
Стоп, еще раз: есть формальные условия задачи, есть ответ. При чем тут жизненный опыт?
Вот за что я не люблю линукс, так это за свои кривые, временами, руки
-
- Сообщения: 910
- Зарегистрирован: 2019.08.13, 01:49
Re: SQL один ко многим с исключением
Код: Выделить всё
$query = Student::find()
->alias('s')
->join('LEFT JOIN', 'rating a', 's.id = a.student_id AND a.value = 2')
->joinWith('ratings b')
->where(['or', ['b.value' => 4], ['b.value' => 5]])
->andWhere(['a.student_id' => null])
->groupBy('s.id');
-
- Сообщения: 910
- Зарегистрирован: 2019.08.13, 01:49
Re: SQL один ко многим с исключением
Arnowt писал(а): ↑2019.11.16, 13:37
Вот так я пытаюсь его запрограммироватьА вот результатКод: Выделить всё
$rr = Page::find() ->with('revision') ->innerJoinWith(['categoryLinks cl0' => function ($q) { $q->where(['or', ['cl0.cl_to' => 'Тип_новости'], ['cl0.cl_to' => 'Тип_статья_в_журнале']]); }]) ->joinWith(['categoryLinks cl1' => function ($q) { $q->where(['cl1.cl_to' => 'Скрытая']); }]) ->andWhere(['page_namespace' => 0]) ->andWhere(['page_is_redirect' => 0]) ->andWhere('cl1.cl_to IS NULL') ->createCommand()->getRawSql()
Код: Выделить всё
$rr = Page::find()
->with('revision')
->join('LEFT JOIN' , 'category_link cl1', '`page`.`page_id` = `cl1`.`cl_from` AND `cl1`.`cl_to` = "Скрытая" ')
->joinWith('categoryLinks cl0')
->where(['or',
['cl0.cl_to' => 'Тип_новости'],
['cl0.cl_to' => 'Тип_статья_в_журнале']]);
->andWhere(['page_namespace' => 0])
->andWhere(['page_is_redirect' => 0])
->andWhere('cl1.cl_to IS NULL')
->createCommand()->getRawSql()
Re: SQL один ко многим с исключением
Спасибо большое!yiiliveext писал(а): ↑2019.11.16, 14:03Код: Выделить всё
$rr = Page::find() ->with('revision') ->join('LEFT JOIN' , 'category_link cl1', '`page`.`page_id` = `cl1`.`cl_from` AND `cl1`.`cl_to` = "Скрытая" ') ->joinWith('categoryLinks cl0') ->where(['or', ['cl0.cl_to' => 'Тип_новости'], ['cl0.cl_to' => 'Тип_статья_в_журнале']]); ->andWhere(['page_namespace' => 0]) ->andWhere(['page_is_redirect' => 0]) ->andWhere('cl1.cl_to IS NULL') ->createCommand()->getRawSql()
С некоторыми небольшими правками этот код даст нужный SQL, но прибегая к join() мы не используем связи модели.
Понятно что на безрыбье... но так можно и на чистом SQL писать. )
Еще раз спасибо.
Но все-же тему пока не считаю решенной, надеюсь на существование более красивого варианта решения.
-
- Сообщения: 910
- Зарегистрирован: 2019.08.13, 01:49
Re: SQL один ко многим с исключением
Это не имеет значения, на выходе все равно генерится SQL. Суть в построении запроса через ActiveQuery, что и получаем на выходе.
Пы.Сы.
Здесь
Код: Выделить всё
INNER JOIN `categorylinks` `cl0` ON `page`.`page_id` = `cl0`.`cl_from` AND ((`cl0`.`cl_to`='Тип_новости')OR (`cl0`.`cl_to`='Тип_статья_в_журнале'))
Последний раз редактировалось yiiliveext 2019.11.16, 15:17, всего редактировалось 4 раза.
-
- Сообщения: 910
- Зарегистрирован: 2019.08.13, 01:49
Re: SQL один ко многим с исключением
Но если очень сильно хочется без join, так что аж кушать не можете, то приведу пример для оценок.
Связи в Student
Запрос
Связи в Student
Код: Выделить всё
/**
* @return \yii\db\ActiveQuery
*/
public function getRatings()
{
return $this->hasMany(Rating::class, ['student_id' => 'id']);
}
/**
* @return \yii\db\ActiveQuery
*/
public function getTwoRatings()
{
return $this->hasMany(Rating::class, ['student_id' => 'id'])->onCondition(['value' => 2]);
}
Код: Выделить всё
$query = Student::find()
->alias('s')
->joinWith('twoRatings a')
->joinWith('ratings b')
->where(['or', ['b.value' => 4], ['b.value' => 5]])
->andWhere(['a.student_id' => null])
->groupBy('s.id');
Re: SQL один ко многим с исключением(решена)
Вот правильный ответ, выкопал таки в документации ))
Код: Выделить всё
$rr = Page::find()
->innerJoinWith(['categoryLinks cl0' => function ($q) {
$q->onCondition(['or',
['cl0.cl_to' => 'Тип_новости'],
['cl0.cl_to' => 'Тип_статья_в_журнале']]);
}])
->joinWith(['categoryLinks cl1' => function ($q) {
$q->onCondition(['cl1.cl_to' => 'Скрытая']);
}])
->andWhere(['page_namespace' => 0])
->andWhere(['page_is_redirect' => 0])
->andWhere('cl1.cl_to IS NULL')
->createCommand()->getRawSql();