To pivot or not to pivot

I am fighting a lot with eloquent setup of my tables and models. I need to make a design decision before I continue coding. I have read many articles but I can't find any satisfying answer. Maybe you guys can help me.

I am designing a cryptomonitor website where Users have Trades. The Trades contain information about the purchase date, price and fee. The Trades table has a connection to a Pair table where the coins are stored and where the price is updated every second.

table Users

id
name

table Trades

id
user_id
pair_id
amount
price

table Pairs

id
coin_name

The above structure would assume that this needs a Pivot approach. So I set relation in the User model as:

public function pairs()
    {
        return $this->belongsToMany('App\Pair', 'trades', 'user_id', 'pair_id')
        ->withPivot('amount', 'price')
        ->withTimestamps();
    }

The issue now is when I would like to get all amounts of each trade a user has made I would use:

$user = User::find(auth()->id());
    foreach ($user->pairs as $pair)
    {
        Log::Info($pair->pivot->amount);
    }

No word about Trades. Only Pairs and Users. I would like to have a more approach with Trades.

A user hasMany trades, a trade belongsTo a user. (I hope these are correct).

A trade belongTo pair, a pair belongsToMany trades. (I hope these are correct).

The table setup would stay the same with above approach. Only the relationships will have to change and I cannot use the Pivot methods.

But now I want to have the coin_name of Trade 3 for User 1 and there comes the frustration. I have no clue how to handle this. Are there any examples for this three table approach?

Punksolid

6 years ago

Level 25

Hi @xibel why not both? Let me explain In your User you can have a pairs relationship and in Pair a user relationship, that is useful for showing some information, however as you say sometimes you will need to use something like $trade->user or $trade->pair to do some inspection.

I had a similar issue once when my pivot table over time had more pivot columns and it became its own concept, however I left the initial relationships and it is consistent.

When you use pivot columns sometime you will end asking if a $user->pair->pivot exists or throw an error instead of null also if you keep only the pivot approach in order to get some specifics like Trade 3 for user 1 you will need to use more complex methods like

public function  getUserWhereHasBlabla($whatever)
    $user->whereHas('pairs', function($query)) use($whatever) {
    $query->where('x', $whatever)
    }->first();
});

Is it possible to use some methods like that to give you the access however in the case like you mention that you need some kind of inspection it is useful and quickly to take advantage of the id key of your "pivot" table.

1 like

fitriali

6 years ago

Level 4

Heres a possible approach without using pivot.

Inside User Model:

public function trades ()
{
  return $this->hasMany('App\Trade');
}

Inside Trade Model:

public function user ()
{
  return $this->belongsTo('App\User');
}

public function pair ()
{ 
  return $this->belongsTo('App\Pair');
}

Inside Pair Model:

public function trades ()
{
  return $this->hasMany('App\Trade');
}

To get current user's trade with pairs:

auth()->user()->trades()->with('pair')->get();

You can also consider hasManyThrough relationship: https://laravel.com/docs/6.x/eloquent-relationships#has-many-through

I think this is what you're looking for based on your last sentence: https://laravel.com/docs/6.x/eloquent-relationships#eager-loading

1 like

lara131141

6 years ago

Level 1

Thank you both for your answer. I like the solution from @afitriali more. I have setup those relationships in my models.

The issue I had was how to get to the pair information that is connected to all trades of the auth user. I was able to dump all trades with all pairs but I was not able to get only the pair names. So I ended in using the method toArray() so I can use the dot notation and using foreach with the data_get helper.

To get the pairnames of all trades of the current auth user is as follows:

$trades = auth()->user()->trades()->with('pair')->get()->toArray();

foreach ($trades as $item)
        {
            Log::Info(data_get($item, 'pair.symbol'));
        }

The result:

[2019-10-22 12:10:48] local.INFO: FUNBTC
[2019-10-22 12:10:48] local.INFO: BCNBTC
[2019-10-22 12:10:48] local.INFO: PAXTUSD

Now I am able to access all trade information and all pair information of that trade. Thanks again!

Please or to participate in this conversation.