CODEWITHSUNDEEP
laravelrelationship
BobbyP
BobbyP

Reputation: 2245

Laravel. How to get relationships where foreign key is an array

I am trying to retrieve database rows with their relationships. However, the local key is an array. Let me explain using an example.

Lets say I have a table of countries and a table of pages. Each country can have many pages. Each page can belong to multiple countries. I do not have the flexibility to change this schema.

pages
+-------------+-----------+
| id | name   | countries |
+-------------+-----------+
| 1  | Page 1 | 1         |
+-------------+-----------+
| 2  | Page 2 | 1,2,3     |
+-------------+-----------+
| 3  | Page 3 | 4,5,6     |
+-------------+-----------+

countries
+----+----------------+
| id | name           | 
+----+----------------+
| 1  | United States  |
+----+----------------+
| 2  | United Kingdom |
+----+----------------+
| 3  | Germany        |
+----+----------------+
| 4  | France         |
+----+----------------+
| 5  | Hong Kong      |
+----+----------------+
| 6  | Thailand       |
+----+----------------+
| 7  | Belgium        |
+----+----------------+
| 8  | Singapore      |
+----+----------------+

My model and controller look something like:

country 

public function pages()
{
    return $this->hasMany(Page::class, 'id', 'countries');
}

MemberController.php

$countries = Country::with('pages')->get();

This is returning all countries, but only Page 1 contains any relationships.

Is there a way to retrieve relationships using a whereIn approach so all three countries will return appropriate pages?

Thanks in advance

Upvotes: 3

Views: 3733

Answers (2)

Charles G
Charles G

Reputation: 1381

Something like this ?

$datas = Pages::where( ##your conditions### )->get()->inArray();
$countries = Countries::pluck('name','id'); // return array of id=>name

foreach($datas as $key=>$data) {

    $c = [];
    foreach(explode(',',$data['countries']) as $country_id) {
        $c[]=$countries[$country_id]; 
        //or 
        $c[]= ['id'=>$country_id,'name'=>$countries[$country_id]];
    }
    $datas[$key]['countries']= $c;
}

Upvotes: 0

Alexey Mezenin
Alexey Mezenin

Reputation: 163768

Since Page can belong to many Countries, you need to create a pivot table called country_page and remove the countries column.

Then define two belongsToMany() relationships in both models:

public function pages()
{
    return $this->belongsToMany(Page::class);
}

If you're not following Laravel naming conventions listed in my repo and you gave the pivot name a custom name, define it too:

public function pages()
{
    return $this->belongsToMany(Page::class, 'custom_pivot_table');
}

Upvotes: 1

Related Questions