in my database i have
i'm trying to get this table data in relation ship and summing this values which stored in column, for example
UPDATE:
InstagramAccount class:
from Laravel use multiple where and sum in single clause
instagram_actions_histories
table which into that i have action_type
column, in the column i have unlike data such as 1
or 2
or 3
i'm trying to get this table data in relation ship and summing this values which stored in column, for example
$userAddedPagesList = auth()->user()->instagramPages()->with([
'history' => function ($query) {
$query->select(['action_type as count'])->whereActionType(1)->sum('action_type');
}
]
)->get();
btw, this code is not correct, in that i want to get all history
with multiple sum
inside thatwhereActionType(1)->sum('action_type')
whereActionType(2)->sum('action_type')
whereActionType(3)->sum('action_type')
for example (pesudo code):$userAddedPagesList = auth()->user()->instagramPages()->with([
'history' => function ($query) {
$query->select(['action_type as like'])->whereActionType(1)->sum('action_type');
$query->select(['action_type as follow'])->whereActionType(2)->sum('action_type');
$query->select(['action_type as unfollow'])->whereActionType(3)->sum('action_type');
}
]
)->get();
UPDATE:$userAddedPagesList = auth()->user()->instagramPages()->with([
'history' => function ($query) {
$query->select('*')
->selectSub(function ($query) {
return $query->selectRaw('SUM(action_type)')
->where('action_type', '=', '1');
}, 'like')
->selectSub(function ($query) {
return $query->selectRaw('SUM(action_type)')
->where('action_type', '=', '2');
}, 'follow')
->selectSub(function ($query) {
return $query->selectRaw('SUM(action_type)')
->where('action_type', '=', '3');
}, 'followBack');
}
]
)->get();
ERROR:Syntax error or access violation: 1140 Mixing of GROUP columns (MIN(),MAX(),COUNT(),...)
with no GROUP columns is illegal if there is no GROUP BY clause (SQL: select *,
(select SUM(action_type) where `action_type` = 1) as `like`, (select SUM(action_type)
where `action_type` = 2) as `follow`, (select SUM(action_type) where `action_type` = 3)
as `followBack` from `instagram_actions_histories` where `instagram_actions_histories`.
`account_id` in (1, 2, 3))
how can i implementing this solution?UPDATE:
InstagramAccount class:
class InstagramAccount extends Model
{
...
public function schedule()
{
return $this->hasOne(ScheduleInstagramAccounts::class, 'account_id');
}
public function history()
{
return $this->hasMany(InstagramActionsHistory::class, 'account_id');
}
}
InstagramActionsHistory class:class InstagramActionsHistory extends Model
{
protected $guarded=['id'];
public function page(){
return $this->belongsTo(InstagramAccount::class);
}
}
User class:class User extends Authenticatable
{
use Notifiable;
...
public function instagramPages()
{
return $this->hasMany(InstagramAccount::class);
}
}
from Laravel use multiple where and sum in single clause
No comments:
Post a Comment