php - how to efficiently convert Raw sql query to Eloquent

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
Copy code

And Eloquent has a built-in support for JOIN. In this case, also, theGROUP BY is unnecessary since only one country'sname percountry is ever retrieved.

Actually, though, if the database is in a reasonable state (there is 1:1 correspondence betweencountries andpopulation) you would not even needcc at all. The countryname is, unlikely enough, available inpp (it should rather be a foreign key intocountries), so as things stand, you might just do:

SELECT country_name, population
FROM population_table;
Copy code

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

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
Copy code

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 
Copy code

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');
}
Copy code

Edit:

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

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

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