Error when consulting with eloquent in laravel 5.6

2

I'm trying to show data from a table that is related and I'm using Laravael Eloquent for the query.

but it generates the sgt error:

  

SQLSTATE [42S22]: Column not found: 1054 Unknown column 'a.name' in       'where clause' (SQL: select count (*) as aggregate from articles inner       join 'categories

the tables are migrated as follows:

Articles

<?php

use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;

class CreateArticulosTable extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('articulos', function (Blueprint $table) {
            $table->increments('id');
            $table->unsignedInteger('categoria_id');
            $table->string('codigo', 40);
            $table->string('nombre', 45);
            $table->string('descripcion', 45)->nullable();
            $table->Integer('stock');
            $table->string('imagen', 50)->nullable();
            $table->string('estado', 20);
            $table->timestamps();

            $table->foreign('categoria_id')->references('id')->on('categorias')->onDelete('no action')->onUpdate('no action');
        });
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::dropIfExists('articulos');
    }
}

Categories

<?php

use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;

class CreateCategoriasTable extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('categorias', function (Blueprint $table) {
            $table->increments('id');
            $table->string('nombre', 45);
            $table->string('descripcion', 45)->nullable();
            $table->tinyInteger('condicion');
            $table->timestamps();
        });
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::dropIfExists('categorias');
    }
}

Controller Articles

<?php

namespace App\Http\Controllers;

use App\Articulo;

use App\Http\Requests\ArticuloFormRequest;

use Illuminate\Http\Request;
use Illuminate\Support\Facades\Input;
use Illuminate\Support\Facades\Redirect;

class ArticuloController extends Controller
{
    /**
     * Display a listing of the resource.
     *
     * @return \Illuminate\Http\Response
     */
    public function index(Request $request)
    {
        if ($request) {

            $query = trim($request->get('searchText'));

            $articulos = Articulo::select('articulos as a')
                ->join('categorias as c', 'a.categoria_id', '=', 'c.id')
                ->select('a.id', 'a.nombre', 'a.codigo', 'a.stock', 'c.nombre as categoria', 'a.descripcion', 'a.imagen', 'a.estado')
                //busca en la caja de texto por el nombre o codigo
                ->where('a.nombre', 'LIKE', '%' . $query . '%')
                ->orwhere('a.codigo', 'LIKE', '%' . $query . '%')
                ->orderBy('id', 'desc')
                ->paginate(2)
                ->get();

            return view('bodega.articulo.index', ["articulos" => $articulos, "searchText" => $query]);

        }
    }
}

view index

@extends('layoust.admin')
@section ('contenido')
<div class="row">
    <div class="col-lg-8 col-md-8 col-sm-8 col-xs-12">
        <h3>
            Listado de Articulos
            <a href="articulo/create">
                <button class="btn btn-success">
                    Nuevo
                </button>
            </a>
        </h3>
        <!--se incluye la vista search, que es una barra de busqueda-->
        @include('bodega.articulo.search')
    </div>
</div>
<div class="row">
    <div class="col-lg-12 col-md-12 col-sm-12 col-xs-12">
        <div class="table-responsive">
            <table class="table table-striped table-bordered table-condensed table-hover">
                <thead>
                    <th>
                        Id
                    </th>
                    <th>
                        Nombre
                    </th>
                    <th>
                        Codigo
                    </th>
                    <th>
                        Categoria
                    </th>
                    <th>
                        Stock
                    </th>
                    <th>
                        Imagen
                    </th>
                    <th>
                        Estado
                    </th>
                    <th>
                        Opciones
                    </th>
                </thead>
                <!--Bucle que recorre todas las articulos-->
                @foreach ($articulos as $art)
                <tr>
                    <td>
                        {{ $art->id_articulo}}
                    </td>
                    <td>
                        {{ $art->nombre}}
                    </td>
                    <td>
                        {{ $art->codigo}}
                    </td>
                    <td>
                        {{ $art->categoria}}
                    </td>
                    <td>
                        {{ $art->stock}}
                    </td>
                    <td>

                    </td>
                    <td>
                        {{ $art->estado}}
                    </td>
                </tr>
                @endforeach
            </table>
        </div>
    </div>
</div>
@endsection

Search view

<!--Abrir formulario,  y lo redicionara a la vista index de articulo y el parametro de busqueda va hacer con
    el parametro get,codigo con laravel colletive-->
{!! Form::open(array('url'=>'bodega/articulo','method'=>'GET', 'autocomplete'=>'off', 'role'=>'search'))!!}
<div class="form-group">
    <div class="input-group">
        <input type="text" class="form-control" name="searchText" placeholder="Buscar.." value="{{$searchText}}">
        <span class="input-group-btn">
            <button type="submit" class="btn btn-primary">Buscar</button>           
        </span>
    </div>
</div>
{{Form::close()}}
    
asked by Juanzu 27.09.2018 в 01:04
source

3 answers

2

I tell you the following:

First your query should look like this

$query = trim($request->get('searchText'));

            $articulos = Articulo::from('articulos as a')
                ->select('a.id', 'a.nombre', 'a.codigo', 'a.stock', 
                         'c.nombre as categoria', 'a.descripcion', 
                         'a.imagen', 'a.estado')
                ->join('categorias as c', 'a.categoria_id', '=', 'c.id')
                //busca en la caja de texto por el nombre o codigo
                ->where('a.nombre', 'LIKE', '%' . $query . '%')
                ->orwhere('a.codigo', 'LIKE', '%' . $query . '%')
                ->orderBy('id', 'desc')
                ->paginate(2)
                ->get();

 return view('bodega.articulo.index', ["articulos" => $articulos, "searchText" => $query]);

INDICATIONS

  
  • Your query was not because in the select you can only alias the columns of the table but not the table   complete
  •   
  • Use the from() method, so that just after invoking the Method Articulo you indicate that the% articulos will put the alias of    a
  •   
  • I think you should move the select so that it is just after the from method so that it improves its reading, at least I always   I work my questions as I indicate you
  •   

    Already with the previous observations you should be working

        
    answered by 27.09.2018 / 14:55
    source
    3

    Change:

    Articulo::select('articulos as a')
    

    for

    Articulo::from('articulos as a')
    
        
    answered by 27.09.2018 в 09:07
    2

    If I need to practice more on the queries in eloquent. Now looking at your answers I had an idea to make the query more comfortable, use this:

    $articulos = Articulo::with(['categoria'])->get();
    

    With this query you already send me the relationship I have with the table categories.

        
    answered by 27.09.2018 в 18:08