Laravel Inner Join?

Question

I have a PHP backend that I'm currently changing over to use the Laravel framework. However, I'm not quite sure how the Laravel inner join works.

The SQL query i'm trying to move over to Laravel is:

"SELECT leagues.league_name FROM leagues INNER JOIN countries on leagues.country_id = countries.country_id WHERE countries.country_name = '$country' " 

Would someone be able to explain how Laravel Joins work?

Apologies if this question isn't suitable.

Answer 1

It should be something like this (haven't tested):

$leagues = DB::table('leagues')     ->select('league_name')     ->join('countries', 'countries.country_id', '=', 'leagues.country_id')     ->where('countries.country_name', $country)     ->get(); 

$leagues will be instance of the Illuminate\Support\Collection object, so you can iterate over it using foreach for example.

You can pass 5th argument to the join() function which will specify type of join (default is "inner").

If you're using Eloquent and have "League" model then you can use join on the model too:

$leagues = League::select('league_name')     ->join('countries', 'countries.country_id', '=', 'leagues.country_id')     ->where('countries.country_name', $country)     ->get(); 

In this case, $leagues will be an instance of Illuminate\Database\Eloquent\Collection which extends regular Laravel collections and gives you a bit more functionality than regular collections.

However, there is even an easier way to write this without using joins:

$leagues = League::select('league_name')->whereHas('countries', function($query) use ($country) {     $query->where('country_name', $country); })->get(); 

Note that in this example "countries" is not a table name but Eloquent relationship name, so you need to set up your relationships before using this approach.

Also, instead of using join, this example will use two queries, or a nested query, I'm not sure; but something like this: SELECT league_name FROM leagues WHERE country_id IN (SELECT id FROM countries WHERE country_name='$country')