Error in counting data numbers from a table using JOIN in Laravel 5.3

Asked

Viewed 64 times

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 the 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 a mistake I don’t know how to fix.

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
  • 3

    Is not group_by and yes groupBy.

  • Fixed the bug but now no longer listing users

  • Hence it is already a problem in your query. You have already tried to run this query in your DBMS ?

  • No, you can’t see by my code if I’m doing something wrong ?

  • I commented wrong. So I don’t know the structure of your table to tell you what might be wrong. I also don’t know what data needs to be shown. You should see this.

  • Take this query and run on your DBMS.

  • How am I going to run this on mysql if this is readable

  • Are you serious ? Convert this into a query for Mysql !

  • Not returning anything in mysql do not know what could be wrong help me

Show 4 more comments

1 answer

1

The group_by does not exist in Laravel 5.3, the correct is groupBy, see the documentation: https://laravel.com/docs/5.3/queries#Parameter-grouping

Thus:

$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, as for example (source: https://stackoverflow.com/a/37289496/1518921):

$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]);

  • Yes this already solved the error but now the problem is that no longer list me users does not list me anything should be missing in the query

  • Show this on the screen []

  • In order called as so did not realize your question

  • executed at the end of DB::table

  • One stitch is missing and a comma you want the result ?

  • You have this link for the result http://pastebin.com/XnEKwfeM

  • Da Undefined variable: model

  • I tested in phpmyadmin it does not return any data but this to fetch all tables of the joins

  • If I take Join from the players it lists the users as I want only when I added them to do Count and they stopped listing

  • I want inside what I have already done because I believe something is missing in the query and I want it with Laravel. You want me to assemble the query without Lockable and post here ?

  • I made in mysql the query without Standard but if using the Join players does not return anything if the take returns the user data right one thing will be because the players table is empty ?

  • @Césarsousa yes, if one of the tables is empty does not return anything, since the INNER JOIN depends on it, read this: http://answall.com/a/6448/3635 ... Now what I don’t understand, how was I returning something before? It’s very likely that you modified something without much attention.

  • Please avoid long discussions in the comments; your talk was moved to the chat

  • But the table may be empty because there may be no registered players associated that user want it and return the number of players that each user has is 0 or if it has returns their number

Show 9 more comments

Browser other questions tagged php laravel-5.3

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