Eloquent OrWhere orWhereHas slowing down the application

The following code checks the whether subscriber can access the property or not.

If property subscriber_id == current subscriber id, can access it.

If property subscriber_id within linked offices subscriber id, can access it.

Elseif property table auction_booked_by or presentations table auction_booked_by equals to subscriber, can access it.

I have used the orWhere and orWhereHas in query builder. I am getting data but it takes more than 7 to 8 secs. With out orWhereHas, taking only 2 to 3 secs. What would be the solution?

Here is my full code to get property data: $this->model is Property model. And Property belongs to many presentations.

$currentSubscriber = Auth::user()->subscriber_id;
$linkedOfficesSubscribers = Auth::user()->subscriber->getLinkedAgencyIdsAttribute();
$subscriberIds = $linkedOfficesSubscribers->merge($currentSubscriber);
$sidewaysSubscriberIds = Auth::user()->subscriber->getSidewaysAgenciesIdsAttribute();
$query = $this->model
	->join('subscribers', 'properties.subscriber_id', '=','subscribers.id')
	->join('property_auction_details', 'property_auction_details.property_id', '=','properties.id')
	->with('subscriber')
	->whereIn('properties.subscriber_id', $subscriberIds)
	->orWhere(function ($query) use ($sidewaysSubscriberIds) {
		$query->whereIn('properties.subscriber_id', $sidewaysSubscriberIds)
			->where('properties.auction_booked_by', Auth::user()->subscriber_id)
			->orWhereHas('presentations', function ($query) {
				$query->where('presentations.auction_booked_by', Auth::user()->subscriber_id);
			});
	});

click

2 years ago

Level 35

I don't have the solution for you but I might be able to guide you in a direction. In general or are more difficult to optimize.

When queries start becoming slow the first thing you have to look for if there is an option to add an index to one or multiple columns. Or maybe there is a simpler way to build your result set?

There is not a clear single answer, you have to start debugging a little bit and see what works for your case.

Try getting the raw query (you can use ->toSql() on the builder) and prepend it with EXPLAIN and run it. This should give you some information what mysql needs to do to run your query and if it s capable of using an index or not.

Some links that might give you some inspiration or understanding.

tisuchi

2 years ago

Level 70

@govindalohani I think orWhere() is making your query slower. You can use debug bar to check exactly what makes your app slower.

Btw, this could be an alternative:

$currentSubscriber = Auth::user()->subscriber_id;
$linkedOfficesSubscribers = Auth::user()->subscriber->getLinkedAgencyIdsAttribute();
$subscriberIds = $linkedOfficesSubscribers->merge($currentSubscriber);
$sidewaysSubscriberIds = Auth::user()->subscriber->getSidewaysAgenciesIdsAttribute();

$query1 = $this->model
	->join('subscribers', 'properties.subscriber_id', '=','subscribers.id')
	->join('property_auction_details', 'property_auction_details.property_id', '=','properties.id')
	->with('subscriber')
	->whereIn('properties.subscriber_id', $subscriberIds);

$query2 = $this->model
	->join('subscribers', 'properties.subscriber_id', '=','subscribers.id')
	->join('property_auction_details', 'property_auction_details.property_id', '=','properties.id')
	->with('subscriber')
	->whereIn('properties.subscriber_id', $sidewaysSubscriberIds)
	->where('properties.auction_booked_by', $currentSubscriber);

$presentation_ids = DB::table('presentations')
	->where('auction_booked_by', $currentSubscriber)
	->pluck('id');

$query3 = $this->model
	->whereIn('id', $presentation_ids);

$properties = $query1->union($query2)->union($query3)->get();

4 likes

govindalohani

2 years ago

Level 1

I tried with the union query. The data is coming through but I am using yarja datatable so I need to feed these data to create datatable and return to DOM . I have large datasets more than 50k records from past few years and I am using pagination in datatable as well. But the query is still taking longer time to fetch data from database and show in the dom. What would be the best approach for this kind of scenario?

Here is my query scopes:

