Ambiguous field in sql query-CodePudding

i'm getting the following error when trying to filter a query by ID:

Error message

My query:

 $film=film::wherehas('actor', function($q) use($actor_id){
        $q->where('actor_id','=', $actor_id);
      })->get();

model Film:

class film extends Model{
    protected $table = "film";  
    protected $primaryKey = 'film_id';
    public $timestamps = false;
    use HasFactory;
    protected $sql=['film_id', 'title', 'length'];
}

Relationship between tables

CodePudding user response：

Just change your query to be like:

$film=film::wherehas('actor', function($q) use($actor_id){
    $q->where('film_actor.actor_id','=', $actor_id);
})->get();

CodePudding user response：

Your query doesn't know which actor_id column you're targeting in that where() clause. Append the table name, like:

$q->where('film_actor.actor_id', '=', $actorId);
// or `actors.actor_id`, not sure which is being expected here.

Or considering changing your actors table's actor_id to just id. Generally, you don't append the table name to id for the primary key. Same with films.film_id; that can just be id.