Be part of JetBrains PHPverse 2026 on June 9 – a free online event bringing PHP devs worldwide together.

olimorris's avatar

Laravel 5.1 - Merge multiple Eloquent queries and sort by timestamp

I have three tables, a Users table, a Jobs table and a Bids table. Users can post multiple Jobs and Users can post a bid on Jobs which are not their's.

*My Goal: I'm trying to query the Jobs table to find all of a users jobs and query the Bids table to get all of the jobs that a user is bidding on. Then I would like to merge the Job IDs and the Bid IDs and sort based on timestamps

The tables are setup as follows:

    | USERS    | JOBS    | BIDS
    | id       | id      | id
    | username | user_id | user_id
    | password | title   | job_id
    |          |         | bid_amount

The relationships between the tables are as follows:

Jobs:

    public function bids()
    {
        return $this->hasMany('App\RocketCandy\Repos\Bids\Bid');
    }

    public function user()
    {
        return $this->belongsTo('App\RocketCandy\Repos\Users\User');
    }

Users:

    public function jobs()
    {
        return $this->hasMany('App\RocketCandy\Repos\Jobs\Job');
    }

    public function bids()
    {
        return $this->hasMany('App\RocketCandy\Repos\Bids\Bid');
    }

Bids:

    public function jobs()
    {
        return $this->belongsTo('App\RocketCandy\Repos\Jobs\Job');
    }

    public function users()
    {
        return $this->belongsTo('App\RocketCandy\Repos\Users\User');
    }

The following code I've developed returns the jobs and the bids but it's not clever enough to sort the IDs based on the updated_at timestamp.

    public function getUsersJobs($userId)
    {
        // Get the jobs that the user posted
        $postedJobs = Job::where('user_id', $userId)
                           ->lists('id')
                           ->toArray();

        // Get the jobs that the user is bidding on
        $biddedJobs = Job::with('bids')
                           ->whereHas('bids', function ($q) use ($userId) {
                               $q->where('user_id', $userId);
                           })->lists('id')
                             ->toArray();
    
        $jobIds = array_merge($postedJobs, $biddedJobs);
    
        // Return the jobs that a user has posted and is bidding on
        return Job::whereIn('id', $jobIds)
                           ->OrderBy('updated_at', 'DESC')
                           ->get();
    }
0 likes
5 replies
EliasSoares's avatar

You you don't do only:

return Job::where('user_id', $user_id)->orWhereHas('bids', function ($q) use ($userId) {
                               $q->where('user_id', $userId);
                           })->orderBy('updated_at', 'DESC')->get();

This should solves your problem.

olimorris's avatar

@EliasSoares - Unfortunately that sorts all jobs by 'updated_at'. I want to sort the users jobs by updated_at and the bids by updated_at

martinbean's avatar

@olimorris Can you not just query through the Bids model since that has both the user ID and job ID?

1 like

Please or to participate in this conversation.