Saturday, July 20, 2024

Multi column searching

 scopeSearch

collect(explode(' ', $terms))->filter()->each(function ($tiem)use ($query) {

    $term = '%' . $term . '%';

    $query->where(function($query) use ($term) {

        $query->where('first_name', 'like', $term)

            ->orWhere('last_name', 'like', $term)

            ->orWhereHas('company', function($query) use ($term) {

                $query->where('name', like', $term);

            });

    });

});

to SQL it is



SELECT * FROM 'users'

WHERE 'first_name' LIKE '%bill%'

    OR 'last_name' LIKE '%bill%'

    OR EXISTS (

        SELECT * FROM 'companies'

        WHERE 'users'.'company_id' = 'companies'.'id'

    )

Optimizing Circular Relationships

 model/Comment.php

public function isAuthor() {// n + 1 issue

     return $this-->feature->comments->first()->user_id === $this->user_id;

}

Improves

in controllor

$feature->load('comments.user', 'comments.feature.comments');


Improves more

$feature->load('comments.user');

$feature->comments->each->setRelation('feature', $feature); 

Calculate totals using conditional aggregates

select 
    count(case when status = 'Requested' then 1 end) as requested,
    count(case when status = 'Planned' then 1 end) as planned,
    count(case when status = 'Completed' then 1 end) and completed,
from features

on laravel

$status = Feature::toBase() // toBase we don't want to the Feature model return back, but instead we want collection of different totals
    ->selectRaw("count(case when status = 'Requested' then 1 end) as requested")
    ->selectRaw("count(case when status = 'Planned' then 1 end) as planned")
    ->selectRaw("count(case when status = 'Completed' then 1 end) as completed")
    ->first();

we also can improve to // filter will allow you to use where statement
$status = Feature::toBase()
    ->selectRaw("count(*) filter (where status = 'Requested') as requested")
    ->selectRaw(("count(*) filter (where status = 'Planned') as planned")
    ->selectRaw(("count(*) filter (where status = 'Completed') as completed")
    ->first();