3

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?

KuKeC
  • 4,392
  • 5
  • 31
  • 60

1 Answers1

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();
Cristo
  • 700
  • 1
  • 8
  • 20
  • add `max(date)` on the select for newer or like that – Cristo Jun 07 '16 at 14:53
  • no, it accumulate it like my query in question. I need to check 3 cases like i wrote in question. – KuKeC Jun 07 '16 at 15:12
  • Have you tried the code, just in case? Or you need to show the ocitano=0 rows also? Other than that have a look at: http://stackoverflow.com/questions/25924592/laravel-multiple-unions. Inthe worst case you can get the query and do the calculation on php – Cristo Jun 07 '16 at 17:32
  • I want to avoid calculation, want to use just query if possible. That's my point. I know how to do it with multiple queries and calculation with arrays, but i woul like to know if this could be done in a single query, – KuKeC Jun 07 '16 at 18:56
  • Not sure but I'd think that until you don't do the ->get the $query won't execute the sql so it would maybe do one, just at the line of the get(). Thus you could type those 3 in a line but would be so ugly to read. Could you check setting a listen or dumping the query string and tell me the results :D? https://laravel.com/docs/5.2/database#listening-for-query-events – Cristo Jun 07 '16 at 19:37