Laravel Join Not Returning Specified Column

Question

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.

George Cummins · Accepted Answer · 2016-04-25 16:41:55Z

1

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

edited Apr 25, 2016 at 16:41

George Cummins

29k8 gold badges74 silver badges91 bronze badges

answered Apr 25, 2016 at 16:38

Andy Karczewski

556 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Laravel Join Not Returning Specified Column

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related