CODEWITHSUNDEEP
phplaravellaravel-5filter
DoubleA
DoubleA

Reputation: 746

Laravel - pass filter to a model

I need some help here. Essentially, there are two tables (tblplayers & tblmatches). Not every player exists in tblmatches. My controller has this code:

use App\Model\Players;
use App\Model\Matches;
use Illuminate\Support\Facades\DB;

class PlayersController extends Controller
{
public function index(Request $request) {
$players = Players::select('*');

I would like to change my select statement above so that it only returns players that also exist in tblmatches (where tblmatches.P1_ID = tblplayers.ID).

What am I doing wrong below?

$players = Players::addSelect(DB::raw('(SELECT * from tblmatches where (P1_ID = ID))'));

Should I be changing the model instead? Thanks for your help.

Upvotes: 1

Views: 229

Answers (3)

Ian Medina
Ian Medina

Reputation: 24

Check this link. https://laravel.com/docs/5.7/queries 

 protected $table = 'tblplayers';
   public function fetchPlayers ($data) {
    $players =  DB::table($this->table)
   // you can filter it by Boolean expression using where 
    ->where('status', '<>', 1)
   // you can group by
    ->groupBy('status')
    ->get();
    return $players;
  }

Upvotes: 0

Kamil Kiełczewski
Kamil Kiełczewski

Reputation: 92377

Add one to many relation (details here) to your player model (and probably also to your Matches model)

public function matches()
{
    return $this->hasMany('App\Model\Matches');
}

And query it by

$players = Players::has('matches')->get();

Upvotes: 4

Mozammil
Mozammil

Reputation: 8750

You should definitely set up a relationship between the two tables. It would make cases like this, so much easier to handle.

However, what you are looking for is essentially a WHERE EXISTS instead. So, something like the following should do the trick. 

$players = Players::whereExists(function ($query) {
        $query->select(DB::raw(1))
            ->from('tblmatches')
            ->whereRaw('tblmatches.player_id = tblplayers.id');
    })
    ->get();

I made an assumption that you have those two fields in the whereRaw(), but you should change it accordingly. 

whereRaw('tblmatches.player_id = tblplayers.id');

However, definitely take a look at relationships :)

Upvotes: 1

Related Questions