Error in counting data numbers of a table using JOIN in laravel 5.3 [closed]

Question

Error in counting data numbers of a table using JOIN in laravel 5.3 [closed]

Navigation

#1 by (1 votes)

1

I'm doing a query that will list users with data from multiple tables so I'm using JOIN .

I've already been able to list user data, what I'm trying to do now is count the number of players that belong to that user I'm listing. I've done everything but you're presenting me with an error that I do not know how to solve.

I count on your help.

Error

Call to undefined method Illuminate \ Database \ Query \ Builder :: group_by ()

Code

class ListaAgentesController extends Controller{

    public function lista_agentes (){

        $user_id = Auth::user()->id;

        $lista_agentes = DB::table('agents')->join('agent_types', 'agent_types.id', '=', 'agents.type')
                                            ->join('players', 'players.agent', '=', 'agents.id')
                                            ->where('agents.id', '!=', $user_id)
                                            ->select('agents.*', 'agent_types.*', 'players.*', 'agents.id as user_id', DB::raw('COUNT(players.id) as count_palyers'))                                         
                                            ->group_by('players.id')
                                            ->get();

        return view('admin.templates.agentes', ['lista_agentes' => $lista_agentes]);

    } 

}

php laravel-5.3

asked by anonymous 25.01.2017 / 10:34

1 answer

Error DLL Connection FireDac with PostgreSQL Mouseout function on icon

score 1 · Answer 1

The group_by does not exist in Laravel 5.3, the correct is groupBy , see the documentation: link

So:

$lista_agentes = DB::table('agents')->join('agent_types', 'agent_types.id', '=', 'agents.type')
                                    ->join('players', 'players.agent', '=', 'agents.id')
                                    ->where('agents.id', '!=', $user_id)
                                    ->select('agents.*', 'agent_types.*', 'players.*', 'agents.id as user_id', DB::raw('COUNT(players.id) as count_palyers'))                                         
                                    ->groupBy('players.id')
                                    ->get();

 return view('admin.templates.agentes', ['lista_agentes' => $lista_agentes]);

To debug the query you can try the toSql and getBindings , such as ( source: link ):

$builder = DB::table('agents')->join('agent_types', 'agent_types.id', '=', 'agents.type')
                                    ->join('players', 'players.agent', '=', 'agents.id')
                                    ->where('agents.id', '!=', $user_id)
                                    ->select('agents.*', 'agent_types.*', 'players.*', 'agents.id as user_id', DB::raw('COUNT(players.id) as count_palyers'))                                         
                                    ->groupBy('players.id');


$lista_agentes = $builder->get();

$sql = $builder->toSql();
$bindings = $builder->getBindings();

foreach ($bindings as $replace){
    $pos = strpos($sql, '?');

    if ($pos !== false) {
        $sql = substr_replace($sql, $replace, $pos, 1);
    }
}

dd($sql); //Extrai o conteudo da query

return view('admin.templates.agentes', ['lista_agentes' => $lista_agentes]);