Laravel select from DB where in Collection - Laravel Nova

Question

I have a DB, "views," with many, many entries. I also have a "Courses" table, which these views are one-many related to. In Laravel Nova, I can get a metric of all views over time for a course with some code like this:

public function calculate(Request $request)
{
    return $this->countByDays($request, view::where('viewable_id', $request->resourceId));
}

In this case, viewable_id is the id of the course, and $request->resourceId gives the ID of the course to sort by. Pretty simple.

However, now things get a little difficult. I have another model called Teachers. Each Teacher can have many courses, also in a one-many relationship. How do I get a metric of views over time for all the courses that teacher teaches?

I assumed the simplest way to do this would be to create a Laravel Collection with all courses the Teacher teaches (not exactly efficient), and then select all views in the database where viewable_id matches one of the courses in that list. Of course, by posting this, I couldn't figure out how to do that.

Of course, once this is figured out, I'd love to do the same thing for Categories (though that should function in a very identical manner to Teachers, so I don't need to ask that question).

score 2 · Accepted Answer · edited Nov 09 '18 at 16:26

How do I get a metric of views over time for all the courses that teacher teaches?

This should be the "countByDays" of views where the viewable_id is in the list of course ids that the teacher teaches.

An SQL query statement to achieve that is given below:

select * from "views" 
where "viewable_id" in (select "id" from "courses" where "teacher_id" = ?)

The Eloquent query should be similar to:

$this->countByDays($request,
  view::whereIn(
    'viewable_id',
     Course::with('teacher')
             ->select('id')
             ->where('teacher_id', $request->resourceId)
  )
);

Laravel select from DB where in Collection - Laravel Nova

1 Answers1