Laravel join query with conditions

Question

I have 4 tables.

User table:

id    col1     col2

CoursesAssigned table:

id    user_id      course_id       approved 

CourseInfo table:

id     parent_id 

CourseParents table:

id    start_date      end_date

I think the table names and its column names are self explanatory.

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

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.

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

either

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.

or

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

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) What is about the query that fetches only the unassigned students ?

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.

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.

To obtain unassigned students I have 2 rules :

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

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.

Uncaused

You should look up laravel's documentation on eloquent and relationships. This can all be achieved in a precise manner with ease. – Rope

I detailed my effort – Uncaused

You should be using Eloquent relationships for performing the queries instead of using the query builder. Do you have relationships defined in the models? See laravel.com/docs/5.6/… – Feverfew

@pseudoanime, what relationship are you asking about ? There are models and corresponding DB tables as well ? – Uncaused

is there a coursesassigned relationship in the users table (for example). It should be something along the lines of the following in the User model : public function coursesAssigned() { return $this->hasMany('App\CoursesAssigned'); } If you do, post all your relationships – Feverfew

@pseudoanime, suppose I have. Do you have any solution using ORM ? – Uncaused

@pseudoanime, I think the relationships you are talking about can be pretty easily assumed from the database table structure I mentioned – Uncaused

@pseudoanime, A user can have one or many courses in CoursesAssigned table. Each course in that table will have a parent in CourseInfo table. Each parent (i.e. row) in this table will have its details in CourseParents table. – Uncaused

Can you ask a simplified question, maybe you can rephrase the question something like: How can I get all assigned users where approved is 1. – Cologne

I tried to make the question precise. Let me clarify here : I need to fetch 2 kinds of users - 1) assigned 2) unassigned.. Unassigned users will be obtained applying 2 rules (written as either, or in OP) while assigned users will be obtained using 1 rule. – Uncaused

@TheAlpha, gonna make an edit in OP. – Uncaused

@TheAlpha, added EDIT2 – Uncaused

@TheAlpha, rephrased Edit2 – Uncaused

What is the course_id in CoursesAssigned , is that a foreign key of CourseInfo that maps to id in the CourseInfo ? – Cologne

@TheAlpha, your guess is right , course_id in CoursesAssigned is a foreign key of CourseInfo that maps to id in the CourseInfo. I said in OP I think the table names and its column names are self explanatory. However, I clarified it now. – Uncaused

Answer 1

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:

User Model (for usres table):

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);
    }
}

Course Model (for CoursesAssigned table):

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 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 Model (for CourseParents table):

namespace App;


use Illuminate\Database\Eloquent\Model;

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

}

Get the assigned users:

$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.

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

---

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.

Cologne

The last where part is $query->where('end_date', >= \Carbon\Carbon::now()). I changed this part to $query->where('end_date', '>=', date('Y-m-d') ). ANd with a dd() function I found the query to be select * from users where exists (select * from coursesassigned where coursesassigned.user_id = users.id and approved = 1). That means the courseInfo.courseParent relation is ingored. What should be done here ? – Uncaused

Maybe you didn't follow the instructions properly. Make sure all relationships are created inside each models. What you dd, Have you get the full query, change the get-> to ->toSql() and then dd($assignedUsers). – Cologne

I removed the ->get() part, then used echo " sql = ".$assignedUsers->toSql(); and dd($assignedUsers->toSql(), $assignedUsers->getBindings());. But did not get the expected result – Uncaused

I checked the relations as well and those are correct. The solution can be in ORM, query builder or raw Mysql for Laravel format as well – Uncaused

What is about query builder or raw MySQL ? – Uncaused

Check the update and try now. check with(['courses.courseInfo.courseParent' => function($query) {...}). – Cologne

The with part was ->with(['courseInfo.courseParent' => function($query) {...}) but it should have ->with(['courses.courseInfo.courseParent' => function($query) {...}). – Cologne

Let us continue this discussion in chat. – Uncaused