How to perform has many on group by in laravel?-CodePudding

I have two tables,

buyers

Field	Type
id	bigint unsigned
name	varchar(255)

buyer_zips

Field	Type
id	bigint unsigned
buyer_id	bigint unsigned
zip	varchar(255)

A buyer can have multiple zips. zip in buyer_zips can be duplicate and hence a zip can have multiple buyers. My goal is to get the buyers that belongs to the zip. Example:

buyers

id	name
1	Company 1
2	Company 2
3	Company 3

buyer_zips

id	buyer_id	zip
1	1	90001
2	2	90001
3	3	90001
4	1	90002
5	2	90002

I want the end result to be something like,

[
  {
    "zip": "90001",
    "buyers": [
      {
        "id": 1,
        "name": "Company 1"
      },
      {
        "id": 2,
        "name": "Company 2"
      },

      {
        "id": 3,
        "name": "Company 3"
      }
    ]
  },
  {
    "zip": "90002",
    "buyers": [
      {
        "id": 1,
        "name": "Company 1"
      },
      {
        "id": 2,
        "name": "Company 2"
      }
    ]
  }
]

I did the following,

BuyerZip::groupBy('zip_id')
    ->with('buyers')
    ->get();

And the relation,

// BuyerZip.php

public function buyers()
{
    return $this->hasMany(Buyer::class, 'buyer_id', 'id');
}

How do I get all the buyers belonging to a zip?

CodePudding user response：

As you said, a buyer can have multiple zips && hence a zip can have multiple buyers. This not one-to-many relationship. That's really many-to-many relationship, so you need changing the Model and relationship and using Pivot Table.

Buyer Model:

use App\Models\Zip;
class Buyer extends Model
{
    use HasFactory;
    public function zips() {
        return $this->belongsToMany(Zip::class);
    }
}

Zip Model

use App\Models\Buyer;
class Zip extends Model
{
    use HasFactory;
    public function buyers() {
        return $this->belongsToMany(Buyer::Class);
    }
}

Pivot Table migration:

return new class extends Migration
{
    public function up()
    {
        Schema::create('buyer_zip', function (Blueprint $table) {
            $table->id();
            $table->foreignid('buyer_id')->constrained();
            $table->foreignid('zip_id')->constrained();
            $table->timestamps();
        });
    }

    public function down()
    {
        Schema::dropIfExists('buyer_zip');
    }
};

You can then easily get Zip with related Buyers:

Zip::with('buyers')->get();

CodePudding user response：

Try to remove the groupBy in your builder. Leave it just like this:

BuyerZip::with('buyers')->get()

CodePudding user response：

try this code

BuyerZip::with('buyers')->groupBy('buyer_id')->all()