CODEWITHSUNDEEP
phpmysqllaraveleloquent
Oliver Kucharzewski
Oliver Kucharzewski

Reputation: 2655

Get distinct columns and return filtered result

Currently i'm looking to display a few holiday categories on the home-page of a website.

In-order to create these categories, I will need to pull a unique set of countries out of the existing holidays.

For example; one holiday may go to Mexico, another 3 may go to Thailand, another may go India.

Notice the 3 holidays going to Thailand, with a basic pull they are all returned; but I only need one of them in-order to create the category. So they must be distinct.

Here's my current code.

 public function getHolidays($mode = "all", $limit = 1000){
    $holidays = Holiday::whereHas('dates', function(Builder $q){
        $q->where('start_sydney', '>=', date('Y-m-d'));
    });
    switch($mode){
        case "home":
            $holidays = $holidays->whereHas('info', function($q) {

            });
        break;
    }
    $holidays = $holidays
        ->with('dates')
        ->with('pricing')
        ->with('info')
        ->with('images')
        ->with('images.image_info')
        ->limit($limit)->get();
    return json_encode($holidays);
}

In the home case, I tried to have $q->distinct('country') but it didn't work.

The country column in the 'info' model needs to be used but i'm not sure how to make it unique.

Could someone assist me in returning a unique set of records based on their 'country' column in the info model?

Upvotes: 1

Views: 58

Answers (2)

Giovanni S
Giovanni S

Reputation: 2110

One thing that is often mistaken with Laravel query builder / eloquent is distinct. You can't pass anything to distinct because it doesn't take any parameters.

That said, I do not know exactly how you have your models set up, but assuming they are somewhat standard, you could try essentially what you are attempting, but in reverse:

Info::select('country')->has('holiday')->distinct()->pluck('country');

Which should return what you are after.

I used pluck here because it would return a nice, simple, ready to use Collection of country names for you to use on the front end.

There are a few ways to do what you want, this is one of them. Hope it helps!

More about distinct from the docs:

The distinct method allows you to force the query to return distinct results:

$users = DB::table('users')->distinct()->get();

Upvotes: 1

Poldo
Poldo

Reputation: 1932

Try doing it with join and using groupby

switch($mode){
        case "home":
            $holidays = $holidays->join('info', 'info.id', '=', 'holidays.id')->groupBy('info.country');
        break;
    }

Upvotes: 1

Related Questions