Jan 29, 2021

Level 1

Query Builder - Optimizing the repeated portion of the 3 queries below?

Hi all, I have to run 3 queries, which shares a similar 'base of the query' and only differs in the final few whereBetween condition, is there a way that I can 'group up' the earlier similar parts of the query, so that I can reuse it, and potentially reduce mistakes along the way, as I'll only need to change the 'base of the query once' and have it applied to the subsequent queries.

Original 3 queries

	$totalTenders = DB::table('tenders')
    ->select('Tender_Result', DB::raw('count(*) as total'))
    ->groupBy('Tender_Result')
    ->whereBetween('RegisteredDate', [$from, $to])
    ->get();

    $totalTendersM1 = DB::table('tenders')
    ->select('Tender_Result', DB::raw('count(*) as total'))
    ->groupBy('Tender_Result')
    ->whereBetween('RegisteredDate', [$minusOneYearFrom, $minusOneYearTo])
    ->get();


    $totalTendersM2 = DB::table('tenders')
    ->select('Tender_Result', DB::raw('count(*) as total'))
    ->groupBy('Tender_Result')
    ->whereBetween('RegisteredDate', [$minusTwoYearsFrom, $minusTwoYearsTo])
    ->get();

Is there a way for me to optimize the code, so that if I want to change the part that's similar amongst all 3 queries, I can just do it one time, and have all 3 queries updated?

A rough idea of what I wanted to achieve

	$baseQuery = DB::table('tenders')->select('Tender_Result', DB::raw('count(*) as total'))
    ->groupBy('Tender_Result');
	$totalTenders = $baseQuery
    ->whereBetween('RegisteredDate', [$from, $to])
    ->get();
	$totalTendersM1= $baseQuery
    ->whereBetween('RegisteredDate', [$minusOneYearFrom, $minusOneYearTo])
    ->get();
	$totalTendersM2 = $baseQuery
    ->whereBetween('RegisteredDate', [$minusTwoYearsFrom, $minusTwoYearsTo])
    ->get();

Tq all

Tray2

5 years ago

Level 73

Something like this might work. I haven't tested it

$query = DB::table('tenders')
    ->select('Tender_Result', DB::raw('count(*) as total'))
    ->groupBy('Tender_Result');

if (some_condition) {
  $query->whereBetween('RegisteredDate', [$from, $to])

} else if (some_other_condtion) {
  $query->whereBetween('RegisteredDate', [$minusOneYearFrom, $minusOneYearTo]);
} else {
  $query->whereBetween('RegisteredDate', [$minusTwoYearsFrom, $minusTwoYearsTo]);  
}

$result = $query->get();

CorvS

5 years ago

Level 27

@ttsb Basically what you already did or just create a function inside your controller (or wherever):

private function foo($from, $to)
{
    return DB::table('tenders')
        ->select('Tender_Result', DB::raw('count(*) as total'))
        ->groupBy('Tender_Result')
        ->whereBetween('RegisteredDate', [$from, $to])
        ->get();
}

...and simply use it:

$totalTenders = $this->foo($from, $to);
$totalTendersM1 = $this->foo($minusOneYearFrom, $minusOneYearTo);
$totalTendersM2 = $this->foo($minusTwoYearsFrom, $minusTwoYearsTo);

TTSB

5 years ago

Level 1

Hi @nimrod , i will try the function way

@tray2 this will not work, as I need to run the 3 conditions continuously. Initially I started off with something similar like what you mentioned.

    $baseQuery = DB::table('tenders')
    ->select('Tender_Result', DB::raw('count(*) as total'))
    ->groupBy('Tender_Result');
    
    $totalTenders = $baseQuery
    ->whereBetween('RegisteredDate', [$from, $to])
    ->get();

    $totalTendersM1 = $baseQuery
    ->whereBetween('RegisteredDate', [$minusOneYearFrom, $minusOneYearTo])
    ->get();


    $totalTendersM2 = $baseQuery
    ->whereBetween('RegisteredDate', [$minusTwoYearsFrom, $minusTwoYearsTo])
    ->get();

However, after $totalTenders is executed, it modifies the '$baseQuery' so when I run $totalTendersM1, it's executing the '$baseQuery that is already modified by the previous $totalTenders'

