laravel search across multiple tables

Question

I have 3 models in Laravel: Contracter, Contractor_Areas & Contractor_Trade_Type

The Contractor can have trade types (plumbing,electric etc) and areas where they work by postcode.

So I need to select all contractors with a certain trade and postcode.

So the code needs to do something like the following for a Contractor_Trade_Type id (16 in this case) and a postal area SS here and give me matching contractors for the id and postcode.

But it says:

trade_type_id column not found

$con = App\Contractor::with('Contractor_Trade_Types')->where('trade_type_id' , 16 )->with( 'contractor_areas' )->where('contractor_areas.postcode','ss')->first();
print_r( $con );

The models are as follows:

class Contractor  extends Model
{
    protected $table = 'Contractors';

    public $id;
    public $company;
    public $contact;
    public $landline;
    public $mobile;
    public $email;
    public $website;
    public $area;
    public $rate;
    public $address;
    public $gas_safe_reg_no;
    public $banned;
    public $ace_trades;
    public $created_at;
    public $updated_at;


    /*
        MODEL RELATIONSHIPS!!
    */
    // define one-to-one relationship between Contractors AND User
    public function user()  {
            return $this->belongsTo('App\User');
    }
    // Contractor connects on one-to-many with Contractor_Trade_Types.
    public function Contractor_Trade_Types()  {
            return $this->hasMany( 'App\Contractor_Trade_Types' );
    }   
    // Contractor connects on one-to-many with Contractor_Areas.
    public function Contractor_Areas()  {
            return $this->hasMany( 'App\Contractor_Areas' );
    }




    /**
    *       Get the trade types for a contractor.
    *       some contractors have more than one trade
    *       e.g. building and carpentry.
    */
    public function comments()  {
            return $this->hasMany('App\Contractor_Trade_Types');
    }


}




namespace App;

use Illuminate\Database\Eloquent\Model;



class Contractor_Trade_Types  extends Model
{
    protected $table = 'Contractor_Trade_Types';

    public $contractor_id;
    public $trade_id;


    // define relationship between Contractor_Trade_Types AND Contractor
    public function Contractor()    {
            return $this->belongsTo(' App\Contractor');
    }   



}


namespace App;

use Illuminate\Database\Eloquent\Model;



class Contractor_Areas  extends Model
{
    protected $table = 'Contractor_Areas';

    public $contractor_id;
    public $postcode;


    // define relationship between Contractor_Areas  AND Contractor
    public function Contractor()    {
            return $this->belongsTo(' App\Contractor');
    }   



}

user_6449040 · Answer

From what I can see, you are trying to chain the with() and where() clauses in order to search within the relationships.

You can add sub queries by using a closure within the with() query method:

$con = App\Contractor::with(['contractor_trade_types' => function ($query) {
        $query->where('id', 16);
    }])
    ->with(['contractor_areas' => function($query) {
        $query->where('id', $area_id);
    }])
    ->first();

print_r($con);

Additionally, these checks can be added to a query scope to use around your application:

$con = App\Contractor::withTradeType(16)->withArea('ss')->first();

Contractor model (e.g. App\Contractor.php)

public function scopeWithTradeType($query, $trade_type_id)
{
    return $query->with(['contractor_trade_types' => function ($q) {
        $q->where('id', $trade_type_id);
    }]);
}

public function scopeWithArea($query, $area_id)
{
    return $query->with(['contractor_areas' => function($q) {
        $q->where('id', 'ss');
    }]);
}

laravel search across multiple tables

1 Answers

Contractor model (e.g. App\Contractor.php)