How to Select one address for each different country in Laravel Eloquent

Question

I have a DB in which I have several addresses. I need to get one row for each different country in the DB. For example if I have | id | name | column | country | | -- | -------------- | ------ | ------- | | 1 | Main address | 10 | UK | | 2 | Second address | 3 | France | | 3 | Third address | 78 | USA | | 4 | Fourth address | 46 | France | | 5 | Fifth address | 44 | France | | 6 | Sixth address | 11 | UK | I need to retrieve only 3 rows: 1 for UK, 1 for France and 1 for USA. The row I get for each country is not important but I need the full row. (For UK id 1 or 6 does not matter but I need all columns). How can I do that in Laravel (preferably using Eloquent).

I cannot use group by because all columns have different values...

score 0 · Answer 1 · answered Mar 05 '21 at 15:25

Depending on how big your data set is (this will get inefficient if very large) you can do something like this...

//Select and get the countries column.  
$countries = DB::table('addresses')->select('country')->get();

//Get the unique values of countries from the collection
$uniqueCountries = $countries->unique();

//Array which will contain a single address from each country.
$singleAddresses = [];

//Loop over each unique country you retrieved from the database.
foreach($uniqueCountries as $country){
    //Grab the first address to occur for whatever country is currently being iterated over in the loop.
    $singleAddress = DB::table('addresses')->where('country','=',$country)->first();
    
    //Push the first address found for the country into the array of single addresses from various countries.
    array_push($singleAddresses,$singleAddress);
}

//Dump the array of collection to the page to make sure the output is what you want.
dump($singleAddresses);

Basically you'll want to grab only the countries column from your database through the select statement. This data is then returned to you as a collection. On that collection you can then use the unique method to get all the unique instances of country names.

Once you have your unique list of country names you can move onto grabbing an address for each country. This is done by looping over the unique set of countries one at a time. Making a database request to only select addresses where that county is, and then grab the first instance of an address within that country.

Once you have the object of that address data you'll want to push it into an array containing all the other single addresses from the countries you've retrieved.

Once the loop completes you'll have an array of Laravel collection objects with a single address from each country in your database.

Some other notes...

If you need the address to be random by country you'll want to modify your selection statement to use ->get() for all addresses from a country. Then you'll call on that data set using the collection random method to grab a random address from that country.
If your data set for these addresses and countries is extremely large you'll want to look into using the caching systems built into Laravel or lean on curated lists of data like this list of countries to help do some of the heavy listing.
I just pushed all of the address collections into an array because it was quick and easy. However Laravel does support making empty collections, which you can then use other collection methods on to manipulate your data. In my experience I've found advantages and tradeoffs to both so take that for what you will.

score 0 · Accepted Answer · answered Mar 12 '21 at 10:35

I managed to get what I needed using ANY_VALUE to avoid the problem of the different values and AS to be able to use the fields easily.

$addresses = Address::select(DB::raw('ANY_VALUE(name) AS name, ANY_VALUE(column) AS column'), 'country')->groupBy('country')->distinct()->get();

It gives me an array with a single row for each country.

How to Select one address for each different country in Laravel Eloquent

2 Answers2