Mark Mark - 2 months ago 19
MySQL Question

Laravel 4.2 Many-to-many relationship : Can't read from pivot table

I was trying to apply Jeffrey Way's many-to-many relationships tutorial into my private messaging app but I got stuck. I'm trying to get 2 conversations,

haha
and
hehe
which are associated to a user. However, Laravel gives me the error :

Column not found: 1054 Unknown column 'Conversations.user_id' in 'where clause' (SQL: select * from `Conversations` where `Conversations`.`user_id` = 1)


I have these data in my conversations table :

+---------+----------+
| conv_id | name |
+---------+----------+
| 1 | haha |
| 2 | hehe |
+---------+----------+


In my user_conversations table :

+----+-------------+--------+
| id | conv_id | user_id|
+----+-------------+--------+
| 1 | 1 | 1 |
| 2 | 2 | 1 |
+----+-------------+--------+


1. I've tried : In controllers :

User :
return $this->belongsToMany('User','id');


Conversatons :
return $this->hasMany('Conversations','conv_id');


but the result I got was :
haha
instead of
haha
and
hehe


2. I've also tried :

User :
return $this->belongsToMany('User','user_conversations');


Conversatons :
return $this->hasMany('Conversations','user_conversations');


but laravel returned me the following error :

Column not found: 1054 Unknown column 'Conversations.user_conversations' in 'where clause' (SQL: select * from `Conversations` where `Conversations`.`user_conversations` = 1)


I'm still relatively new in Laravel so I might be making some silly mistakes.




here are my codes :

MODEL

Conversations

class Conversations extends Eloquent {

protected $table = 'Conversations';

protected $fillable = array('name');

public function users(){
return $this->belongsToMany('User');
}

}


User

<?php

use Illuminate\Auth\UserInterface;
use Illuminate\Auth\Reminders\RemindableInterface;

class User extends Eloquent implements UserInterface, RemindableInterface {

....

public function conversations(){
return $this->hasMany('Conversations');
}
}


CONTROLLERS

Conversations Controller

public function create()
{
$loginuser = User::find(Auth::user()->id);
$conversations = $loginuser->conversations;
return View::make('msgsystem.Conversations.Conversations',array('conversations'=>$conversations));
}


MIGRATIONS (in function up())

user

Schema::create('users',function($table)
{
$table->increments('id');
$table->string('email')->unique();
$table->string('password',100);
$table->string('name',150);
$table->string('usertype',50);
$table->boolean('block');
$table->string('remember_token',100);
$table->timestamp('lastlogin_at');
$table->timestamps();
$table->softDeletes();
});


conversations

Schema::create('Conversations', function(Blueprint $table)
{
$table->increments('conv_id')->index();
$table->string('name',100);
$table->timestamps();
});


user_conversations

Schema::create('user_conversations', function(Blueprint $table)
{
$table->increments('id')->unsigned();
$table->integer('conversation_id')->unsigned()->index();
$table->foreign('conversation_id')->references('conv_id')->on('conversations')->onDelete('cascade');
$table->integer('user_id')->unsigned()->index();
$table->foreign('user_id')->references('id')->on('users')->onDelete('cascade');
$table->timestamps();
});





Bonus points for improvement of code. Thank you very much!

Answer

In your example of a many to many relationship a user can have many conversations, where the conversations can also be shared by many users.

In this case we need to use the belongsToMany() method for both the relationship on the user model, and the inverse on the conversations model.

class User 
{
    public function conversations()
    {
        return $this->belongsToMany('Conversations');
    }
}



class Conversations 
{
    public function users()
    {
        return $this->belongsToMany('User');
    }
}

If you need to use a different name for your pivot table, or override the associated keys, you can pass them in as optional arguments.

return $this->belongsToMany(
   'Conversations',          // related_table
   'user_conversations',     // pivot_table
   'user_id',                // key_1
   'conversations_id'        // key_2
);
Comments