addSelect confusion

@Nakov the exact same examples in your shared article are referenced in the Laravel official documentation as well: https://laravel.com/docs/9.x/eloquent#subquery-selects what i do not understand is the examples themselves. from sql perspective i can feel a "join" operation is being performed by this functionality but it's still so vague for me to understand how it's actually working

@arashb a subquery is different than a join, because with subquery you can select just single result from a related table, with join however it can return more.

I am not sure how else to explain it, because the example given in the article is really good.

So unless you share a code, and why you are considering the addSelect I am not sure I can help you any better.

@Nakov i have developed a complex search functionality for a site in which one can filter the job positions based on multiple factors such as location, specialisation and etc. how i did it is by doing:

1. in AppServiceProvider boot method:

    Builder::macro('whereLike', function ($attributes, string $searchTerm) {
        $this->where(function (Builder $query) use ($attributes, $searchTerm) {
            foreach (Arr::wrap($attributes) as $attribute) {
                $query->when(
                    str_contains($attribute, '.'),
                    function (Builder $query) use ($attribute, $searchTerm) {
                        [$relationName, $relationAttribute] = explode('.', $attribute);
   
                        $query->orWhereHas($relationName, function (Builder $query) use ($relationAttribute, $searchTerm) {
                            $query->where($relationAttribute, 'LIKE', "%{$searchTerm}%");
                        });
                    },
                    function (Builder $query) use ($attribute, $searchTerm) {
                        $query->orWhere($attribute, 'LIKE', "%{$searchTerm}%");
                    }
                );
            }
        });
   
        return $this;
    });
   

2. in the related controller:

    $this->specialisations = Specialisation::all();
    $data_jobtypes = Job::select('type')->distinct()->get();
    $data_joblocations = Job::select('location')->distinct()->get();
    return view('livewire.job-datatable',[
        'jobs' => Job::whereLike('title',$this->search ?? '')
        ->when($this->location, function($query,$location){
            return $query->where('location',$location);
        })
        ->when($this->jobtypes, function($query, $jobtypes){
            return $query->whereIn('type',$jobtypes);
        })
        ->when($this->roles, function($query, $roles){
            $selected_roles = array_keys($roles,true);
   
            if(empty($selected_roles))
                return $query;
   
                //dd($query);
            return $query->whereHas('specialisation',function($query) use($selected_roles){
                //dd($query);
                return $query->whereIn('id',$selected_roles);
             });
        })
        ->when($this->remote, function($query, $remote){
            return $query->where('is_remote',$remote);
        })
        ->paginate(5)
   

it works but i get the feeling that it's unnecessary complex and can be implemented better. i was looking for some remedies inside the documentation and i came across addSelect functionality hence i was curios to find out if its applicable for such scenario or not. i can not understand the documentation about the this functionality to verify if it's what i am after or not

@arashb I don't think that a subquery will help you with your case.

The subquery is used for returning a single value out of related table based on a condition, as the example in the article - latest login time out of many logins or latest flight out of many flights to the destination as in the documentation. Using "Like" can and should be possible to return multiple results.

@vincent15000 in this simplistic form yes it's quite easy to understand. what i struggle to understand is it's usage explained here: https://laravel.com/docs/9.x/eloquent#subquery-selects

@arashb That simply does a sub query select. It would result in this

select 
(select name from flights where flights.destination_id = destinations.id order by arrived_at desc limit 1) as last_flight
from destinations