1

I am new to Laravel and need some help with what might be a basic question.

Race horses (can) have many owners. Owners (can) have many race horses. However, owners can buy and sell shares in race horses. At any point in time it must be possible to determine the share holding of each race horse.

My pivot table is defined as follows:

Schema::create('horse_owner', function (Blueprint $table) {
    $table->id();
    $table->foreignId ('horse_id');
    $table->foreignId ('owner_id');
    $table->date ('effective'); // the date when the transaction occurred
    $table->integer ('shares')->default (1); // the number of shares + or -
    $table->timestamps();
});

My Horse model includes a withPivot for effective and shares and I can show these in the Horses view. However, where an owner buys and sells shares, I would like the resultant number of shares to be shown.

The SQL query to find the current owners of a horse on a certain date looks like this:

SELECT `owners`.*, SUM(`horse_owner`.`shares`) AS `share_total`
FROM `owners`
    INNER JOIN `horse_owner` ON `owners`.`id` = `horse_owner`.`owner_id`        
WHERE `horse_owner`.`horse_id` = ? -- some horse
    AND `horse_owner`.`effective` <= ? -- some date
GROUP BY horse_owner.owner_id;

While seeing the individual "transactions" is a necessary function, this share total will be mostly more useful than the individual transactions (e.g. summary reports, billing) and so being able to extract it easily for one or more horses or owners will be useful.

How does one set this up in Laravel and are there any best practice suggestions?

EDIT:

Based on other solutions, I've attempted the following:

return $this->belongsToMany (Owner::class)
        ->selectRaw ("`owners`.*, SUM(`horse_owner`.`shares`) AS `share_total`")
        ->withTimestamps()
        ->groupBy ("horse_owner.owner_id");

However, this results in an error because the query still includes "contains nonaggregated column horse_owner.id which is not functionally dependent on columns in GROUP BY clause".

Philip
  • 3,689
  • 3
  • 24
  • 35
  • you can use `withSum` method along with the relationships – Pradeep Aug 16 '21 at 10:47
  • Thanks for your response, @Pradeep. Can you show me any examples of how to implement this? – Philip Aug 16 '21 at 11:48
  • It seems like it would be much more efficient to have a `transactions` table, rather than trying to store this data in the pivot table. If your pivot table was simply `horse_id`, `user_id`, and `share` (a fixed value updated in code after a transaction) your code would be much more performant. As it is now, you have to do the math every time you want to determine ownership share. – miken32 Aug 16 '21 at 19:09
  • Thank you @miken32 - that is an interesting point. I do know that point-in-time lookups are going to be done more frequently than "current" shareholding (which is really only needed when producing shareholding check sheets). At which point it may not be worth storing the current shareholding at all and simply using a separate transaction table. At this point, though, the pivot and the transactions table have near identical keys (perhaps the 'near' is not near enough) which would suggest from a normalization point of view that they could be unified. – Philip Aug 17 '21 at 05:33

0 Answers0