Laravel. Exporting results from database problems-CodePudding

As short as possible. My code runs through multiple databases counts objects and matches name - number of objects It runs like a script(command in laravel) that exports the results in .csv file.

$formatted_data = array();
        $providers = provider::where('del', 'no')->get();       
        foreach($providers as $provider){   
            $formatted_data[$provider['id']]['name'] = $provider['name'];
        }
        
        $objectMappingsModels = array((new objectMapping1), (new objectMapping2),
         (new objectMapping3), (new objectMapping4), (new objectMapping5),
         (new objectMapping6), (new objectMapping7), (new objectMapping8));
        
         foreach($objectMappingsModels as $objectMappingsModel){
            $totals = $objectMappingsModel::select('providerFK', DB::raw('count(*) as total'), 
                                                                 DB::raw('monthName(ut) as month_name')
                                                  )
                                          ->where('userFK', '!=', 1)  
                                          ->where('del', 'no')
                                          ->whereMonth('ut', $this->option('month'))   
                                          ->whereYear('ut', $this->option('year'))
                                          ->groupBy('providerFK', 'month_name')
                                          ->get()
                                          ->toArray();              
                                          
            foreach($totals as $total){
                $formatted_data[$total['providerFK']]['count'] = $total['total'];
            }   
   
        }
        $responce =  Excel::store(new UsersExport($formatted_data), 'testNameDate.csv',null, \Maatwebsite\Excel\Excel::CSV);
        return true;

That's what my code looks like.

class UsersExport implements FromArray
{
    protected $invoices;

    public function __construct(array $invoices)
    {
        $this->invoices = $invoices;
    }

    public function array(): array
    {
        return $this->invoices;
    }
}

And that's what my export class looks like.

Unfortunately, it's not working flawlessly. It gives me wrong results from time to time. Some records a correct and some are not. Also, the last row is always with just a random number(without a name attached to it). Any idea why such problems occur and can you suggest me some code optimizations?

Thanks!

CodePudding user response：

Fix and some optimizations in code: As far as i understand you have multiple tables in possible multiple database that's not a problem if one is not correct so fix shall work if you write code accordingly.

Step 1: Mention connections and tables in Models as:

protected $connection = 'mysql2';
protected $table = 'tableNames';

Step 2: add objects to your array or collection (laravel) and same time search through chaining each object:

$object = array(ObjectMapping1::where('del', 'no')->where('userFK', '!=', 1)->get(), ObjectMapping12::where('del', 'no')->where('userFK', '!=', 1)->get(), ObjectMapping3::where('del', 'no')->where('userFK', '!=', 1)->get();

Dates can be filtered as:

->whereDay('created_at', '=', date('d'));
->whereMonth('created_at', '=', date('m'));
->whereYear('created_at', '=', date('Y'));
->whereTime('created_at', '=', date('H:m:s'));

Note: Objects will be coming into collection so you can change it to array by adding ->toArray() for later use.

Step 3: Format data accordingly with foreach $object and Then export these into your Excel::store package.

Note: Since you need total as well you can go for $total = $object[0]->count() this will return a total.

CodePudding user response：

I added this if-else in the last loop

if(isset($formatted_data[$total['providerFK']]['count'])){
                    $formatted_data[$total['providerFK']]['count']  = $total['total'];
                }else{
                    $formatted_data[$total['providerFK']]['count'] = $total['total'];
                }

And it seems to have fixed some issues