SQL один ко многим с исключением

Общие вопросы по использованию второй версии фреймворка. Если не знаете как что-то сделать и это про Yii 2, вам сюда.
Ответить
Arnowt
Сообщения: 170
Зарегистрирован: 2013.09.13, 11:11

SQL один ко многим с исключением

Сообщение Arnowt » 2019.11.15, 18:50

Есть 2 таблицы
id | ФИО
id | Оценки

Нужно выбрать все ФИО у которых есть 4 или 5, но нет 2
Вася - 5,5,4,4,3 - подходит
Петя - 5,5,2,4,3 - НЕ подходит

Понятно что можно двумя запросами, выбрать тех кто имеет 4,5, потом тех у кого есть хоть одна 2, а потом, исключить вторых из первых.
Но все-же хотелось бы одним запросом и на yii :)

Аватара пользователя
proctoleha
Сообщения: 276
Зарегистрирован: 2016.07.10, 19:00

Re: SQL один ко многим с исключением

Сообщение proctoleha » 2019.11.16, 08:17

Код: Выделить всё

        $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();
Вот за что я не люблю линукс, так это за свои кривые, временами, руки

unknownby
Сообщения: 35
Зарегистрирован: 2019.11.05, 16:34

Re: SQL один ко многим с исключением

Сообщение unknownby » 2019.11.16, 09:05

proctoleha писал(а):
2019.11.16, 08:17
При этом не учитывается, что у учеников может быть и единица?
А где же троечка? Проходной балл
Можно было бы и через (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
Сообщения: 276
Зарегистрирован: 2016.07.10, 19:00

Re: SQL один ко многим с исключением

Сообщение proctoleha » 2019.11.16, 10:51

unknownby писал(а):
2019.11.16, 09:05
При этом не учитывается, что у учеников может быть и единица?
Где в условии задачи сказано про 1?
Вот за что я не люблю линукс, так это за свои кривые, временами, руки

unknownby
Сообщения: 35
Зарегистрирован: 2019.11.05, 16:34

Re: SQL один ко многим с исключением

Сообщение unknownby » 2019.11.16, 11:02

proctoleha писал(а):
2019.11.16, 10:51
unknownby писал(а):
2019.11.16, 09:05
При этом не учитывается, что у учеников может быть и единица?
Где в условии задачи сказано про 1?
Это сказано в жизни :D Жизненный опыт подсказывает мне ;)
Либо 10-ти бальная система, либо 5-ти бальная система, подразумевает то, что оценки ученикам ставятся от 1 до 5/10.
По твоему решению, если у ученика будут оценки от 3 до 5, он подходит, однако если будет 1 и от 3 до 5, он тоже подходит :D

Arnowt
Сообщения: 170
Зарегистрирован: 2013.09.13, 11:11

Re: SQL один ко многим с исключением

Сообщение Arnowt » 2019.11.16, 13:37

Иллюстрация с оценками выбрана для простоты, на самом деле задача сложнее
Вот адаптированная/упрощенная версия запроса на чистом 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);  
Как видите разница в том что ий выносит условия из ON в WHERE
Изображение
Последний раз редактировалось Arnowt 2019.11.16, 14:00, всего редактировалось 4 раза.

Аватара пользователя
proctoleha
Сообщения: 276
Зарегистрирован: 2016.07.10, 19:00

Re: SQL один ко многим с исключением

Сообщение proctoleha » 2019.11.16, 13:41

unknownby писал(а):
2019.11.16, 11:02
proctoleha писал(а):
2019.11.16, 10:51
unknownby писал(а):
2019.11.16, 09:05
При этом не учитывается, что у учеников может быть и единица?
Где в условии задачи сказано про 1?
Это сказано в жизни :D Жизненный опыт подсказывает мне ;)
Стоп, еще раз: есть формальные условия задачи, есть ответ. При чем тут жизненный опыт?
Вот за что я не люблю линукс, так это за свои кривые, временами, руки

yiiliveext
Сообщения: 523
Зарегистрирован: 2019.08.13, 01:49

Re: SQL один ко многим с исключением

Сообщение yiiliveext » 2019.11.16, 13:45

Код: Выделить всё

 $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');

yiiliveext
Сообщения: 523
Зарегистрирован: 2019.08.13, 01:49

Re: SQL один ко многим с исключением

Сообщение yiiliveext » 2019.11.16, 14:03

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()

Arnowt
Сообщения: 170
Зарегистрирован: 2013.09.13, 11:11

Re: SQL один ко многим с исключением

Сообщение Arnowt » 2019.11.16, 14:34

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 писать. )
Еще раз спасибо.
Но все-же тему пока не считаю решенной, надеюсь на существование более красивого варианта решения.

yiiliveext
Сообщения: 523
Зарегистрирован: 2019.08.13, 01:49

Re: SQL один ко многим с исключением

Сообщение yiiliveext » 2019.11.16, 14:40

Arnowt писал(а):
2019.11.16, 14:34
С некоторыми небольшими правками этот код даст нужный SQL, но прибегая к join() мы не используем связи модели.
Понятно что на безрыбье... но так можно и на чистом SQL писать. )
Это не имеет значения, на выходе все равно генерится SQL. Суть в построении запроса через ActiveQuery, что и получаем на выходе.
Пы.Сы.
Здесь

Код: Выделить всё

INNER JOIN `categorylinks` `cl0` ON `page`.`page_id` = `cl0`.`cl_from` AND ((`cl0`.`cl_to`='Тип_новости')OR (`cl0`.`cl_to`='Тип_статья_в_журнале'))
условия для cl_to можно вынести в WHERE, они не нужны в ON
Последний раз редактировалось yiiliveext 2019.11.16, 15:17, всего редактировалось 4 раза.

yiiliveext
Сообщения: 523
Зарегистрирован: 2019.08.13, 01:49

Re: SQL один ко многим с исключением

Сообщение yiiliveext » 2019.11.16, 14:59

Но если очень сильно хочется без join, так что аж кушать не можете, то приведу пример для оценок.
Связи в 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');

Arnowt
Сообщения: 170
Зарегистрирован: 2013.09.13, 11:11

Re: SQL один ко многим с исключением(решена)

Сообщение Arnowt » 2019.11.16, 18:38

Вот правильный ответ, выкопал таки в документации ))

Код: Выделить всё

$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();

Ответить