CODEWITHSUNDEEP
phplaraveljoin
Andy Karczewski
Andy Karczewski

Reputation: 55

Laravel Join Not Returning Specified Column

I have a table jobs. A job may have 1 or more rows in jobs_days

I want to return a list of jobs that have at least 1 'job day'. So I have this is my Jobs Model:

public function days(){
    return $this->hasMany('App\JobsDays', 'job_id', 'id');
}

Which works fine. However I need to have a list of jobs that have at least 1 job day, but sort this list by this job start date: start2(timestamp) column. I have tried using:

$jobs = Jobs::has('days')
            ->leftJoin('jobs_days', function ($join) {
                $join->where('jobs_days.job_id', '=', 'jobs.id');
            })
            ->select('jobs.id','jobs_days.start2')
            ->orderBy('jobs_days.start2', 'asc')->paginate(15);

This won't return my start2 date. If I use on in place of where, I get my start2 column, but it now returns duplicate jobs.

Upvotes: 1

Views: 59

Answers (1)

Andy Karczewski
Andy Karczewski

Reputation: 55

After many hours, I found the solution. Using on in the join, I added a simple:

->groupBy('jobs.id')

Gives me distinct job.ids. Not sure why distinct doesn't work but this seems to do the job

Upvotes: 1

Related Questions