Union of one eloquent group by several different columns

Question

I have query for analytic from one table. That table has lots of columns, i'll show only columns which are important

table oc_za_puni_uredjaj
sif_company  (integer - primary, value for company)
sif_device   (integer - primary, value for device)
sif_nos      (integer - primary, value for customer)
sif_mp       (integer - primary, value for customer's place)
rbr          (integer - primary, value for customer's place item)
ocitano      (integer - 0 - empty, 1 - full)
datum        (date    - date of the record)
area         (text    - name of area of customer)
....

My existing query is

$data = DB::table('oc_za_prazni_uredjaj')->select(DB::raw('area as naselje, count(*) total, sum(case when ocitano = 1 then 1 else 0 end) ocitano'))
            ->where('sif_company',$request->sif_company)
            ->groupBy('area')->get();

And that gives me proper data if i got for each customer/customer's place and his item for just one device (it's like i have only 1 sif_device in whole table).

For example

sif_company | sif_device | sifnos | sif_mp | rbr | ocitano | datum | area |
---------------------------------------------------------------------------
1           |     1      | 1      | 1      | 1   | 1       |...    |abcd
1           |     1      | 1      | 2      | 1   | 0       |...    |abcd
1           |     1      | 2      | 1      | 1   | 1       |...    |abc
1           |     1      | 1      | 2      | 1   | 1       |...    |abcd
1           |     1      | 1      | 2      | 3   | 1       |...    |abcd

Output data is like (which is fine for only 1 device)

naselje    |  total    |    ocitano
------------------------------------
abc        |  1        |     1
abcd       |  4        |     3 

The problem is if i have same data for 2 different devices. I could have data in table like below

sif_company | sif_device | sifnos | sif_mp | rbr | ocitano | datum | area |
---------------------------------------------------------------------------
1           |     1      | 1      | 1      | 1   | 1       |...    |abcd
1           |     2      | 1      | 1      | 1   | 0       |...    |abcd
1           |     1      | 2      | 1      | 1   | 1       |...    |abcd
1           |     2      | 2      | 1      | 1   | 1       |...    |abcd
1           |     1      | 3      | 1      | 1   | 1       |...    |abc

My desired output for that data should look like

naselje    |  total    |    ocitano
------------------------------------
abc        |  1        |     1
abcd       |  2        |     2 

So if i have in table row with (sif_nos, sif_mp, rbr) for different device (sif_device) then i have cases:

• if only one of them has ocitano = 1 then for that area i have to increment ocitano in output by 1 and take row with ocitano value = 1 (for later work)
• if there is several rows with ocitano = 1 then i have to increment ocitano in output by 1 and take the row with newest date (column datum) for later work
• if all of them got ocitano = 0 then i don't increment ocitano in output

Any help would be nice

EDIT

So far i have successfully get array of data on which i would like to run my query from beggining of the question. The function is

 public function testFunction(Request $request){
    $sif_tvrtka = $request->sif_tvrtka;
    $podaci = za_prazni_uredjaj::where('sif_company',$sif_tvrtka)->get();
    $indeks = 0;
    $izlaz = new Collection();
    //tried with array also
    //$izlaz = [];
    foreach ($podaci as $podatak){
        $exists = false;
        foreach ($izlaz as $izl){
            if($izl->sifnos == $podatak->sifnos && $izl->sifkup == $podatak->sifkup && $izl->redbrprik == $podatak->redbrprik){
                $exists = true;
                break;
            }
        }
        if ($exists == true){
            foreach ($izlaz as &$izl){
                if($izl->sifnos == $podatak->sifnos && $izl->sifkup == $podatak->sifkup && $izl->redbrprik == $podatak->redbrprik) {
                    if ($izl->ocitano < $podatak->ocitano) {
                        $izl = $podatak;
                    } else {
                        if ($izl->datumNovogOcitanja < $podatak->datumNovogOcitanja)
                        {
                            $izl = $podatak;
                        }
                    }
                }
            }
        }
        else{
            $izlaz[$indeks++] = $podatak;
        }

    }
    dd($izlaz);
}

But i have 1 little problem. My data in $izlaz is not collection so i can't use query builder for my query

$data = DB::table('oc_za_prazni_uredjaj')->select(DB::raw('area as naselje, count(*) total, sum(case when ocitano = 1 then 1 else 0 end) ocitano'))
            ->where('sif_company',$request->sif_company)
            ->groupBy('area')->get();

Is there any better way to achieve this?

Answer 1

See this edit, you'll have to tweak it, but get the idea.

Would that give the desired result?

//one of them has ocitano = 1
$data1 = DB::table('oc_za_prazni_uredjaj')->select(DB::raw('area as naselje, count(*) total, sum(case when ocitano = 1 then 1 else 0 end) ocitano'))
            ->where('sif_company',$request->sif_company)
            ->having('sum(ocitano)', 1)
            ->groupBy('area');
    // several rows with ocitano = 1
        $data2 = DB::table('oc_za_prazni_uredjaj')->select(DB::raw('area as naselje, count(*) total, sum(case when ocitano = 1 then 1 else 0 end) ocitano, sum(ocitano) ocitano_max, max(datum) datum'))
                    ->where('sif_company',$request->sif_company)
                    ->having('ocitano_max', '>', 1)
                    ->groupBy('area');
//ocitano = 0
        $data0 = DB::table('oc_za_prazni_uredjaj')->select(DB::raw('area as naselje, count(*) total, max(ocitano) ocitano, max(datum) datum'))
                    ->where('sif_company',$request->sif_company)
                    ->where('ocitano', 0)
                    ->groupBy('area', 'sif_device');

---

$data = $data1->unionAll($data0)->unionAll($data2);
$data = $data->get();