CharacterWalletJournal::select('first_party_id', 'second_party_id', 'ref_type', 'category', 'entity_id as party_id', 'name as party_name', DB::raw('count(*) as total'), DB::raw("CASE when universe_names.category = 'character' then (SELECT corporation_id FROM character_affiliations WHERE character_id = universe_names.entity_id) when universe_names.category = 'corporation' then (SELECT corporation_id FROM character_affiliations WHERE corporation_id = universe_names.entity_id LIMIT 1) end AS corporation_id, CASE when universe_names.category = 'character' then (SELECT alliance_id FROM character_affiliations WHERE character_id = universe_names.entity_id) when universe_names.category = 'corporation' then (SELECT alliance_id FROM character_affiliations WHERE corporation_id = universe_names.entity_id LIMIT 1) end AS alliance_id, CASE when universe_names.category = 'character' then (SELECT faction_id FROM character_affiliations WHERE character_id = universe_names.entity_id) when universe_names.category = 'corporation' then (SELECT faction_id FROM character_affiliations WHERE corporation_id = universe_names.entity_id LIMIT 1) end AS faction_id"))
                    ->leftJoin('universe_names', 'universe_names.entity_id', '=', 'character_wallet_journals.second_party_id')
                    ->whereIn('character_wallet_journals.character_id', $character_ids->toArray())
                    ->whereNotIn('character_wallet_journals.second_party_id', $character_ids->toArray())
                    ->groupBy('first_party_id', 'second_party_id', 'ref_type', 'category', 'party_id', 'party_name')