Select all rows from pivot table

Question

I'm having a bit of trouble selecting pivot table rows with Eloquent on Slim Framework. I have the following relations:

tb_bet (id, ...)
tb_fixture (id, ...)
tb_bet_fixture (bet_id, fixture_id, status)

Models:

Bet.php:

use \Illuminate\Database\Eloquent\Model;

class Bet extends Model {

    protected $connection = 'client';
    protected $table = 'tb_bet';
    public $timestamps = false;

    public function fixtures() {
        return $this->belongsToMany('Fixture')->withPivot('status');
    }
}

Fixture.php:

use \Illuminate\Database\Eloquent\Model;

    class Fixture extends Model {

        protected $connection = 'client';
        protected $table = 'tb_fixture';
        public $timestamps = false;

        public function bets() {
            return $this->belongsToMany('Bet')->withPivot('status');
        }
    }

I'm able to query all bets from a fixture and all fixtures from a bet but what I really need is to do this: select * from tb_bet_fixture where bet_id = 1. So I have a list of all fixture status for a given bet. Kinda like this:

bet:{
  'id': 1,
  'pivot': [
      {bet_id: 1, fixture_id: 2, status: true},
      {bet_id: 1, fixture_id: 3, status: false},
      {bet_id: 1, fixture_id: 4, status: true}
  ]
}

I thought of use raw query but whenever I try to declare use Illuminate\Database\Capsule\Manager as DB; on my controller and use $users = DB::select('select * from tb_users'); as a test I get a Database [default] not configured. error and I can't figure how to access the '$capsule' variable which contains the current connection(yes, I use multiple connections to different db's) and is defined on a Middleware. Anyway, I'd appreciate any help with this.

Answer 1

Option 1: Create a model

If i were to use a pivot as a model . I would create a Model for pivot table. So that you can directly query the table. Like this.

use \Illuminate\Database\Eloquent\Model;
use App\Bet;

class BetFixture extends Model 
{
    protected $table =‘tb_bet_fixture’;

    public function bets() 
    {
        return $this->hasMany(Bet::class);
    }
} 

This would make it easier and clearer than querying through as u are selecting from a pivot directly like this

select * from tb_bet_fixture where bet_id = 1. 

Option 2: Select using fixture, with specific bet

If you want to select fixture with a specific bet through fixtures you can query like this.

Fixture::with(‘bets’ => function($query) use ($betId) {
    $query->where(‘tb_bet_fixture.bet_id,$betId);
});