Get all keys without translations-CodePudding

I have this entity :

class Keyword extends Model
{
    use HasFactory;

    protected $table = "keywords";

    protected $fillable = [
        'key',
        'description',
        'project_page_id'
    ];

    public function keywordsTranslated()
    {
        return $this->hasMany(KeywordTranslated::class);
    }

    public function projectPage()
    {
        return $this->belongsTo(ProjectPage::class)->withDefault();
    }
  }

And this one :

class KeywordTranslated extends Model
{
    use HasFactory;

    protected $table = "keywords_translated";

    protected $fillable = [
        'translation',
        'language_id',
        'keyword_id'
    ];

    public function keyword()
    {
        return $this->belongsTo(Keyword::class)->withDefault();
    }

    public function language()
    {
        return $this->belongsTo(Language::class)->withDefault();
    }
}

And I want to get all keywords that don't have a keywordTranslated for a specific language.

I tried like this :

public function searchTranslatedKeys(
    int $projectPageId,
    int $curentPage,
    int $languageId,
    string $search = null
) {
    $query = $this->model->newQuery();
    $query->where('project_page_id', $projectPageId);
    if ($search) {
        $query->where('key', 'LIKE', "%{$search}%");
    }
    $query->doesntHave('keywordsTranslated');
    $query->orderBy('id', 'DESC');

    if ($search) {
        return $query->get();
    }

    return $query->get()->forPage($curentPage, 20);
}

But in this case my return is empty.

For example, in keywords I have :

id          key                description
1           addItem            description for this item
2           deleteItem         description for this item 
3           editItem           description for this item

In kewordTranslated :

translation             keyword_id                     language_id
Add item                1                               1
Add item new            1                               2
Delete item             2                               1

I want to get all keys without language 2, for previous example I should received 2 keys with id = 2 & 3, because these keys don't have a kewordTranslated for this language. If language id will be 1 I should get only keyword with id = 3, because this keyword didn't have a kewordTranslated for this language

CodePudding user response：

you can use whereDoesntHave

it limit your results based on the absence of a relationship:

instead of:

 $query->doesntHave('keywordsTranslated');

you can use:

 $query->whereDoesntHave('keywordsTranslated',function ($query) use ($languageId) {
            $query->where('language_id','=',$languageId);
        });