laravel many to many fetching data

Question

I am trying to build a menu according to user roles using many to many relationship. laravel is my first php framework and i am facing this issue

Unhandled Exception

Message:

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'user_role.created_at' in 'field list'

SQL: SELECT `roles`.*, `user_role`.`id` AS `pivot_id`, `user_role`.`created_at` AS `pivot_created_at`, `user_role`.`updated_at` AS `pivot_updated_at`, `user_role`.`user_id` AS `pivot_user_id`, `user_role`.`role_id` AS `pivot_role_id` FROM `roles` INNER JOIN `user_role` ON `roles`.`id` = `user_role`.`role_id` WHERE `user_role`.`user_id` = ?

Bindings: array (
  0 => 1,
)

user migration:

<?php
class Users {

public function up()
{
    Schema::create('users', function($table){
        $table->engine = 'InnoDB';
        $table->increments('id');
        $table->string('username', 128);
        $table->string('password', 128);
        $table->string('firstname', 128);
        $table->string('lastname', 128);
        $table->date('dob');
        $table->string('phone')->nullable();
        $table->text('image')->nullable();
        $table->timestamps();
    });

    DB::table('users')->insert(array(
        'username' => 'admin',
        'password' => Hash::make('admin'),
        'firstname' => 'asdf',
        'lastname' => 'zxcv',
        'dob' => '1990-02-23',
        'phone' => '935735367'
    ));

}

function down()
{
    Schema::drop('users');
}

}

roles migration:

<?php

class Role {

    public function up()
    {
        Schema::create('roles', function($table){
            $table->engine = 'InnoDB';
            $table->increments('id');
            $table->string('lable', 60);
            $table->string('url', 128)->default("#");
            $table->integer('parent')->default("0");
            $table->integer('level')->default("0");
            $table->integer('sort')->default("0");
            $table->integer('published')->default("0");
        });

    }

    public function down()
    {
        Schema::drop('roles');
    }

}

role_user

<?php

class Access {

    public function up()
    {
        Schema::create('role_user', function($table){
            $table->engine = 'InnoDB';
            $table->increments('id');
            $table->integer('user_id')->unsigned();
            $table->integer('role_id')->unsigned();
            $table->foreign('user_id')->references('id')->on('users');
            $table->foreign('role_id')->references('id')->on('roles');
        });


    }

    public function down()
    {
        Schema::drop('role_user');
    }

}

user model:

<?php 
class User extends Basemodel{
    public static $table = 'users';
    public static $timestamps = true;
    public static $rules = array(
        'username' => 'required|min:3|alpha',
        'password' => 'required|min:3|alpha'
    );

    public function roles()
    {
        return $this->has_many_and_belongs_to('Role');
    }

    public static function menu(){
        $roles = User::find(1)->roles()->get();
        return $roles;
    }
}

Role Model

<?php 
class Role extends Eloquent{
    public static $table = 'roles';

}

Controller:

<?php

class Home_Controller extends Base_Controller {
public $restful= true;

public function get_index()
{
    return View::make('home.index')
        ->with('title','App Index')
        ->with('menu',User::menu());
}

can someone guide me on what to do ?

Answer 1

It looks like, at first glance, the timestamp columns are missing in the user_role table. If you add the two columns; created_at and updated_at to the table and set them to datetime it should fix it up for you!

Also by the looks of things, your roles table doesn't have these timestamps described above either. You should either add these or set a public static variable in the Role model to state that they aren't there. You can do this by writing public static $timestamps = false