join query sum fetch error

hi, i got the two following tables table name : formulario id primary bigint(20) mes_ano varchar(255) matricula varchar(255) table name: reparacoes id primary bigint(11) matricula varchar(255) valor varchar(255) what i want to accomplish is joining on the valor with formulario's values and fetch the data in laravel view dashboard. i tried the following way, but with the join it doesnt show accurate values.

public function index(Request $request, Formulario $id, Reparacoes $valor)
    {
        $data = Reparacoes::select(DB::raw("SUM(reparacoes.valor) as sum"))
        ->join('formulario', 'reparacoes.matricula', '=', 'formulario.matricula')
        ->orderBy("reparacoes.matricula")
        ->groupBy(DB::raw("reparacoes.matricula"))
        ->get();

        return view('admin.page', ['data' => $data]);
    }
 <tbody>
                        @foreach ($data as $item)
                        <tr>
                            <td>{{ $item->matricula }}</td>
                            <td>{{ $item->sum }}</td>
                            <td>{{ $item->mes_ano }}</td>
                        </tr>
                        @endforeach     
                    </tbody>

i don't even put on the select the mes_ano because the sum it's not working i got two repairs on one matricula that summing up gives 30 in value, and the other matricula that have reparacao has just one of 10 when i apply the join in controller, it shows up as the first matricula having a sum of 420. thanks for whoever helps me doing this join query sum. is there a possible way of doing this?

Tray2

3 years ago

Level 73

You are only selecting one column.

You need to select all the ones you want to display, also sum is not a good column alias, since it's a reserveed word in SQL. You should use total instead.

To save yourself from some headaches, use English for table and column names.

peterpan26

3 years ago

Level 1

im having trouble selecting the two columns SQLSTATE[42S02]: Base table or view not found: 1051 Unknown table 'laravel.formulario'

$data = Reparacoes::select("reparacoes.*","formulario.*", DB::raw('sum(valor) as sum', 'mes_ano'))
        
        ->groupBy("reparacoes.matricula")
    
        ->having('valor', '>', 0)
    
        ->get();
    
        return view('admin.formulario.relatorio', compact('data'));

also if i type count or total in where i got sum i get error aswell

Tray2

3 years ago

Best Answer

Level 73

@peterpan26 You use a join like you did before, but with a select as well.

DB::table('books')
    ->join('formats', 'books.format_id', '=', 'formats.id')
    ->select('books.*', 'formats.format')
    ->get();

Or with Eloquent

Book::with('formats')->get();

You can read more here

https://tray2.se/posts/database-design-part-2

1 like

peterpan26

3 years ago

Level 1

thank you very much tray it worked as you said.

Please or to participate in this conversation.