Cakephp 3 不在查询中 [英] Cakephp 3 NOT IN query

问题描述

我认为这是一种常见的模式，但我找不到优雅的 CakePHP 方法.这个想法是从已经选择的列表中删除值.使用 Cake 书中的一个例子:

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)

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

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 不在查询中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！