Laravel: How to use multiple pivot table relationships

I'm new to defining relationships and frameworks all together, I'm just used to raw SQL. Did my homework (google + Laravel documentation) but I think I'm just not understanding it properly.

Heres is the relevant information: User Table:

Table: Users
id       - int (auto increment)
username - varchar

Challenges Table:

Table: Challenges
id    - int (auto increment)
name  - varchar

User_challenge_links

Table User_challenge_links
id                          - int (auto increment)
user_id                     - int
challenge_sub_categories_id - int

Challenge_sub_categories

Table Challenge_sub_categories
id                - int (auto increment)
category_id       -
sub_category_id   -
challenge_id      -

So my goal.. user->challenges.

class User extends Eloquent
{
    protected $table = "users";

    public function challenges() {
        // get user-> challenges
    }
}

The relationships:

  • A user has many User_challenge_links
  • A User_challenge_link has a challenge_sub_categories_id (thus a challenge_sub_category)
  • A challenge_id from challenges_sub_categories matches a challenge in the challenges table

Any help, even pointing me in the right direction will be much appreciated!

Edit: example data: Users Data

Users
id    name
1     "Sjaak"
2     "Henk"

Categories Data id name 1 "Foo" 2 "Bar"

Sub_categories Data id name 1 "SubFoo" 2 "SubBar"

Challenges Data id name 1 "Swing dat Foo" 2 "Bar all the things" 3 "Foo The Bars"

Challenge_sub_categories Data

id   category_id    sub_category_id    challenge_id
1    1              1                  1
2    2              1                  1
3    1              2                  2
4    2              1                  3
5    2              2                  2

User_challenge_links Data

id     user_id      Challenge_sub_categories_id
1      1            1
2      1            3
3      2            2
4      2            3
5      2            4

Dataflow: A user can create categories or use existing ones and link challenges to them (existing or new). However, a user can also choose to use a subcategory, which he then links to a category and link challenges to that instead. So, a category is mandatory, but a sub_category isn't. If however a sub_category is used (again.. existing or new) the challenge will be connected to that subcategory.

Note: A subcategory CAN be connected to multiple categories

category - House
    sub_category - Cleaning
         Challenge - getting special soap

category - Car
    sub_category - Cleaning
        Challenge - getting special soap

category - Showering
    Challenge - getting special soap

These are some possible situations

Answers


This setup should get you going. I tried to keep the naming as simple as possible.

users
    id
    username

challenge_user
    user_id
    challenge_id

challenges
    id
    name
    topic_id      
    category_id

topics
    id
    name

categories
    id
    name

Defining your Eloquent Models

class User extends Eloquent {
    public function challenges() {
        return $this->belongsToMany('Challenge');
    }
}

class Challenge extends Eloquent {
    public function users() {
        return $this->belongsToMany('User');
    }
    public function topic() {
        return $this->belongsTo('Topic');
    }
    public function category() {
        return $this->belongsTo('Category');
    }
}

class Topic extends Eloquent {
    public function challenges() {
        return $this->hasMany('Challenge');
    }
}

class Category extends Eloquent {
    public function challenges() {
        return $this->hasMany('Challenge');
    }
}

Using your Eloquent Models ... just some exmaples of what you can do.

// Collection of all Challenges by Topic name
Topic::with('challenges')->whereName($topic_name)->first()->challenges;

// Collection of all Challenges by Category name
Category::with('challenges')->whereName($category_name)->first()->challenges;

// Collection of all Users by Challenge id
Challenge::with('users')->find($challenge_id)->users;

// Collection of Users with atleast 2 Challenges
User::has('challenges', '>', 1)->get();

// Attach Challenge to User
$user = User::find($id);
$user->challenges()->attach($challenge_id);

// Assign a Topic to a Challenge
$challenge = Challenge::find($challenge_id);
$topic     = Topic::find($topic_id);

$challenge->topic()->associate($topic);
$challenge->save();

References and suggested reading:

Laravel Eloquent Relationships belongsTo belongsToMany hasMany

Querying relations Model::has()

Eager Loading Model::with()

Dynamic Properties for Accessing Relations Resolve $model->relationship

Inserting related Models attach() associate()

Query Scopes

Working with Pivot tables If you need to retrieve extra data from the pivot table.


Need Your Help

Codeigniter session bugging out with ajax calls

jquery ajax codeigniter session cookies

My CodeIgniter app uses the session library and saves data to the DB.

ORM supporting immutable classes

java hibernate orm scala types

Which ORM supports a domain model of immutable types?