public function scopeWhereUserCanAccess(Builder $query, $periodType){

    // Execution time for getting these linked subscribers are in 100 to 150 milliseconds
    $currentSubscriber = Auth::user()->subscriber_id;
    $linkedAgenciesSubscribers = Auth::user()->subscriber->getLinkedAgencyIdsAttribute();
    $subscriberIds = $linkedAgenciesSubscribers->merge($currentSubscriber);
    $sidewaysSubscriberIds = Auth::user()->subscriber->getSidewaysAgenciesIdsAttribute();
    $childrenCorporateIdsAndSelf = Auth::user()->subscriber->linkedChildrenCorporateIds->merge($currentSubscriber);
    
    
    // Execution time for getting list of properties 3 to 4 secs at least
    $query2 = clone $query;
    
    // properties of linkedAgenciesIds and Self
    $query1 = $query
        ->when($periodType == "past", function($query) {
            return $query->wherePast(); // returns data with auction datetime < than today's date
        }, function($query) {
            return $query->whereUpcoming();  // returns data with auction datetime >= than today's date
        })
        ->with('subscriber')
        ->whereIn('properties.subscriber_id', $subscriberIds);
    
    // Lookup for properties of sideways Agencies which is booked for $childrenCorporateIdsAndSelf
    $query2 = $query2
        ->when($periodType == "past", function($query) {
            return $query->wherePast(); // returns data with auction datetime < than today's date
        }, function($query) {
            return $query->whereUpcoming(); // returns data with auction datetime >= than today's date
        })
        ->select('properties.*')
        ->with('subscriber')
        ->whereIn('properties.subscriber_id', $sidewaysSubscriberIds)
        ->join('presentation_property', 'presentation_property.property_id', '=','properties.id') // presentations table does not have property_id but presentation_property has property_id column
        ->join('presentations', 'presentations.id', '=','presentation_property.presentation_id')
        ->whereIn(DB::raw('COALESCE(properties.auction_booked_by, presentations.auction_booked_by)'), $childrenCorporateIdsAndSelf);
        // first check in properties table booked for $childrenCorporateIdsAndSelf else presentations table booked for $childrenCorporateIdsAndSelf
    
    $properties = $query1
        ->union($query2)
        ->get();
    
    return $properties;
}

