How to treat an sql command in the Laravel controller?

Question

Good night, you guys. I followed the dompdf tutorial successfully for static texts, now I wanted to make a query in the database to print the results in tables in a pdf and I get this error:

Illuminate\Database\QueryException thrown with message "SQLSTATE[42S22]: Column not found: 1054 Unknown column 'CONDICOES' in 'where clause' (SQL: SELECT i.id, p.descricao, sum(i.valorcusto * i. qtd) custo, sum(i.qtd) quant, sum(i.total) venda, sum(i.total-(i.qtd * i.valorcusto)) lucro, sum((v.desconto/(v.valor+v.desconto))*i.total) valordesconto FROM itemvenda i JOIN venda v on v.id=i.idvenda JOIN produtos p on p.id=i.idproduto WHERE (v.valor+v.desconto)<>0 AND (i.situacao=0) AND v.situacao=0 AND v.data >= 2017-01-01 AND v.data <= 2021-01-01 AND v.tipo <> CONDICOES AND v.tipo <> PENDENTE AND v.tipo <> ORCAMENTO AND v.tipo <> BAIXA AND v.tipo <> RET_EST AND v.tipo <> DEVOLUCAO GROUP BY i.idproduto,p.descricao)"

The query works if it is done directly in SQL BD, but when I try to do it by the controller in the Windows gives error. I wonder what I’m doing wrong, I tried two different codes.

first Controller-

public function generatePDF(){
        $dbusuario = new Funcoes; //instancia o Helper
        $bancousuario = $dbusuario->selecionausuario();
        $data = DB::connection($bancousuario)
              ->select('SELECT i.id, p.descricao, sum(i.valorcusto * i. qtd) custo, 
                     sum(i.qtd) quant, sum(i.total) venda, 
                     sum(i.total-(i.qtd * i.valorcusto)) lucro, 
                     sum((v.desconto/(v.valor+v.desconto))*i.total) valordesconto 
                     FROM itemvenda i 
                        JOIN venda v on v.id=i.idvenda 
                        JOIN produtos p on p.id=i.idproduto 
                     WHERE (v.valor+v.desconto)<>0 AND 
                           (i.situacao=0) AND 
                            v.situacao=0 AND v.data >= 2017-01-01 AND 
                            v.data <= 2021-01-01 AND  
                            v.tipo <> CONDICOES AND 
                            v.tipo <> PENDENTE AND 
                            v.tipo <> ORCAMENTO AND  
                            v.tipo <> BAIXA AND 
                            v.tipo <> RET_EST AND 
                            v.tipo <> DEVOLUCAO 
                      GROUP BY i.idproduto,p.descricao');

        $pdf = PDF::loadView('myPDF', $data);
        return $pdf->stream();
    }

View

@extends('layouts.menus')
@section('content')
<br>
    <div class="row">
        <div class="col">
            <div class="card card-small mb-4">
                <div class="card-header">
                    <h1>Relatórios</h1>     
                </div>
                <div class="card-body">
                    <table>
                      <thead>
                        <tr>
                          <th>ID</th>
                          <th>Descrição</th>
                          <th>Custo</th>
                          <th>Quantidade</th>
                          <th>Venda</th>
                          <th>Lucro</th>
                          <th>Desconto</th>
                        </tr>
                      </thead>
                      <tbody>
                        @foreach($data as $customer)
                          <tr>
                            <td>{{ $customer->id }}</td>
                            <td>{{ $customer->descricao }}</td>
                            <td>{{ $customer->custo }}</td>
                            <td>{{ $customer->quant }}</td>
                            <td>{{ $customer->venda }}</td>
                            <td>{{ $customer->lucro }}</td>
                            <td>{{ $customer->valordesconto }}</td>
                          </tr>
                        @endforeach
                      </tbody>
                    </table>
                </div>
            </div>         
        </div>
    </div>
@endsection

2nd Controller-

$dbusuario = new Funcoes; //instancia o Helper
$bancousuario = $dbusuario->selecionausuario();
$data = DB::connection($bancousuario)->select('SELECT itemvenda.id, produtos.descricao')
        ->sum('(itemvenda.valorcusto * itemvenda. qtd)custo')
        ->sum('(itemvenda.qtd) quant')
        ->sum('itemvenda.total venda')
        ->sum('((itemvenda.total - (itemvenda.qtd * itemvenda.valorcusto)) lucro)')
        ->sum('((venda.desconto/(venda.valor + venda.desconto))*itemvenda.total) valordesconto')
        ->from('itemvenda')
        ->join('venda ON venda.id = itemvenda.idvenda')
        ->join('produtos produtos ON produtos.id = itemvenda.idproduto')
        ->where('(venda.valor + venda.desconto) <> 0')
        ->orWhere('itemvenda.situacao = 0')
        ->orWhere('venda.situacao = 0')
        ->orWhere('venda.data >= 2017-01-01')
        ->orWhere('venda.data <= 2021-01-01')
        ->orWhere('venda.tipo <> CONDICOES')
        ->orWhere('venda.tipo <> PENDENTE')
        ->orWhere('venda.tipo <> ORCAMENTO')
        ->orWHere('venda.tipo <> BAIXA')
        ->orWhere('venda.tipo <> RET_EST')
        ->orWhere('venda.tipo <> DEVOLUCAO')
        ->groupBy('itemvenda.idproduto,p.descricao');
        $pdf = PDF::loadView('relatorios.index', $data);
        return $pdf->stream(); //assim você visualiza o arquivo antes de baixar */

In both attempts the error is the same. I searched for sql manipulation by Aravel, tried up to DB::raw, but I don’t know what I’m doing wrong. Thank you in advance.

Answer 1

Like CONDITIONS, PENDING, etc... are strings so they should be in quotes in the query so you can choose to ->orWhere("venda.tipo <> 'CONDICOES'") or ->orWhere('venda.tipo', '<>', 'CONDICOES')