I have two main tables, Posts and Categories which should have a many-to-many relationship. I know you can just do it with a table called category_post but thats not how the structure I'm able to work with is set up.
It is like this: Posts have a one-to-one relation with questions and questions have categories through the posts.
My database looks like this:
Schema::create('posts', function (Blueprint $table) {
$table->bigIncrements('id');
$table->string('title');
$table->text('contet');
}
Schema::create('post_questions', function (Blueprint $table) {
$table->bigIncrements('id');
$table->string('post_id');
$table->integer('views');
$table->integer('answers');
}
Schema::create('categories', function (Blueprint $table) {
$table->bigIncrements('id');
$table->string('name')->unique();
}
Schema::create('post_question_categories', function (Blueprint $table) {
$table->bigIncrements('id');
$table->integer('post_question_id');
$table->string('category_name');
$table->string('additional_info');
}
As you can see, my pivot table does neither have a post_id nor a category_id but a post_question_id and a category_name which is unique in categories table. But the should-be state is a many-to-many with posts and categories.
Questions also have a One-To-Many relation to query additional_infos
This are my models:
class Post extends Model
{
public function question()
{
return $this->hasOne('App\PostQuestion')->with('categories');
}
}
class Question extends Model
{
public function post()
{
return $this->belongsTo('App\Post');
}
public function categories()
{
return $this->hasMany('App\PostQuestionCategory');
}
}
class PostQuestionCategory extends Model
{
public function question()
{
return $this->belongsTo('App\PostQuestion')->with('post');
}
}
class Category extends Model
{
public function posts()
{
}
}
So this is where the fun begins: I want to query all posts from one category. I already tried and got this by 3 approaches:
1.
public function posts()
{
$name = $this->name;
return Post::whereHas('question', function ($question) use ($name) {
$question->whereHas('categories', function ($categories) use ($name) {
$categories->where('name', $name);
});
})->get();
}
2.
public function posts()
{
return PostQuestionCategory::with('question')->where('name', $this->name)->get();
}
3. Adding post_id and category_id to the table
public function posts()
{
return $this->belongsToMany('App\Post', 'post_question_categories');
}
The third is not a real option sine I don't want to manipulate the existing database. In the second I dont really get the post object as a relation but just as a plain query result.
Also when I do in category/show.blade.php
@foreach ($category->posts() as $post)
{{ $post->question->categories }}
@endforeach
I dont have access to many attributes because of that.
So I prefer using the first option.
The thing is:
I only want to get the categories which are the current I'm currently on. With that query I'll get indeed all posts which have that, but in the foreach I'll also get all additional categories. Furthermore I want to get the additional_info attribute in this instance.
Like this: I'm on route categories.show/1 (name = cat1)
and my database is like
posts
id title
1 a
2 b
3 c
4 d
post_questions
id post_id views answers
1 1 12 2
2 2 7 0
3 3 456 6
4 4 124 11
post_question_categories:
id question_id additional_info category_name
1 1 ai1 cat 1
2 1 ai2 cat 2
3 3 ai1 cat 1
4 4 ai1 cat 3
categories
id name
1 cat 1
2 cat 2
3 cat 3
My ideal output would be:
posts
[
{id: 1, title: 'a', question: {id: 1, views: 12, answers: 2, categories: [{id: 1, additional_info: 'ai1', name: 'cat1'}, // Not id 2 since im on show/1]},
{id: 3, title: 'c', question: {id: 3, views: 456, answers: 6, categories: [{id: 1, additional_info: 'ai1', name: 'cat1'}}
]
I hope this is not too confusing and someone can help me!
I think your second one query already solved your problem. Just do some modification to it like this.
public function posts()
{
return PostQuestionCategory::with('question.post')->where('category_name', $this->name)->get();
}
just add post together with question to be able to include it in the returned collection.
or if you have a relationship also with categories then you just add also the category relationship, like this one
public function posts()
{
return PostQuestionCategory::with('question.post', 'category')->where('category_name', $this->name)->get();
}