CODEWITHSUNDEEP
phplaraveleloquentrelationship
odd_duck
odd_duck

Reputation: 4101

Laravel Eloquent Relationship Across 3 Tables

I have the following database table setup in an existing Laravel project I have started working on:

sports
    id 
    name

lines
    id 
    sport_id - Foreign Key (lines_sport_id_foreign)
    bet_id - Foreign Key (lines_bet_id_foreign)

bets
    id
    stake

With relationships set up like so:

Sport.php

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

Line.php

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

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

Bet.php

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

Now I need to be able to create a relationship between Sport and Bet. Specifically to

  1. Get all bets on a sport (sport->bets)
  2. And then also be able to sort the sports by the most staked (from the stake column on bets)

I have tried using Laravel's hasManyThrough method like so within Sport

public function bets()
{
    return $this->hasManyThrough('App\Bet', 'App\Line');
}

But understandably I am getting a response of

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'bets.line_id' in 'on clause' (SQL: select `bets`.*, `lines`.`sport_id` as `laravel_through_key` from `bets` inner join `lines` on `lines`.`id` = `bets`.`line_id` where `lines`.`sport_id` = 1)

If I was to call App\Sport::find(1)->bets as this expects the link between lines and bets on bets table, not the lines table as it currently is (I am unable to change the existing structure)

Is there anyway to get all bets on a sport along the lines of $sport->bets

And then secondly with that set up, then sort all sports by the stake column on the bets table? (ideally along the lines of $sport->bets->orderBy('stake', 'desc')

Is this possible?

Upvotes: 1

Views: 86

Answers (2)

Makdous
Makdous

Reputation: 1433

There're two ways you can do this:

  1. Eager loading:
    where you can load the relationship bet() between models line and bet & you can make it exclusive to a certain sport with just a where condtition

    return Line::with('bet')->where('sport_id',$sport_id)->get()

    $sport_id: is the desired sport.

    The downside to this is the extra records of Line and the result cannot be ordered.

  2. Join Using QueryBuilder:
    Do a simple join between lines and bets with the same condition as before and include the order stake

    DB::table('lines')
    ->join('bets', 'lines.bet_id', '=', 'bet_id')
    ->select('lines.sport_id', 'bets.*')
    ->where('lines.sport_id',$sport_id)
    ->orderBy('bets.stake')
    ->get();

    A Little messy, But Here you can get the desired result (all bets on a sport).

Upvotes: 1

Norgul
Norgul

Reputation: 4783

Maybe you could do something like:

public function bets()
{
    return Bet::where('id', $this->lines->pluck('bet_id'));
}

Upvotes: 0

Related Questions