php - how to efficiently convert Raw sql query to Eloquent

I've a raw SQL query

SELECT cc.name, pp.population
FROM population_table pp
WHERE pp.country_name IN (
    SELECT cc.name 
    FROM `countries` cc
)
GROUP BY pp.country_name

The above query gives the list of countries that are in countries table along with population value.

I'm trying to convert this query to eloquent by splitting the queries into two. I'm using this eloquent in database level.

public function getCountriesWithPopulation() {
    $countryTable = Country::$TABLE_NAME;
    $populationTable = Population::$TABLE_NAME;

    $query1 = // eloquent query
    $query2 = // eloquent query
}

How can I convert the raw query into eloquent by splitting the queries?

Edit:

SQL Query

SELECT country, region, area, pp.population
FROM population_table pp
FROM (
    SELECT cc.country, cc.region, cc.area 
    FROM `countries` cc
) 
GROUP BY country

Solution:

You don't need to run two queries. Just consider it as a straight JOIN:

SELECT cc.name, pp.population
FROM population_table AS pp
JOIN `countries` AS cc 
    ON (cc.name = pp.country_name)
GROUP BY pp.country_name

And Eloquent has a built-in support for JOIN. In this case, also, the GROUP BY is unnecessary since only one country's name per country is ever retrieved.

Actually, though, if the database is in a reasonable state (there is 1:1 correspondence between countries and population) you would not even need cc at all. The country name is, unlikely enough, available in pp (it should rather be a foreign key into countries), so as things stand, you might just do:

SELECT country_name, population
FROM population_table;

Unless you have the population table storicized. Then things are easy for a JOIN, more difficult for Eloquent, and there is the possibility of a missing census year in population:

SELECT cc.name, pp.population
FROM population_table AS pp
JOIN `countries` AS cc
    ON (
        cc.name = pp.country_name AND 
        pp.year=$selectedYear
    )
GROUP BY pp.country_name

Solution:

Basen on your query,

SELECT cc.name, pp.population
FROM population_table pp
WHERE pp.country_name IN (SELECT cc.name FROM `countries` cc) 
GROUP BY pp.country_name 

You can achieve the same result with Eloquent such as

public function getCountriesWithPopulation()
{
    // get country names as array
    $countryNames = Country::pluck('name');

    // get your result
    $result = Population::whereIn('country_name', $countryNames)->groupBy('country_name');
}

Edit:

matiaslauriti's comment was right, you can directly use pluck('name') instead of all()->pluck('name') for efficiency and performance.

Country::pluck('name') will return all name column data in the country table, so that you can use later for filtering population data using whereIn()

Population::whereIn('country_name', $countryNames)->groupBy('country_name') will return all data in population table where column country_name is in $countryNames array. The query will be grouped by country_name column so that there is no country name duplication.