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();
}