Cakephp 3 NOT IN查询 [英] Cakephp 3 NOT IN query

问题描述

我认为这是一个常见的模式，但我找不到优雅的CakePHP方式。想法是从已经选择的列表中删除值。要使用蛋糕书中的示例：

I think this is a common pattern, but I can't find the elegant CakePHP way of doing it. The idea is to remove the values from a list which have already been chosen. To use an example from the Cake book:

table Students (id int, name varchar)
table Courses (id int, name varchar)
table CoursesMemberships (id int, student_id int, course_id int, days_attended int, grade varchar)

b $ b

所有我想做的是创建一个查询，返回给定学生尚未注册的课程，最有可能填充一个选择下拉列表。

All I want to do is create a query which returns the courses that a given student has not yet signed up for, most probably to populate a select dropdown.

如果我不使用Cake，我会做类似

If I weren't using Cake, I'd do something like

select * from Courses where id not in 
    (select course_id from CoursesMemberships where student_id = $student_id)

或者可能是一个等效的NOT EXISTS子句，或者外面的连接欺骗，但你得到的想法。

Or maybe an equivalent NOT EXISTS clause, or outer join trickery, but you get the idea.

我很难说如何在Cake做优雅。在我看来，这将是一个常见的需求，但我研究了一段时间，以及尝试一些查询的想法，无济于事。

I'm stumped how to do this elegantly in Cake. It seems to me that this would be a common need, but I've researched for awhile, as well as tried some query ideas, to no avail.

推荐答案

找到IMO最优雅的答案...使用notMatching（）选项：

Found IMO the most elegant answer... use the notMatching() option:

$data = $this->Courses->find("list")
                      ->notMatching("Students", 
                         function($q) use ($student_id) {
                            return $q->where(["Students.id"=>$student_id]);
                         }
                      );

这假设学生有很多课程和课程有许多学生。

This assumes that Students HasMany Courses and Courses HasMany Students of course.

我认为这是最优雅的答案，因为它不依赖于知道任何SQL，并且表示我试图使用Cake的语义实现的逻辑。

I think this is the most elegant answer since it doesn't depend on knowing any SQL, and represents the logic of what I'm trying to achieve using Cake's semantics only.

这篇关于Cakephp 3 NOT IN查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！