php - Laravel: How to use multiple pivot table relationships

Question

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

Answer 1

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 examples 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.