Query by category_id and locale in laravel

Question

I need to query all articles were given cateogry_id and locale from request

example: all articles where category_id is 2 and locale is "en"

    articles
      id
      language

   article_category
     article_id
     category_id

   categories
     id
     name

what I tried

  private function filterArticleByCategory(&$query, $category_id) {

        if (!is_null($category_id)) {
            $query = $query->whereIn('articles.id', Article::select('article_id')->from('article_category')->where('category_id', $category_id ));
        }
    }


    private function filterArticleByLanguage(&$query, $lang) {
        if(!is_null($lang)) {
//            $array = explode(' ', $lang);
            #arreyVal = array_values($array)[0]
            $query = $query->where('language', $lang);
        }
    }

Answer 1

You can make a method directly in your controller method:

class ArticleController {
    
    // ...
    
    public function getByCategoryAndLanguage(int $categoryId, string $language) {
        return Article::join('article_category', 'article.id', '=', 'article_category.article_id')
            ->where('category_id', '=', $categoryId)
            ->where('language', '=', $language)
            ->get();
    }
}

If you need to have these filters directly available on your Article model, you may want to use local scopes, though it's probably overkill and redundant for such simple where conditions:

class Article extends Model {

    // ...

    public function scopeInCategory($query, int $categoryId) {
        return $query->join('article_category', 'article.id', '=', 'article_category.article_id')
            ->where('category_id', '=', $categoryId);
    }

    public function scopeInLanguage($query, string $language) {
        return $query->where('language', '=', $language);
    }
}

And then in your controller:

class ArticleController {
    
    // ...
    
    public function getByCategoryAndLanguage(int $categoryId, string $language) {
        return Article::inCategory($categoryId)
            ->inLanguage($language)
            ->get();
    }
}

EDIT after your comment:

Then you can have two distinct methods on your controller —one for each filter— quite easily:

class ArticleController {
    
    // ...
    
    public function getByCategory(int $categoryId) {
        return Article::join('article_category', 'article.id', '=', 'article_category.article_id')
            ->where('category_id', '=', $categoryId)
            ->get();
    }

    public function getByLanguage(string $language) {
        return Article::where('language', '=', $language)->get();
    }
}