Filtering duplicated foreign key in laravel

Question

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...

update3

so a few days ago i thing i find the solution and also add it into answer section.

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

but today after i tried to add real data i find there is one big flaw in this code. like say the data upthere... what it will show is only the one with max date

id|no.BA|name|value|date
6 |44   |kkkk|67677|31/01/2016

so please anybody that have expertise in sql query... please help me.. i stuck into every question in stackoverflow that claim the solution work... but in my case it is not working the way i want...

is your question similar to this one: http://stackoverflow.com/questions/23835311/laravel-4-query-builder-groupby-max-date ? — , Jul 09 '16 at 23:49
wow... yes i think it is pretty much the same, but in my case i get data from 2 table.... one is my primary table and another one is from cuprimer table, so how do i add 'max' parameter into my belongsto? — PamanBeruang, Jul 10 '16 at 00:02
I have no experience with laravel; just wanted to point out the similar question in the hopes it would help :) — , Jul 10 '16 at 00:07
Here's a question how it to do it with plain ol' SQL http://stackoverflow.com/questions/612231/how-can-i-select-rows-with-maxcolumn-value-distinct-by-another-column-in-sql maybe you can use that to turn it into a Laravel solution — , Jul 10 '16 at 04:46
yes if it still using ol'SQL then i can do it... but i want to do it laravel way or specificly eloquent way.... — PamanBeruang, Jul 10 '16 at 04:49
I figured. Again, just hoping those answers could lead to the solution here. — , Jul 10 '16 at 04:53
Actually.... have you tried using [`having`](https://laravel.com/docs/5.1/queries#ordering-grouping-limit-and-offset)? Looks like you might be able to do, `->having('date', '=', 'MAX(date)')` after the `groupBy` — , Jul 10 '16 at 04:55

score 1 · Accepted Answer · answered Jul 10 '16 at 02:03

1

I believe that will do the work.

use App\Models\Cuprimer;
$rows = Cuprimer::groupBy('no.BA')
    ->havingRaw('COUNT(*) > 1')
    ->get();

If that doesn't work, try to remove the line with havingRaw().

answered Jul 10 '16 at 02:03

Mauro Baptista

195
4

Try include de orderBy('date', 'DESC') ($datas = Article::with('category')->orderBy('date', 'DESC')->groupby('no.BA')->get();) – Mauro Baptista Jul 13 '16 at 20:10
hmm still no luck sir, it will show the first inputed into database field for each "no.BA" but not the latest by date... still get no answer for this problem..... – PamanBeruang Jul 14 '16 at 02:38
That is just a guess, but you could try to separate the calls, like: `$all = Article::with('category')->orderBy('date', 'DESC')->get();` / `$datas = $all->groupby('no.BA');` – Mauro Baptista Jul 14 '16 at 03:47

Filtering duplicated foreign key in laravel

1 Answers1