Laravel带条件的联接查询 [英] Laravel join query with conditions

问题描述

我有4张桌子.

用户表:

id    col1     col2

分配的课程表:

id    user_id      course_id       approved 

CourseInfo 表:

id     parent_id 

CourseParents 表:

id    start_date      end_date

我认为表名及其列名是不言自明的.

我有2种用户-i)已分配ii)未分配.我在2个不同的页面中显示它们.

I have 2 kinds of users - i) assigned ii) unassigned. I show them in 2 different pages.

在显示分配的学生时，我需要 users 表中的那些学生，因为每个人在 CoursesAssigned 表中至少一行 user_id是他自己的用户ID，approved字段为1，该行中的course_id具有其自己的parent_id(来自 CourseInfo )和end_date(来自 CourseParents )大于或等于今天.

While showing the assigned students I need those students from users table for each of whom there is at least one row in CoursesAssigned table where user_id is his own user id and the approved field is 1 and the course_id in that row has its own parent_id (from CourseInfo) with end_date (from CourseParents) greater than or equal to today.

要显示未分配的学生，我需要 users 表中的那些学生，每个人-

For showing unassigned students, I need those students from users table, for each of whom -

两个

CoursesAssigned 表中有 NO 行，其中user_id是他自己的用户ID，approved列的值为1.用户，可能存在一个具有自己的用户ID的行，但approved字段包含0.

there is NO row in CoursesAssigned table where the user_id is his own user id and the approved column has a value 1. That is for an unassigned user, there may exist a row with his own user id but the approved field contains 0.

或

CoursesAssigned 表中可能存在行，其中user_id是他自己的用户ID，而approved字段的值为1，但parent_id是从 CourseInfo获取的来自 CourseParents 的end_date小于今天的日期.

there may be rows in CoursesAssigned table with the user_id being his own user id and the approved field having a value 1 but the parent_id obtained from CourseInfo has from CourseParents an end_date which is less than today's date.

我可以为分配的学生编写查询，例如:

I can write a query for assigned students like:

    $date=date('Y-m-d');

    $records = User::join('CoursesAssigned','users.id','=','CoursesAssigned.user_id')
                 ->join('CourseInfo','CourseInfo.id','=','CoursesAssigned.course_id')
                  ->join('CourseParents','CourseParents.id','=',
'CourseInfo.parent_id')
                  ->where('CoursesAssigned.approved','=',1)
                  ->where('CourseParents.end_date','>=',$date)
                  ->select('users.id','users.col1','users.col2')
                  ->orderBy('users.id','desc');

但这不会产生正确的结果，因为它不会在最少 1行中检查 CoursesAssigned 表中是否满足所有提及的条件. Q1)还是应该?

But that should not produce the correct result as that does not check CoursesAssigned table for at least 1 row that meets all mentioned criteria. Q1) Or should it ?

Q2)关于仅获取未分配学生的查询又如何呢?

Q2) What is about the query that fetches only the unassigned students ?

答案可以是ORM，查询生成器甚至是Laravel格式的原始MySql.

EDIT : The answer can be in ORM, query builder or even raw MySql for Laravel format.

让我在这里说明一下情况: 我需要分别获取已分配和未分配的用户.

EDIT2 : Let me clarify the scenario here : I need to fetch both assigned and unassigned users separately.

要获得分配的用户，我有1条规则:如何获得在 CoursesAssigned 表中具有至少1个已批准课程的用户和父级(从 CourseInfo 表中获得) )的结束日期(在 CourseParents 表中)大于或等于今天.

To obtain assigned users I have 1 rule: How can I get those users who have at least 1 approved course in CoursesAssigned table and the parent (obtained from CourseInfo table )of that course has the end_date (in CourseParents table) greater than or equal to today.

要获得未分配的学生，我有2条规则:

To obtain unassigned students I have 2 rules :

规则1:获取那些没有任何已批准课程的学生(即所有课程都已批准= 0).他们是未分配的学生

Rule 1: Get those tudents who do not have any approved course (i.e. all courses have approved =0). They are unassigned students

规则2:使那些已批准课程但没有一个批准课程符合指定学生标准的学生.这意味着那里没有批准的课程，其家长的结束日期大于或等于今天，他们也是未分配的学生.

Rule 2: Get those students who have approved courses but none of the approved courses meet the criteria of those for assigned students . That means there is no approved course there that has a parent whose end_date is greater than or equal to today.They are also unassigned students.

推荐答案

我仍然不太完全了解您的表关系，但是根据我的猜测，我想出了以下解决方案，首先使用Eloquent模型创建关系:

I'm still not completely sure about your table relationships but from my guess, I came up with the following solution, first create the relationships using Eloquent models:

用户模型(用于usres表):

namespace App;

use App\Course;
use Illuminate\Notifications\Notifiable;
use Illuminate\Foundation\Auth\User as Authenticatable;

class User extends Authenticatable
{
    use Notifiable;

    public function courses()
    {
        return $this->hasMany(Course::class);
    }
}

课程模型(用于CoursesAssigned表):

namespace App;

use App\CourseInfo;
use Illuminate\Database\Eloquent\Model;

class Course extends Model
{
    protected $table = 'CoursesAssigned';

    public function courseInfo()
    {
        return $this->belongsTo(CourseInfo::class);
    }
}

CourseInfo模型(用于CourseInfo表):

CourseInfo Model (for CourseInfo table):

namespace App;

use App\CourseParent;
use Illuminate\Database\Eloquent\Model;

class CourseInfo extends Model
{
    protected $table = 'CourseInfo';

    public function courseParent()
    {
        return $this->belongsTo(CourseParent::class, 'parent_id');
    }
}

CourseParent模型(用于CourseParents表):

CourseParent Model (for CourseParents table):

namespace App;


use Illuminate\Database\Eloquent\Model;

class CourseParent extends Model
{
    protected $table = 'CourseParents';

}

获取分配的用户:

$assignedUsers = User::whereHas('courses', function($query) {
    $query->where('approved', 1)
          ->with(['courses.courseInfo.courseParent' => function($query) {
              $query->where('end_date', >= \Carbon\Carbon::now());
          }]);
})->get(); // paginate(...) for paginated result.

如果我的假设正确的话，这应该可以工作.首先尝试assignedUsers，然后让我知道，然后我将针对其他要求进行研究.还要确保您确实了解模型之间的雄辩关系，并正确地实现所有事情(使用正确的名称空间).

This should work if my assumption is correct. Try this for assignedUsers first then let me know and then I'll look into it for the other requirements. Also make sure that you do understand about Eloquent relationships between models and implement everything correctly (with correct namespace).

注意:此答案是不完整的，需要进一步的信息，因为该答案是通过电话与OP直接对话的结果(OP打电话给我，因为我可及)，因此如有需要，我们将进行一些更改，并将继续进行.逐步解决，直到我提出解决方案或留下解决方案为止.

Note: This answer is incomplete and need further info because the answer is a result of direct conversation with OP over phone (OP called me because I was reachable) so some changes will be made overtime if needed and will be continued step by step until I can come up with a solution or leave it.

这篇关于Laravel带条件的联接查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！