How to do this query?

I have these tables courses and lessons, with properly relationship set:

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

//Lesson
public function course()
{
    return $this->belongsTo(Course::class);
}

so 1 course can have many lessons, and a lesson belongs to a course, basic stuff…

each lesson has a date field which indicates the start of the class

what i want to accomplish is determine the start and end of the course based on its lessons date,

(assuming today is 2016-05-10)

Course 1 = 2 lessons, (the earliest lessons 2016-05-01 and the latest lesson 2016-05-20) = Released
Course 2 = 2 lessons, (the earliest lessons 2016-05-01 and the latest lesson 2016-05-09) = Expired
Course 3 = 2 lessons, (the earliest lessons 2016-05-11 and the latest lesson 2016-05-20) = Unreleased

so assuming i would be doing the queries thought query scopes.. i would end up doing this ->

{
    Course::released()->get();
    Course:expired()->get();
    Course:unreleased()->get();
}

i have been trying a lot of query->where, joins, whereExists… a lot of combinations of those… and i am still stuck… any idea how to accomplish this?

willvincent

10 years ago

Level 54

You'd probably save yourself a bunch of headache by storing start/end dates on the course itself, and updating those values when lessons are added to or removed from a course.

To get the earliest and latest lesson dates in a course you should be able to do this:

$course_dates = DB::table('lessons')
  ->where('course_id', '=', $course_id)
  ->select(DB::raw('min(start_date) as earliest'), DB::raw('max(start_date) as latest'))
  ->get();

If your start/end dates existed on the course fetching courses for those three states would be much easier...

$released = Course::with('lessons')
              ->where('start_date', '<=', $today)
              ->where('end_date', '>', $today)
              ->get();

$expired = Course::with('lessons')
              ->where('end_date', '<=', $today)
              ->get();

$unreleased = Course::with('lessons')
              ->where('start_date', '>', $today)
              ->get();

milewski

10 years ago

Level 1

i really need to have it done on the mysql side, because the example i gave is just an abstraction of what i am really trying to do.. in real its a app to generate sql queries with a drag and drop interface...the user create the scene, and sql queries are generated to represent what he created.

the first example u gave:

$course_dates = DB::table('lessons')
  ->where('course_id', '=', $course_id) <--- i dont have an $course_id i am trying to grab all the records
  ->select(DB::raw('min(start_date) as earliest'), DB::raw('max(start_date) as latest'))
  ->get();

willvincent

10 years ago

Level 54

Ok, then this should give you start/end dates for every course.

$course_dates = DB::table('lessons')
  ->select('course_id', DB::raw('min(start_date) as earliest'), DB::raw('max(start_date) as latest'))
  ->groupBy('course_id')
  ->get();

Beyond this you really need to be much more explicit about what you're trying to acheive since apparently this isn't it.

Please or to participate in this conversation.