public function scopeWherePast($query, $dateTime = null) {

    if ($dateTime == null) {
        $dateTime = Carbon::today(Auth::user()->timezone)->startOfDay()->setTimezone('UTC')->toDateTimeString();
    } else {
        $dateTime = $dateTime->copy()->setTimezone('UTC')->toDateTimeString();
    }

    return $query->whereHas('auctionDetail', function($query) use ($dateTime) {
        $query->whereRaw("
                IF (properties.auction_type = 4,
                    auction_end_date_time < ?,
                    auction_date_time < ?
                )", [$dateTime, $dateTime]);
    });
}

public function scopeWhereUpcoming($query, $dateTime = null) {

    if ($dateTime == null) {
        $dateTime = Carbon::today(Auth::user()->timezone)->startOfDay()->setTimezone('UTC')->toDateTimeString();
    } else {
        $dateTime = $dateTime->copy()->setTimezone('UTC')->toDateTimeString();
    }

    return $query->whereHas('auctionDetail', function($query) use ($dateTime) {
        $query->whereRaw("
                IF (properties.auction_type = 4,
                    auction_end_date_time >= ?,
                    auction_date_time >= ?
                )", [$dateTime, $dateTime]);
    });
}

Jsanwo64

2 years ago

Level 11

@govindalohani

try


public function scopeWhereUserCanAccess(Builder $query, $periodType) {
    $currentSubscriber = Auth::user()->subscriber_id;
    $linkedAgenciesSubscribers = Auth::user()->subscriber->getLinkedAgencyIdsAttribute();
    $subscriberIds = $linkedAgenciesSubscribers->merge($currentSubscriber);
    $sidewaysSubscriberIds = Auth::user()->subscriber->getSidewaysAgenciesIdsAttribute();
    $childrenCorporateIdsAndSelf = Auth::user()->subscriber->linkedChildrenCorporateIds->merge($currentSubscriber);
    
    $query1 = $this->applyPeriodFilter($query, $periodType)
        ->with('subscriber')
        ->whereIn('properties.subscriber_id', $subscriberIds);
    
    $query2 = $this->applyPeriodFilter(clone $query, $periodType)
        ->select('properties.*')
        ->with('subscriber')
        ->whereIn('properties.subscriber_id', $sidewaysSubscriberIds)
        ->join('presentation_property', 'presentation_property.property_id', '=', 'properties.id')
        ->join('presentations', 'presentations.id', '=', 'presentation_property.presentation_id')
        ->whereIn(DB::raw('COALESCE(properties.auction_booked_by, presentations.auction_booked_by)'), $childrenCorporateIdsAndSelf);
    
    $properties = $query1->union($query2)->get();
    
    return $properties;
}

public function scopeWherePast($query, $dateTime = null) {
    $dateTime = $this->getUtcDateTimeString($dateTime);
    
    return $query->whereHas('auctionDetail', function($query) use ($dateTime) {
        $query->whereRaw("IF (properties.auction_type = 4, auction_end_date_time < ?, auction_date_time < ?)", [$dateTime, $dateTime]);
    });
}

public function scopeWhereUpcoming($query, $dateTime = null) {
    $dateTime = $this->getUtcDateTimeString($dateTime);
    
    return $query->whereHas('auctionDetail', function($query) use ($dateTime) {
        $query->whereRaw("IF (properties.auction_type = 4, auction_end_date_time >= ?, auction_date_time >= ?)", [$dateTime, $dateTime]);
    });
}

private function applyPeriodFilter($query, $periodType) {
    return $query->when($periodType == "past", function($query) {
        return $query->wherePast();
    }, function($query) {
        return $query->whereUpcoming();
    });
}

private function getUtcDateTimeString($dateTime) {
    if ($dateTime == null) {
        $dateTime = Carbon::today(Auth::user()->timezone)->startOfDay()->setTimezone('UTC')->toDateTimeString();
    } else {
        $dateTime = $dateTime->copy()->setTimezone('UTC')->toDateTimeString();
    }
    
    return $dateTime;
}

public function scopeWhereUserCanAccess(Builder $query, $periodType) {
    $currentSubscriber = Auth::user()->subscriber_id;
    $linkedAgenciesSubscribers = Auth::user()->subscriber->getLinkedAgencyIdsAttribute();
    $sidewaysSubscriberIds = Auth::user()->subscriber->getSidewaysAgenciesIdsAttribute();
    $childrenCorporateIdsAndSelf = Auth::user()->subscriber->linkedChildrenCorporateIds->merge($currentSubscriber);
    
    $properties = $query
        ->when($periodType == "past", function($query) {
            return $query->wherePast();
        }, function($query) {
            return $query->whereUpcoming();
        })
        ->with('subscriber')
        ->whereIn('properties.subscriber_id', $linkedAgenciesSubscribers->merge($currentSubscriber))
        ->orWhereIn('properties.subscriber_id', $sidewaysSubscriberIds)
        ->join('presentation_property', 'presentation_property.property_id', '=', 'properties.id')
        ->join('presentations', 'presentations.id', '=', 'presentation_property.presentation_id')
        ->whereIn(DB::raw('COALESCE(properties.auction_booked_by, presentations.auction_booked_by)'), $childrenCorporateIdsAndSelf)
        ->select('properties.*')
        ->distinct()
        ->get();
    
    return $properties;
}

public function scopeWherePast($query, $dateTime = null) {
    $dateTime = $this->getUtcDateTimeString($dateTime);
    
    return $query->whereHas('auctionDetail', function($query) use ($dateTime) {
        $query->where(function($query) use ($dateTime) {
            $query->where('properties.auction_type', 4)
                ->where('auction_end_date_time', '<', $dateTime);
        })->orWhere(function($query) use ($dateTime) {
            $query->where('properties.auction_type', '!=', 4)
                ->where('auction_date_time', '<', $dateTime);
        });
    });
}

public function scopeWhereUpcoming($query, $dateTime = null) {
    $dateTime = $this->getUtcDateTimeString($dateTime);
    
    return $query->whereHas('auctionDetail', function($query) use ($dateTime) {
        $query->where(function($query) use ($dateTime) {
            $query->where('properties.auction_type', 4)
                ->where('auction_end_date_time', '>=', $dateTime);
        })->orWhere(function($query) use ($dateTime) {
            $query->where('properties.auction_type', '!=', 4)
                ->where('auction_date_time', '>=', $dateTime);
        });
    });
}

private function getUtcDateTimeString($dateTime) {
    if ($dateTime == null) {
        $dateTime = Carbon::today(Auth::user()->timezone)->startOfDay()->setTimezone('UTC')->toDateTimeString();
    } else {
        $dateTime = $dateTime->copy()->setTimezone('UTC')->toDateTimeString();
    }
    
    return $dateTime;
}

Please or to participate in this conversation.