Why complicated scope with subquery does not work

In my Laravel 5.7/ mysql app I make request on a storage_spaces table, which has client_id field(can be empty) which is reference to clients table. Any client can has several emails and there is client_additive_emails table with fields client_id and email I need by $search value with email entered (like 'hgedsaemail3.com' value) get all storage_spaces whose clients has entered emails in client_additive_emails. I have a complcated request where $filter_search is one of the parameters and it fails to find valid rows by entered email:

$storageSpacesCollection = StorageSpace
    ::getByStatus($filter_status)
    ->whereRaw('storage_spaces.id <= 8') // DEBUGGING
    ->getById($relatedStorageSpacesArray)
    ->getByLocationId($filter_location_id, 'warehouses')
    ->getByCapacityCategoryId($filter_capacity_category_id, 'storage_capacities')
    ->getByLevel($filter_level)
    ->getByNumber($filter_number, true)
    ->orderBy('storage_spaces.id', 'asc')
    ->getByStorageCapacityId($filter_storage_capacity_id)
    ->getByClientId($filter_client_id)
    ->getByColorId($filter_color_id)
    ->getBySearch($filter_search) // THAT IS COMPLICATED SCOPE

    ->leftJoin( 'storage_capacities', 'storage_capacities.id', '=', 'storage_spaces.storage_capacity_id' )
    ->leftJoin( 'warehouses', 'warehouses.id', '=', 'storage_spaces.warehouse_id' )
    ->leftJoin( 'clients', 'clients.id', '=', 'storage_spaces.client_id')

    ->select(
        "storage_spaces.*",
        \DB::raw( "CONCAT(storage_capacities.count, ' ', storage_capacities.sqft ) as storage_capacity_name" ),
        \DB::raw("( SELECT check_ins.job_ref_no FROM check_ins WHERE check_ins.storage_space_id=storage_spaces.id ORDER BY check_ins.id DESC limit 1 ) AS job_ref_no"),
        "warehouses.name as warehouse_name",
        "clients.id as client_id",
        \DB::raw( "CONCAT( clients.id, ' ', clients.full_name ) as client_full_name" ) )
    ->get();

search is impemented in scope with subquesry on emails search :

public function scopeGetBySearch($query, $search = null)
{
    if (empty($search)) {
        return $query;
    }
    $search_is_is_valid_float = with(new StorageSpace)->isValidFloat($search);

    $tb= with(new StorageSpace)->getTable();
    return $query->where(
        $tb.'.number',  $search
    ) ->orWhere(function ($query) use ($search, $tb, $search_is_is_valid_float) {
        $query->where( $tb.".notes",  'like',  '%'.$search.'%' )
            ->orWhere("clients.full_name", $search)
            ->orWhereRaw("(SELECT check_ins.job_ref_no FROM check_ins WHERE check_ins.storage_space_id=storage_spaces.id ORDER BY check_ins.id ASC LIMIT 1) = '".$search."'");

        if($search_is_is_valid_float) {
            $query->orWhere("storage_capacities.count", $search);
            $query->orWhere($tb . ".selling_range", $search);
            $query->orWhere($tb . ".actual_storage_rent", $search);
            $query->orWhere($tb . ".insurance_vat", $search);
        } // if($search_is_is_valid_float) {


        // THAT SUBQUERY DOES NOT WORK:
        $query->orWhereIn('storage_spaces.client_id', function($query) use ($search){
            $query->select('client_additive_emails.client_id')
                  ->from("client_additive_emails")
                  ->where('client_additive_emails.client_id', "clients.id")
                  ->where('client_additive_emails.email', $search);
        });

and in sql-trace I see :

   SELECT `storage_spaces`.*, CONCAT(storage_capacities.count, ' ', storage_capacities.sqft )     AS storage_capacity_name, (   SELECT check_ins.job_ref_no 
    FROM check_ins 
    WHERE check_ins.storage_space_id=storage_spaces.id 
    ORDER BY check_ins.id DESC limit 1 )     AS job_ref_no, `warehouses`.`name`     AS `warehouse_name`, `clients`.`id`     AS `client_id`, CONCAT( clients.id, ' ', clients.full_name )     AS client_full_name 
    FROM `storage_spaces` 
    LEFT JOIN `storage_capacities` on `storage_capacities`.`id` = `storage_spaces`.`storage_capacity_id` 
    LEFT JOIN `warehouses` on `warehouses`.`id` = `storage_spaces`.`warehouse_id` 
    LEFT JOIN `clients` on `clients`.`id` = `storage_spaces`.`client_id` 
    WHERE storage_spaces.id <= 8     AND (`storage_spaces`.`number` = 'hgedsaemail3.com'     OR (`storage_spaces`.`notes` like '%hgedsaemail3.com%'     OR `clients`.`full_name` = 'hgedsaemail3.com'     OR (  SELECT check_ins.job_ref_no 
    FROM check_ins 
    WHERE check_ins.storage_space_id=storage_spaces.id 
    ORDER BY check_ins.id ASC LIMIT 1) = 'hgedsaemail3.com'     OR `storage_spaces`.`client_id` in (  SELECT `client_additive_emails`.`client_id` 
    FROM `client_additive_emails` 
    WHERE `client_additive_emails`.`client_id` = 'clients.id'     AND `client_additive_emails`.`email` = 'hgedsaemail3.com'))) 
    ORDER BY `storage_spaces`.`id` asc 

I try to debug the subquery with client id = 1885(if to run sql above without $search value entered then storage_spaces with client_id= 1885 is returned)

select *
 FROM `client_additive_emails` 
    WHERE `client_additive_emails`.`client_id` = 1885    AND 
`client_additive_emails`.`email` = 'hgedsaemail3.com'

It returns 1 rows as I expected

But I do not see why my request does not work ?

Thanks!

from Newest questions tagged laravel-5 - Stack Overflow https://ift.tt/2Zlw4Vx
via IFTTT