Raw SQL vs Eloquent query

Hi,

I am trying to get my head round using a collection instead of a DB:: query the following code returns results a a json object but is not quite what i need:

$entries = DB::table('timesheet_entries')
            ->join('timesheets', 'timesheet_entries.timesheet_id', '=', 'timesheets.id')
            ->where('client_id', $client_id)
            ->where('project_id', $project_id)
            ->whereBetween('timesheet_entries.updated_at', [$start, $end])
            ->select('timesheet_entries.*', 'timesheets.user_id')
            ->get();

I got part of the way but I am struggling with the JOIN part of the query here is what I have so far:

$entries = TimesheetEntry::where('client_id', $client_id)
            ->where('project_id', $project_id)
            ->whereBetween('timesheet_entries.updated_at', [$start, $end])
            ->get();

Now the reason I am using eloquent over DB for this is that i have no idea how to get blade to display data objects from a DB:: query as whoops tells me it can't.

any help would be greatly appreciated as this will fix 4 potential issues I have found so far.

in addition I need to filter the results by user_id that resides in the related timesheet table

cjconnor24

8 years ago

Level 1

I may be way off here - but do you have your eloquent relationships configured in your models? If so, perhaps you could access them that way?

I'm not familiar with your system but say a user has many timesheet entries...

// USER MODEL - Eloquent Relationship
  public function TimeSheetEntries()
    {
        return $this->hasMany(TimesheetEntry::class);
    }

Then you could access

$user = User::find(2); // The user you wish to display
$user->TimeSheetEntries; // would return a collection of timesheet entries for that user

Like I said, maybe I'm way off

mstarkey

8 years ago

Level 31

Thanks for replying CJ, my structure only associates the timesheet with the user, so..

user hasMany timsheets and timesheets hasMany timesheetEntries.

I need to be able to access the user from the timesheet entry. I am thinking belongToThrough but I am no so good at the advanced relationships like 'through and polyMorphic'

mstarkey

8 years ago

Best Answer

Level 31

Found the solution to this one, using hasManyThrough on the User Model:

public function timesheet_entries()
    {
        return $this->hasManyThrough(Timesheet::class, TimesheetEntry::class);
    }

Please or to participate in this conversation.