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

Solution

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.

Comments

[Jarek Tkaczyk]

How's user related to the challenge in your table schema? You have user_id and challenge_sub_categories_id which isn't correct, riht?

[lagbox]

are we missing a categories table ?

[lagbox]

is this Challenge_sub_categories intended to be a sub type of challenge ... or a challenge belongs to a category and that category may have a parent category ?

[Pwnball]

a challenge is always linked to a category and optionally a sub category, but a challenge can be linked to many categories and subcategories hence that link table in between, we need to know what particular instance of that challenge the user owns. the categories and sub_categories table mostly constists of <table> <id> - int (auto increment) <name> - varchar <description> - varchar

[lagbox]

what do you mean by instance of that challenge ?

[Pwnball]

@lagbox I have added a data example to my OP, to clearify the relationships further!

[lagbox]

@Pwnball i want to answer but i dont understand why categories and subcategories are 2 seperate entities ... in my mind something is a subcategory when it has a parent category ... in what you have they are completely seperate until you connect them to the challenge

[Pwnball]

@lagbox I will do my best to supply the information required for you to answer, the effort you're already putting in is fantastic thanks. I have added more info on the bottom of my OP (dataflow) to explain it further and make it more clear why they are seperate entities

[Pwnball]

Thanks alot for your time m8! Still a few challenges to tackle but this helps me a great deal to setup everything the way I want to and understand how to approach things with laravel relationships!

[Sreejith Sasidharan]

How do I get a collection of users in a particular category ?