/** * @param int $character_id * * @return \Illuminate\Support\Collection */ public function characterTopWalletTransactionInteractions(int $character_id) { return WalletTransaction::leftJoin('character_affiliations', function ($join) { $join->on('character_affiliations.characterID', '=', 'character_wallet_transactions.clientID'); })->where('character_wallet_transactions.characterID', $character_id)->where('character_wallet_transactions.clientID', '<>', $character_id)->select('character_affiliations.characterID', 'character_affiliations.characterName', 'character_affiliations.corporationID', 'character_affiliations.corporationName', 'character_affiliations.allianceID', 'character_affiliations.allianceName')->selectRaw('count(clientID) as total')->groupBy('clientID'); }