Laurensius Tony Laurensius Tony - 1 year ago 78
PHP Question

Filtering duplicated foreign key in laravel

I have this kind of data

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

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?


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'))
//->orderBy('paper_update', 'desc')

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

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 Source

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

$datas = PerkembanganCU::with('cuprimer')
                ->whereRaw('date= (select max(`date`) from perkembangancu)')

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

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download