Laravel - PDOException::("SQLSTATE[42S02]: Base table or view not found: 1146 Table 'vesti-CodePudding

I'm new to Laravel.

I'm trying to access some data from within a partial view.

I tried to put my query inside the boot method of AppServiceProvider, but Im getting this error:

PDOException::("SQLSTATE[42S02]: Base table or view not found: 1146 Table 'vestibulare.app\models\telefone' doesn't exist")

I can't even run php artisan serve.

Why is Laravel assuming my model's name is in the singular?

Here are my models:

class Usuario extends Model
{
    protected $table = 'usuarios';
    public $timestamps = false;

    protected $fillable = [
        'nome'
    ];

    public function telefones()
    {
        return $this->hasMany(Telefone::class, 'id_usuario');
    }
}

class Telefone extends Model
{
    protected $table = 'telefones';
    public $timestamps = false;

    protected $casts = [
        'id_usuario' => 'int'
    ];

    protected $fillable = [
        'id_usuario',
        'numero'
    ];

    public function usuario()
    {
        return $this->belongsTo(Usuario::class, 'id_usuario');
    }
}

Here's the boot method inside app\Providers\AppServiceProvider class:

public function boot()
    {
        $data = Usuario::join(Telefone::class, "usuarios.id", "=", "telefones.id_usuario")
            ->select(
                "usuarios.id as u.id",
                "usuarios.nome as nome",
                "telefones.numero as numero",
                DB::raw("COUNT(numero) AS numero_count")
            )
            ->groupBy("nome")
            ->orderBy("nome")
            ->get();

        view()->with(["data" => $data]);
    }

Initially, the query inside boot method was inside my controller's index method, like so:

class ContactListController extends Controller
{

    public function index()
    {
        // $usuarios = Usuario::all();
        // $telefones = Telefone::all();
        $data = Usuario::join(Telefone::class, "usuarios.id", "=", "telefones.id_usuario")
            ->select(
                "usuarios.id as u.id",
                "usuarios.nome as nome",
                "telefones.numero as numero",
                DB::raw("COUNT(numero) AS numero_count")
            )
            ->groupBy("nome")
            ->orderBy("nome")
            ->get();

        return view("index", compact("data"));
    }
    {...}
}

Why am I getting this error?

How can I access the data I want from within the partial data?

Here are my views:

index.blade.php

<html>
@include("header")
@include("search_contact")
@include("contact_list")
@include("footer")
</html>

contact_list.blade.php <- I want to access data from here

<div >

    <table id="myTable" >
        <thead >
            <th >Name</th>
            <th >Nº of Phones</th>
            <th >Phone</th>
        </thead>

        <tbody id="tableBody">

            @foreach ($data as $item)
            <tr>
                <!-- Test -->
                <td>{{ $item }}</td>
            </tr>
            @endforeach

        </tbody>
    </table>
</div>

I've been reading the docs, watching tutorials and searching for similar questions for over 6 hours. Please help me.

Thank you in advance.

Edit:

I've tried changing the query to the following:

$data = Usuario::join("telefones", "usuarios.id", "=", "telefones.id_usuario")
            ->select(
                "usuarios.id as u.id",
                "usuarios.nome as nome",
                "telefones.numero as numero",
                DB::raw("COUNT(telefones.numero) AS numero_count")
            )
            ->groupBy("nome")
            ->orderBy("nome")
            ->get();

Where insteaad of using Telefone::class I'm now using telefones. I got a different error:

SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'vestibulare.usuarios.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by (SQL: select `usuarios`.`id` as `u.id`, `usuarios`.`nome` as `nome`, `telefones`.`numero` as `numero`, COUNT(telefones.numero) AS numero_count from `usuarios` inner join `telefones` on `usuarios`.`id` = `telefones`.`id_usuario` group by `nome` order by `nome` asc)

Edit 2:

I've changed the following line inside my database.php config file:

'mysql' => [
    ...
    'strict' => true,
    ...
]

To:

'mysql' => [
    ...
    'strict' => false,
    ...
]

Now I'm not getting the above error anymore, but there's something wrong with my query because it's coming back empty.

CodePudding user response：

Maybe do you try with DB?

\DB::table('usuarios')->join('telefones', ....

CodePudding user response：

This is what I will do

class Usuario extends Model { 
    public $timestamps = false;
    protected $table = 'usuarios';    

    protected $with=['telefonos'];

    protected $fillable = [
        'nome'
    ];

    public function telefones()
    {
        return $this->hasMany(Telefone::class);
    }
}


class Telefone extends Model {
    protected $table = 'telefones';
    public $timestamps = false;


    protected $fillable = [
        'id_usuario',
        'numero'
    ];

    public function usuario()
    {
        return $this->belongsTo(Usuario::class);
    }
}

//Here's the boot method inside app\Providers\AppServiceProvider class:

// do not use this for querys to database
    public function boot()
    {
        
    }

class ContactListController extends Controller {

    public function index()
    {
        $usuarios = Usuario::all();
        return view("index", $usuarios);
    }
    {...}
}

// And your view 

<div >

    <table id="myTable" >
        <thead >
            <th >Name</th>
            <th >Nº of Phones</th>
            <th >Phone</th>
        </thead>

        <tbody id="tableBody">

            @foreach ($usuarios as $usuario)
            <tr>
                @php

                $telefono = $usuario->tefono ?? 'This user has no telephone';
                
                @endphp

                <td>Nome: {{$usuario->nome}} Telef: {{$telefono}} </td>
            </tr>
            @endforeach

        </tbody>
    </table>
</div>

And finally return mysql values to original state.