My intention was to have $totalTendersM1 running off the original $baseQuery, I was thinking $totalTenders, $totalTendersM1, and $totalTendersM2 all will spawn a new instance of $baseQuery, but apparently it's not.

TTSB

5 years ago

Level 1

@nimrod setting it to a function works!

Thanks~

TTSB

5 years ago

Level 1

In the previous example, the query is the same, only the parameters is different, so I can use a function for it.

How can I achieve the same, if the base of the query is the same, but different get() will need different set of where conditions? How do I reuse the 'base of the query' in such a case?

Function won't work due to the differences in orWhere clauses

    $securedContractSum = DB::table('project_registry')
    ->select(DB::raw("count(Project_Code) as totalJobs, round(sum(contract_original_value)/1000,2) as monthlySecuredSum, date_format(Project_Date_Prepared, '%Y-%m') as YearMonth"))
    ->groupBy('YearMonth')
    ->whereBetween('Project_Date_Prepared', [$from, $to])
    ->where(function($query) {
        $query->where('project_code', 'like', '%1')
        ->orWhere('project_code', 'like', '%2')
        ->orWhere('project_code', 'like', '%3')
        ->orWhere('project_code', 'like', '%4')
        ->orWhere('project_code', 'like', '%5')
        ->orWhere('project_code', 'like', '%6')
        ->orWhere('project_code', 'like', '%7')
        ->orWhere('project_code', 'like', '%8')
        ->orWhere('project_code', 'like', '%9')
        ->orWhere('project_code', 'like', '%0');
    })
    ->where(function($query) {
        $query->where('project_status', '<>', 'cancelled');
    });


    $securedContractSumX = $securedContractSum
    ->where(function($query) {
        $query->where('project_code', 'like', 't%');
    })
    ->where(function($query) {
        $query->where('project_type', 'like', '%X%')
        ->orWhere('project_type', 'like', 'Y%')
        ->orWhere('project_type', 'like', '%Z%')
        ->orWhere('project_type', 'like', '%A%');
    })
    ->where(function($query) {
        $query->where('project_client', 'like', '%B%');
    })
    ->get();

    $securedContractSumTNBGrid = $securedContractSum
    ->where(function($query) {
        $query->where('project_code', 'like', 't%');
    })
    ->where(function($query) {
        $query->where('project_type', 'like', '%ZX%')
        ->orWhere('project_type', 'like', '%XXX%')
        ->orWhere('project_type', 'like', '%YYY%')
        ->orWhere('project_type', 'like', '%DDD%')
        ->orWhere('project_type', 'like', '%BBB%')
        ->orWhere('project_type', 'like', '%AAA%');
    })
    ->where(function($query) {
        $query->where('project_client', 'like', '%SSS%');
    })
    ->get();

Tray2

5 years ago

Best Answer

Level 73

When queries starting to get complex like this I almost always turn to pure SQL since it's imho much easier to read than a tricky query builder construction.

And if it has a common base I might even push that part into a database view that I can run a much simpler Eloquent query against.

1 like

TTSB

5 years ago

Level 1

That's correct too, if I go to pure SQL, i can possibly put the 'base part' of the SQL into a variable and it'll still works.

Never thought about it from that perspective... pure sql would trump query builder in my current case...

Thanks! I guess I was so fixated (tunnel vision) at getting it to work the 'laravel way'

MichalOravec

5 years ago

Level 75

@ttsb Why did you remark the best reply? It has nothing to do with the question in OP...

TTSB

5 years ago

Level 1

Mmmm, the OP kinda morphed into the 2nd question, which best solved my question at the latest moment.

TTSB

5 years ago

Level 1

I ended up with the following

    $baseString = "select `X`, `Y` from table where Z = 'ZZ' and purchaseDate between :from and :to ";
    $where = "and (ZZZ like 'gg%') ";
	$where .= "and (YYY like 'xx%')";
    $results = DB::select( DB::raw($baseString.$where), array(
        'from' => $from,'to' => $to,
      ));

This allows me to reuse the similar (base) part of the query, and I can then just .= additional where as necessary

Hope it'll help others too.

Thanks again

Please or to participate in this conversation.