How can I execute this query with Laravel 5?

Asked

Viewed 325 times

2

How I would perform the following query in Laravel 5:

SELECT *, (SELECT COUNT(`anuncios`.`cat-id`)
FROM `anuncios` WHERE `anuncios`.`cat-id`=`categoria`.`cat-id`)
AS `cat-total`
FROM `categoria`
ORDER BY `cat-nome` ASC
php laravel query count

2 answers

2


This way 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();

0

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 for categorias.

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

And finally, you don’t need the count in the SELECT, but can simply use a feature present in Collection of Laravel.

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

When using the foreach, just do:

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

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

Browser other questions tagged php laravel query count

You are not signed in. Login or sign up in order to post.