laravel query run twice

5 seconds or 5 ms which is millisecond ? It take a lot if it is in seconds. And you should check the queries.. Are you sharing the count with all the views maybe? Share some code where you fetch the accommodations so someone can help you with writing a better query maybe.

yes nakov your right i must share some code here we go dude :

  $data = QueryBuilder::for(Accommodation::class)
            ->allowedFilters([
                AllowedFilter::scope('bed_count'),
                AllowedFilter::scope('filter_price'),
                AllowedFilter::scope('filter_date'),
                AllowedFilter::scope('discounts'),
                AllowedFilter::exact('grade_stars'),
                AllowedFilter::exact('city_id'),
                AllowedFilter::exact('is_recommended'),
                AllowedFilter::exact('accommodation_type_id'),
                'name',
            ])
            ->allowedAppends(['cheapestroom'])
            ->allowedIncludes(['gallery','city','accommodationRooms','accommodationRooms.roomPricingHistorySearch','discounts','prices'])
            ->allowedSorts([
                AllowedSort::custom('discount', new DiscountSort() ,'amount'),
                AllowedSort::custom('price', new PriceSort() ,'price'),
            ])

            ->paginate(10);

this is my spatie query builder and this is the part that takes 5 secs twice :

class DiscountSort implements Sort
{
    public function __invoke(Builder $query, bool $descending, string $property) : Builder
    {
        $direction = $descending ? 'DESC' : 'ASC';
        $data = $query->join('accommodation_rooms', 'accommodations.id', '=', 'accommodation_rooms.accommodation_id')
            ->join('discounts', 'accommodation_rooms.id', '=', 'discounts.accommodation_room_id')
            ->select('accommodation_rooms.id')
            ->orderBy('discounts.amount', 'desc')
            ->select('discounts.amount', 'accommodations.*')
            ->groupBy('discounts.amount', 'accommodation_rooms.id')
        ;
        return $data;
    }

its a join and because laravel and this package are not able to order data by relationship or nested relationship value i should have used a join . can that second query be the cause of pagination ??

If you are paginating laravel needs to know the total amount of accommodations , hence the first query. The second he actually grabs the first 10 rows.

5 sec ! I hope it is indeed 5ms ... or there is something very wrong with your database ;)

@hondnl its a complicated filter and my database has like 3 tables with like 5k records in each of them and i am reading from all of them this should not make my query that long ? :)

btw add into your AppServiceProvider in the boot :

   DB::listen(function ($query) {
            if(!preg_match('/(information_schema|migrations|drop table|create table|alter table)/i',$query->sql)) {
                echo '<pre>';
                print_r([
                    $query->sql,
                    $query->bindings,
                    $query->time
                ]);
                echo '<pre>';
            }
        });

Will give you the whole query... A lot easier to debug big queries and see where you could improve. For more complicated queries I generally start in mysql (phpadmin or any othe gui) and once I am satisfied with the performance of it I convert it back to Eloquent.

@hondnl this is my query but ofc i get it from telescope

select
  count(*) as aggregate
from
  `accommodations`
where
  exists (
    select
      `bed_count`
    from
      `accommodation_rooms`
    where
      `accommodations`.`id` = `accommodation_rooms`.`accommodation_id`
      and `bed_count` = '3'
  )
  and exists (
    select
      `sales_price`
    from
      `room_pricing_histories`
      inner join `accommodation_rooms` on `accommodation_rooms`.`id` = `room_pricing_histories`.`accommodation_room_id`
    where
      `accommodations`.`id` = `accommodation_rooms`.`accommodation_id`
      and `sales_price` > '1'
      and `sales_price` < '99990000'
  )
  and exists (
    select
      *
    from
      `accommodation_rooms`
    where
      `accommodations`.`id` = `accommodation_rooms`.`accommodation_id`
      and exists (
        select
          `from_date`,
          `to_date`
        from
          `room_capacity_histories`
        where
          `accommodation_rooms`.`id` = `room_capacity_histories`.`accommodation_room_id`
          and date(`from_date`) > '2019-05-22'
          and date(`to_date`) < '2029-10-22'
      )
  )
  and exists (
    select
      `amount`,
      `is_deleted`
    from
      `discounts`
      inner join `accommodation_rooms` on `accommodation_rooms`.`id` = `discounts`.`accommodation_room_id`
    where
      `accommodations`.`id` = `accommodation_rooms`.`accommodation_id`
      and `is_deleted` = 0
  )
  and `grade_stars` = '3'
  and `city_id` = '329'

hmm that is a lot of Exists in one query... Try to rewrite this query with (left) joins , see if that improves the performance. I don;t have a lot of time atm ... but will take a look later if you don't figure it out yourself.

Good luck!

Thanks @hondnl i will go look for it