Laurensius Tony Laurensius Tony - 4 months ago 13
PHP Question

Filtering duplicated foreign key in laravel

I have this kind of data

id|no.BA|name|value|date
1 |22 |test|11111|30/11/2015
2 |22 |test|11144|31/12/2015
3 |34 |tttt|24455|31/12/2015
4 |44 |kkkk|33332|30/11/2015
5 |44 |kkkk|44433|31/12/2015
6 |44 |kkkk|67677|31/01/2016


no.BA is foreign key and id is my primary key, and in model I just use this belongsTo

public function cuprimer(){
return $this->belongsTo('App\Models\Cuprimer','cu','id');
}


While in my controller

$datas = PerkembanganCU::with('cuprimer')->orderBy('cu','asc')->get();


but what I intend to show is

id|no.BA|name|value|date
2 |22 |test|11144|31/12/2015
3 |34 |tttt|24455|31/12/2015
6 |44 |kkkk|67677|31/01/2016


So I don't need duplicated of no.BA 22 and 44 and only show the latest one according to date.

How to do that?

update:

Thanks to terminus for pointing to similar question
in here but how to do it in eloquent way? since the solution using

$rows = DB::table('papers')
->select(DB::raw('id, max(paper_update) as year,user_id'))
->groupBy('user_id')
//->orderBy('paper_update', 'desc')
->get();


is there any eloquent way in laravel to improve belogsTo with max date?

update2:
So after doing some research I get until this point

$datas = Article::with('category')->groupby('no.BA')-get();


and it will only show each one of no.BA only one, but it's not complete yet.. since it only show the very first of each no.BA that i ever input/saved into database... i still need to specify that only show the latest or in this case the maximum of date...

So I almost there but not quite there yet...

Answer

okay, after so many day asking around and experimenting code, i finally get the best way to make it works...

$datas = PerkembanganCU::with('cuprimer')
                ->orderBy('date','desc')->groupby('cu')
                ->whereRaw('date= (select max(`date`) from perkembangancu)')
                ->get();

many thanks to other who willing to help and contribute some workaround.... :D

Comments