How to optimize the following query Laravel?

Question

I have the code which is working perfectly fine.

 $amenityCategoryMapping1 = AmenityCategoryMapping::where('property_id', $property->id)
    ->orderBy('amenity_name','asc')
    ->orderBy('updated_at','desc')
    ->pluck('category_id', 'amenity_name')->toArray();

$amenityCategoryMapping2 = AmenityCategoryMapping::where('company_id', $property->company_id)
    ->orderBy('amenity_name','asc')
    ->orderBy('updated_at','desc')
    ->pluck('category_id', 'amenity_name')->toArray();

$amenityCategoryMapping3 = AmenityCategoryMapping::whereNull('property_id')
    ->orderBy('amenity_name','asc')
    ->orderBy('updated_at','desc')
    ->pluck('category_id', 'amenity_name')->toArray();

$amenityCategoryMapping = array_merge($amenityCategoryMapping3, $amenityCategoryMapping2, $amenityCategoryMapping1 );

However, currently I am executing 3 different query to extract data from the same table just using different parameters. Is there any way to minimize this query requests and still receiving the same results?

Currently, while plucking if two array has the same key then I am replacing amenityCategoryMapping3 by amenityCategoryMapping2 and that by amenityCategoryMapping1. That is more priority should be given to amenityCategoryMapping1

You could perhaps use a union, removepluck and toArray methods from 1 and 2, then use union on 2 to merge 1, then on 3 to merge 2. Use pluck on the final one. That should take care of the duplicates. But, do analyse performance before discarding your current approach. Union could be faster or slower depending on your dataset and your memory limitations. — user3532758
– user3532758, Commented Aug 3, 2020 at 0:10

Anselme · Accepted Answer · 2020-08-02 14:10:18Z

1

Try using orWhere function

$amenityCategoryMapping1 = AmenityCategoryMapping::where('property_id', $property->id)
    ->orWhere('company_id', $property->company_id)
    ->orWhereNull('property_id')
    ->orderBy('amenity_name','asc')
    ->orderBy('updated_at','desc')
    ->pluck('category_id', 'amenity_name')->toArray();

answered Aug 2, 2020 at 14:10

Anselme

111 silver badge2 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Aayush Dahal Over a year ago

Sorry, it won't work because of the order of array merge. In this case, the key with latest updated_at value will replace all other data. However, clearly mentioned data came from property_id > company_id > null (property_id). This violate the array_merge concept on the question.

Anselme Over a year ago

I think there is no need to merge the array since all the data come. Try to remove the toArray function and add the get function()

Collectives™ on Stack Overflow

How to optimize the following query Laravel?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related