Get Filtered and associated records in Laravel 5.1

- 1 answer

Ad

I have three Database Tables.

CREATE TABLE `tblprojecttype` (
  `ProjectTypeID` int(11) NOT NULL,
  `ProjectType` varchar(30) NOT NULL,
  `Description` varchar(500) NOT NULL,
  `IsActive` tinyint(1) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `tblprojecttypecurrencyprice` (
  `ProjectTypeCurrencyID` int(11) NOT NULL,
  `CurrencyID` int(11) NOT NULL,
  `ProjectTypeID` int(11) NOT NULL,
  `Price` decimal(10,0) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `tblcurrency` (
  `CurrencyID` int(11) NOT NULL,
  `Currency` varchar(100) NOT NULL,
  `IsActive` tinyint(1) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Models

class Currency_Model extends Model
{
    protected $table = "tblcurrency";
    protected $primaryKey = "CurrencyID";
    public $timestamps = false;
}

class ProjectType_Model extends Model
{
    protected $table = "tblprojecttype";
    protected $primaryKey = "ProjectTypeID";
    public $timestamps = false;

    public function projecttypecurrencyprice()
    {
         return $this->hasOne('\App\Models\ProjectTypeCurrencyPrice_Model', 
                                                     "ProjectTypeID");
    }
}

class ProjectTypeCurrencyPrice_Model extends Model
{
    protected $table = "tblprojecttypecurrencyprice";
    protected $primaryKey = "ProjectTypeCurrencyID";
    public $timestamps = false;

    public function Currency()
    {
         return $this->belongsTo('\App\Models\Currency_Model', "CurrencyID");
    }
}

There is CurrencyID relationship and ProjectTypeID relationship

What I am trying ?

In my Laravel 5.1 code, I am trying to achive below sql statement so that I can get projecttypecurrencyprice records for each ProjectType record. Finally it should also show records from currency Table for each projecttypecurrencyprice record

$ProjectTypes = \App\Models\project\ProjectType\ProjectType_Model
                ::with("projecttypecurrencyprice")
                ->with("projecttypecurrencyprice.Currency")
                ->get();

What's the Problem ?

I am getting all Currency records for each projecttypecurrencyprice record. I just want those where currency ID = 1

Ad

Answer

Ad

You need to apply additional criteria when fetching related ** projecttypecurrencyprice** objects. You can do that with whereHas() method. The following code should do the trick:

$ProjectTypes = \App\Models\project\ProjectType\ProjectType_Model
  ::with("projecttypecurrencyprice")
  ->with(["projecttypecurrencyprice.Currency" => function($query) {
    $query->where('CurrencyID', 1);
  }])
  ->get();
Ad
source: stackoverflow.com
Ad