CODEWITHSUNDEEP
laraveleloquent
Pieter VDE
Pieter VDE

Reputation: 2235

Laravel Eloquent complex join statement

My database structure is the following:

Relation blogger <-> Topics

So I've got a Bloggers table (sorry for the typo in the image), which has 3 topic fields, all 3 are foreign keys to the topics' table id.

I made a Blogger view front-end with a simple table showing all blogger columns. At the moment, the id's of the topics are being shown instead of the names though.

How can I change this? I've already tried the following in my Controller, but that just creates duplicates in the view with different values in the main_topic column.

$bloggers= DB::table('bloggers')
            ->join('topics', function ($join) {
                $join
                    ->on('bloggers.main_topic', '=', 'topics.id')
                    ->orOn('bloggers.subtopic1', '=', 'topics.id')
                    ->orOn('bloggers.subtopic2', '=', 'topics.id');
            })
            ->select('bloggers.*', 'topics.name as main_topic')
            ->get();

return view('pages.bloggers', compact('bloggers'));

Upvotes: 1

Views: 559

Answers (3)

Pieter VDE
Pieter VDE

Reputation: 2235

The answer to my specific question was a little different from the answers given, so I'll post it here in case anyone needs it in the future.

So I've got 3 tables: bloggers, topics and a pivot table called blogger_topic. bloggers contains 3 foreign keys (integers) to topics called main_topic, subtopic1, subtopic2. The pivot table contains a blogger_id, and a topic_id.

What I ended up with:

Blogger.php model:

public function mainTopic()
{
    return $this->belongsTo('App\Models\Topic', 'main_topic', 'id');
}
public function subtopicOne()
{
    return $this->belongsTo('App\Models\Topic', 'subtopic1', 'id');
}
public function subtopicTwo()
{
    return $this->belongsTo('App\Models\Topic', 'subtopic2', 'id');
}

Topic.php model:

public function bloggers()
{
    return $this->belongsToMany('App\Models\Blogger');
}

View (blogger.blade.php):

@foreach($bloggers as $blogger)
    {{ $blogger->mainTopic }}
    {{ $blogger->subtopicOne }}
    {{ $blogger->subtopicTwo }}
@endforeach

Upvotes: 1

Harry Loyd
Harry Loyd

Reputation: 429

If you cant change your database, you could use group_concat on the results. Not sure if it will work but something like:

DB::table('bloggers')
            ->join('topics', function ($join) {
                $join
                    ->on('bloggers.main_topic', '=', 'topics.id')
                    ->orOn('bloggers.subtopic1', '=', 'topics.id')
                    ->orOn('bloggers.subtopic2', '=', 'topics.id');
            })
            ->groupby('bloggers.id')
            ->select('bloggers.*', 'topics.name as main_topic', DB::raw('group_concat(topics.name)'))
            ->get();

Upvotes: 0

Min Thura
Min Thura

Reputation: 21

I think you should change your database tables if possible as below (sample link), by creating a pivot table. And then I think you should make relationships in Blogger and Topic model files with many-to-many. Then it would be very easy to fetch all related topics. For eg,

$topics = $blogger->topics;

Here you can reference the sample db table designs

I kindly recommend you to use models in Laravel coz they'll make your life more simple. Hope this help.

Upvotes: 0

Related Questions