withSum results Invalid parameter number

[helturkey]

thanks for your package it helped me to decrease query time from 500 ms to less than 1 ms, but it is not working will with withSum. it returns Invalid parameter number and clear all columns except id.

it also in this case exchange where values as below publish should be 1 and category_id should be 3

select `table`.`id`, (select sum(`another_table`.`views`) from `another_table` where `table`.`id` = `another_table`.`table_id` and `publish` = 3) as `views` from `table` where `category_id` = 1 and `publish` = ? order by `views` desc limit 24 offset 648

[aarondfrancis]

Hey @helturkey, thanks for the issue! Can you show me the PHP code that generated this? Thanks!

[aarondfrancis]

Also can you make sure you're using the latest version of the package?

[VirusEcks]

i just faced this error

select users.id, (select count(*) from follows where users.id = follows.follow_id and follows.follow_type = ?) as followers_count from users where id != ? and users.deleted_at is null and users.enabled = ? order by followers_count desc limit 5 offset 0

passed arguments:

array:2 [▼ 0 => 11 1 => true ]

follow, enable, delete are traits

i'm using version 0.1.7

[aarondfrancis]

@VirusEcks can you give me the PHP code that generated this? Thanks!

[VirusEcks]

the query is scattered among many function/ files but i'll try to put it together as best as i could (so i apologize in advance)

• follow relation

    public function followers(): MorphMany
    {
        return $this->MorphMany(Follow::class, 'follow');
    }

• global scopes

scope('soft_delete') // laravel

scope('enable_disable') // copy of above but for enable field

    public function apply(Builder $builder, Model $model)
    {
         $builder->Where($model->getQualifiedEnabledColumn(), true);
    }

scope('orderByFollowersCount')

    public function scopeOrderByFollowersCount($query, string $direction = 'desc')
    {
        return $query->withCount('followers')->orderBy('followers_count', $direction);
    }

• appends ( might not be relevant to query but is relevant to the missing parameter )

    public function getIsFollowingAttribute()
    {
        /* @var Model $this */
        if ($this->relationLoaded('followers')) {
            return $this->followers
                    //->where('pivot.accepted_at', '!=', null)
                    ->where('pivot.owner_id', Auth::user()?->getKey())
                    ->where('pivot.owner_type', Auth::user()?->getMorphClass())
                    ->first();
        }

        return $this->followers()
                //->where('accepted_at', '!=', null)
                ->where('owner_id', Auth::user()?->getKey())
                ->where('owner_type', Auth::user()?->getMorphClass())
                ->first();
    }

• short version

$query
// with eager loaded relations
->with(['avatar', 'cover', 'entity_infos'])
// condition for query
->where('id', '!=', 11)
//  scopes
->scope('OrderByFollowersCountDesc')
->scope('soft_delete')
->scope('enable_disable')
// paginate
->fastPaginate(5, ['*'], page: 1);
// append
->append('is_following');

• final query

  select `users`.`id`, (select count(*) from `follows` where `users`.`id` = `follows`.`follow_id` and `follows`.`follow_type` = ?) as `followers_count` from `users` where `id` != ? and `users`.`deleted_at` is null and `users`.`enabled` = ? order by `followers_count` desc limit 5 offset 0 

• query args passed to mysql according to debugbar

  array:2 [
  0 => 11
  1 => true
  ]

• expected arguments (i think)

  
  array:3 [
  0 => 'UserModel'
  1 => 11
  2 => true
  ]

[aarondfrancis]

Excellent, thank you! I can dig into this now.

[helturkey]

this issue occurs only when you try to use withSum with condition like so:

->withSum(['posts' => fn($query) => $query->where('published', true)], 'views')

but it works if you used

->withSum('posts' , 'views')

[aarondfrancis]

I think we fixed this one with #37. If a column includes a binding then we'll defer to regular pagination just to be safe. Let me know if you run into anything else and thanks for all the info!

[helturkey]

it works fine, thank you