How to get result from two combined tables

Question

i have two tables:

Matches:

id   round league_id   home_team_id   away_team_id   match_date

Scores:

id  match_id    home_team_score   away_team_score    created_at  updated_at

How to get latest team match, which have record in table 'scores', for exmpl:

need to get last match where team_id = 1

with this i get all matches of this team, but i need only the last,and where has record in scores table:

$lastMatch = Match::where('away_team_id', '=', '1')
        ->orWhere('home_team_id', '=', '1')
        ->get();

Answer 1

To get latest record from some table, you should be able to do something like this:

Model::latest()->first();

or

Model::latest('created_at', 'desc'))->first();

or

Model::orderBy('created_at')->first();

To get the result of two tables combined, you can join two tables, and then add orderBy or latest on the table and column you want.

So for the instance, in your case, that would be something like this:

Match::join('scores', 'matches.id', '=', 'scores.match_id')
       ->where('away_team_id', '=', '1')
       ->orWhere('home_team_id', '=', '1')
       ->latest('scores.created_at')->first()