在 yii 中使用 CActiveDataProvider 获取数据

发布于 2024-10-05 22:34:04 字数 600 浏览 0 评论 0原文

我有 3 个表，标准关系多对多用户（id，...）-> Users_Has_Courses(Users_id, Courses_id) ->; Courses(id,...)

课程模型有下一个关系

'users' => array(self::MANY_MANY, 'Users', 'users_has_courses(Courses_id, Users_id)')

用户模型有下一个关系

'courses' => array(self::MANY_MANY, 'Courses', 'users_has_courses(Users_id, Courses_id)')

请说明我如何获取课程列表，其中指定“id”的用户尚未通过 CActiveDataProvider 订阅？换句话说，我需要这个简单 SQL 查询的类似物，

select * from Courses where id not in (select Courses_id from users_has_courses where Users_id = 2)

感谢您的帮助

原文

I have 3 tables, standart relation MANY-TO-MANY
Users(id,...) -> Users_Has_Courses(Users_id, Courses_id) -> Courses(id,...)

Courses Model has next relation

'users' => array(self::MANY_MANY, 'Users', 'users_has_courses(Courses_id, Users_id)')

Users Model has next relation

'courses' => array(self::MANY_MANY, 'Courses', 'users_has_courses(Users_id, Courses_id)')

Please, say how I can get list of courses, on which user with specified "id" hasn't been subscribed with CActiveDataProvider ?
Otherwords, I need an analogue of this plain SQL query

select * from Courses where id not in (select Courses_id from users_has_courses where Users_id = 2)

thanks for the help

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

网白 2024-10-12 22:34:04

尝试使用参数化的命名范围来封装查询，而不是常规的“关系”。在您的课程模型中，添加此范围函数以获取用户不在其中的所有课程的列表：

public function userNotIn($user_id)
{
  $criteria=new CDbCriteria();
  $criteria->condition .= 't.id NOT IN (SELECT users_has_courses.Courses_id FROM users_has_courses WHERE users_has_courses.Users_id = :userid)';
  $criteria->params[':userid'] = $user_id;
  $this->getDbCriteria()->mergeWith($criteria);
  return $this;
}

然后您应该能够执行以下操作：

$coursesNotIn=new CActiveDataProvider(Courses::model()->userNotIn($user->id));

该代码完全未经测试，但原则上应该可以工作。当我有一个复杂的查询时，我经常做这种事情，但我仍然想使用 AR 功能，比如 CActiveDataProvider。在此处阅读有关“命名范围”的更多信息：

http: //www.yiiframework.com/doc/guide/1.1/en/database.ar#parameterized-named-scopes

祝你好运！

Instead of a regular "relation", try a parametrized Named Scope to encapsulate the query. In your Courses model, add this scope function to get a list of all the courses the user is not in:

public function userNotIn($user_id)
{
  $criteria=new CDbCriteria();
  $criteria->condition .= 't.id NOT IN (SELECT users_has_courses.Courses_id FROM users_has_courses WHERE users_has_courses.Users_id = :userid)';
  $criteria->params[':userid'] = $user_id;
  $this->getDbCriteria()->mergeWith($criteria);
  return $this;
}

Then you should be able to do this:

$coursesNotIn=new CActiveDataProvider(Courses::model()->userNotIn($user->id));

This code is completely untested, but it should work in principle. I do this sort of thing often when I have a complex query but I still want to use the AR features, like CActiveDataProvider. Read more about "named scopes" here:

http://www.yiiframework.com/doc/guide/1.1/en/database.ar#parameterized-named-scopes

Good luck!

回复收藏 0 原文

~没有更多了~