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
Advertisement
Answer
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()
Working with Pivot tables If you need to retrieve extra data from the pivot table.