(Laravel) Multiple counts, group by on same table with one query

I have two tables. I would like to get the values of the counts of these two rows (attacker, victim) from the first table and their respective userid from the second table, if possible.

Table1:

id----attacker---victim
1-----1216-------1040
1-----1216-------1024
1-----1040-------1024
1-----1040-------1024
1-----1024-------1216
1-----1216-------1024

Table2:

id----name----userid
1-----John----1216
2-----Joe-----1024
3-----Peter---1040

So far I only managed to get the values of the counts of rows attacker and victim but I achieved it using two separate queries. Is it possible to do this with just one query?

$victims = DB::table('Table1')
    ->select(DB::raw('victim as userid, count(victim) as victims'))
    ->where('victim', '<>', 1)
    ->groupBy('victim')
    ->get();

$attackers = DB::table('Table1')
    ->select(DB::raw('attacker as userid, count(attacker) as attackers'))
    ->where('attacker', '<>', 1)
    ->groupBy('attacker')
    ->get();

Basically what I am aiming for is to get something like this in the end:

name----------(count of attacker)-----(count of victim)
John-----------3----------------------1
Joe------------1----------------------4
Peter----------2----------------------1

UPDATE:

After some time I managed to get name + victims and name + attackers but separate by using the code below:

$kills = DB::table('Table2')
    ->join('Table1', 'Table2.roleid', '=', 'Table1.attacker')
    ->select(DB::raw('Table2.name, count(Table1.attacker) as kills'))
    ->groupBy('Table2.name')
    ->orderBy('kills', 'desc')
    ->get();

$deaths = DB::table('Table2')
    ->join('Table1', 'Table2.roleid', '=', 'Table1.victim')
    ->select(DB::raw('Table2.name, count(Table1.victim) as deaths'))
    ->groupBy('Table2.name')
    ->orderBy('deaths', 'desc')
    ->get();

Answer

After some hours of researching I think I somehow managed to find a solution. I just had to use relationships.

On Table 2 Model:

public function kills()
{
    return $this->hasMany('Kill', 'attacker', 'userid');
}

public function deaths()
{
    return $this->hasMany('Kill', 'victim', 'userid');
}

then I used the following code to get the results I wanted:

$results = Table2::select('name')
            ->whereHas('kills')->orWhereHas('deaths')
            ->withCount(['kills'=> $query])
            ->withCount(['deaths'=> $query])
            ->orderBy('kills_count', 'desc')
            ->get();

return $results->toArray();

I’ll also post the RAW SQL query incase anyone could benefit from it

SELECT
   `name`,
   (
      SELECT
         COUNT(*) 
      FROM
         `Table1` 
      WHERE
         `Table2`.`userid` = `Table1`.`attacker` 
   )
   AS `kills_count`,
   (
      SELECT
         COUNT(*) 
      FROM
         `Table1` 
      WHERE
         `Table2`.`userid` = `Table1`.`victim` 
   )
   AS `deaths_count` 
FROM
   `Table2` 
WHERE
   EXISTS 
   (
      SELECT
         * 
      FROM
         `Table1` 
      WHERE
         `Table2`.`userid` = `Table1`.`attacker` 
   )
   OR EXISTS 
   (
      SELECT
         * 
      FROM
         `Table1` 
      WHERE
         `Table2`.`userid` = `Table1`.`victim` 
   )
ORDER BY
   `kills_count` DESC

If anyone knows how to further improve this query for performance, please let me know.