Ad

Laravel Eloquent Many To Many Through Other Table With Name Instead Id And Get Additional Data

- 1 answer

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!

Ad

Answer

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();
 }
Ad
source: stackoverflow.com
Ad