DB Query advice...

Hi. I'm falling into the same thing quite regularly where I'm building a report or needing multiple variables for calculations etc. and was wondering if there is a better way to do it? I've got a project where I have about 15 different variables to set and I'm trying to figure out how to level up my skills.

Cheers

$this->reports = ReportImport::where('status', 0)->whereYear('timestamp', $this->date_range)->get();

//        $report->setReportType('Date Created');

        $this->created =  $this->reports->where('type', 'Date Created'); // Neutral
        $this->verified_complete =  $this->reports->where('type', 'Set Status To Verified Complete'); // Positive
        $this->verified_incomplete =  $this->reports->where('type', 'Set Status To Verified Incomplete');  // Neutral
        $this->cancelled =  $this->reports->where('type', 'Set Status To Cancelled'); // Negative
        $this->approved_conditional =  $this->reports->where('type', 'Set Status To Approved Conditional'); // Positive
        $this->approved =  $this->reports->where('type', 'Set Status To Approved'); // Positive
        $this->payment_date =  $this->reports->where('type', 'Payment Date'); // Positive
        $this->approval_and_payment_email_sent =  $this->reports->where('type', 'Approval And Payment Email Sent'); // Positive

LaryAI

2 years ago

Level 58

It seems like you're trying to filter a collection of reports by different types and assign them to separate variables. This approach can become cumbersome as the number of variables grows. Instead, you can streamline your process by using Eloquent's collection methods or by refining your database queries.

Here's a more efficient way to handle this using Eloquent's collection methods:

// Fetch all reports once
$this->reports = ReportImport::where('status', 0)
    ->whereYear('timestamp', $this->date_range)
    ->get();

// Group reports by type
$this->groupedReports = $this->reports->groupBy('type');

// Now you can access the reports by type like this:
$this->created = $this->groupedReports->get('Date Created');
$this->verified_complete = $this->groupedReports->get('Set Status To Verified Complete');
// ... and so on for the other types

By using the groupBy method, you're creating a collection that's indexed by the report type, which makes it easy to access the reports you need without having to filter the collection multiple times.

If you find yourself needing to perform more complex calculations or summaries, consider using Eloquent's query builder to perform aggregate functions directly in your database query. For example, if you need to count the number of reports by type, you could do something like this:

$reportCounts = ReportImport::where('status', 0)
    ->whereYear('timestamp', $this->date_range)
    ->groupBy('type')
    ->selectRaw('type, COUNT(*) as count')
    ->pluck('count', 'type');

// Now you have an associative array with the type as the key and the count as the value

This approach minimizes the amount of data transferred from the database and can significantly improve performance for large datasets.

Remember to always consider the size of your dataset and the performance implications of your queries. For very large datasets, you might need to look into more advanced techniques like database indexing, caching, or even background processing with queues.

Tray2

2 years ago

Level 73

It is very hard to understand what you want help with, what you are trying to do, what your data looks like, and what is the expected result.

2 likes

jlrdw

2 years ago

Level 75

Perhaps have more than one report.

Just example of what I relate to, for a summary report of income and expenses I usually do one report.

However for a details report of income and expenses, I do separate reports.

Just a suggestion.

Please or to participate in this conversation.