How can I execute this query with Laravel 5?

This is with Inner Join. But if you want something even more organized, do what Wallace posted.

<?php
    $anuncios = Categoria::orderBy('cat-nome')
                ->join('anuncios', 'anuncios.cat-id', '=', 'categoria.cat-id')
                ->selectRaw('*, count(anuncions.cat-id) as cat-total')
                ->get();

You have to create the models referring to the tables.

class Anuncio
{
     protected $table = 'anuncios';
}


class Categoria
{
      protected $table = 'categorias';
}

Then create the relationship in Anuncio to categorias .

public function categorias()
{
     return $this->hasMany('Categoria', 'cat-id', 'cat-id');
}

And lastly, you do not need count in SELECT , but you can simply use a feature in Collection of Laravel .

$anuncios = Anuncio::with('categorias')->get();

When using foreach , just do:

foreach($anuncios as $anuncio)
{
      echo $anuncio->categorias->count();
}

It may seem more complex, but I would prefer to use the way that Laravel abstracts each table, to make it easier to reuse database data.