Level 51
$seller = Seller::find($sellerId)->prices()->select('created_at AS date', 'value')->get();
PS: I just assumed the relationship and table columns.
Aug 22, 2022
5
Level 3
Group by on has many relationship
Hello
I have 2 models 'Seller' & 'Price'. The price model is for a historical table which tracks prices. I'd like to display those in a chart and so I need to group the 'prices' relationship on created at date (on hour level).
Seller -> hasMany -> Price
so I would like to get the following:
[
'dates' => ['2022-08-20', '2022-08-21', '2022-08-22'];
'data' => [
'Seller 1' => [7.90, 7.85, 7.99],
'Seller 2' => [7.79, 7.65, 7.95],
]
]
Level 3
@MohamedTammam Thanks for your reply, but this just selects the colums? I need to group them together on the price model + I need it for a list of Sellers not just 1 model instance.
Level 51
@liamseys Please post the table structures and relationships.
Level 3
@MohamedTammam Prices table:
$table->uuid('id')->primary();
$table->string('product_offer_id');
$table->unsignedDecimal('price');
$table->timestamp('created_at')->index()->nullable();
$table->timestamp('updated_at')->nullable();
$table->foreign('product_offer_id')
->references('id')
->on('product_offers')
->onDelete('cascade');
Product Offer Table:
$table->uuid('id')->primary();
$table->string('product_id');
$table->string('seller_id');
$table->string('country')->index();
$table->timestamps();
$table->unique(['product_id', 'seller_id', 'country'], 'bbc_unique');
$table->foreign('product_id')
->references('id')
->on('products')
->onDelete('cascade');
$table->foreign('seller_id')
->references('id')
->on('sellers')
->onDelete('cascade');
Relationships:
public function prices()
{
return $this->hasMany(Price::class);
}
public function product()
{
return $this->belongsTo(Product::class);
}
public function seller()
{
return $this->belongsTo(Seller::class);
}
Level 51
@liamseys In that case you can fetch all data and do that group by in PHP not in SQL.
$seller = Seller::findOrFail($sellerId);
$prices = $seller->prices()->select('created_at AS date', 'price')->get()->gropupBy('date');
Please or to participate in this conversation.