In my project I have the essence of the article . Each article may belong to several sections. This is easily accomplished by the many-to-many approach.

Articles id | name Categories id | name Article_categories id | article_id | category_id

But I need to add subsections to the essentials, given that the article may belong to several subsections. In a simple view, this could be implemented with two additional tables.

  Subcategories id | name Article_subcategoires id | article_id | subcategories_id

And now the question. I need to get the data in a "hierarchical" way:

  • get all articles
  • get all articles / specific category / all subcategories
  • get all articles / specific category / specific subcategory
  • get all articles / all categories / specific subcategories

and here my knowledge and practice are no longer lacking, and the dual approach of many-to-many does not work, or rather, the result has been obtained, but is not sure of its correctness and effectiveness in terms of resources.

Bike on laravel 5.3

  ArticleController.php $articles = Article::Published() ->ByCategory($cat) ->BySubcategory($subcat) ->paginate(10); Article.php Model public function scopeByCategory($query, $catRequest){ if($catRequest && $catRequest != 'all' ) { return $query->with('categories') ->whereHas('categories', function ($q) use ($catRequest) { $q->where('id', $catRequest); }); }else{ return $query; } } public function scopeBySubcategory($query, $subcatRequest){ if($subcatRequest && $subcatRequest != 'all' ) { return $query->with('subcategories') ->whereHas('subcategories', function ($q) use ($subcatRequest) { $q->where('id', $subcatRequest); }); }else{ return $query; } }

But I have a persistent feeling that this is not right, and this is a bicycle. Are there any other solutions at the level of database design or data processing in php?

    1 answer 1

     SELECT a.* FROM Articles a -- 1 , Categories c , Article_categories a_c -- 2 , Subcategories s, Article_subcategoires a_s WHERE 1=1 -- 1 AND a.id = a_c.article_id AND a_c.category_id = c.id AND c.name IN (@c_name_csv) -- 2 AND a.id = a_s.article_id AND a_s.subcategories_id = s.id AND s.name IN (@s_name_csv)

    Uncomment 1 if certain categories are needed.

    Uncomment 2 if you need certain subcategories.

    If you want a universal query - uncomment everything and add conditions to something like

     AND ((c.name IN (@c_name_csv)) OR (@c_name_csv = ''))

    Ps. I do not understand why categories and sub categories. According to the description - they are completely independent.

    • thank! In the DB structure, these are separate independent entities. I will try to describe how it looks on the site. For example, the article "Lorem" may belong to the category "A-Category" and "B-Category", as well as belong to the subcategories "a-subcat" and "b-subcat". But users should see it at addresses 1) articles / all 2) articles / a-category / all-subcategory and articles / b-category / all-subcategory 3) articles / a-category / all-subcategory 4) articles / a- category / a-subcat and articles / a-category / b-subcat and so on. Probably a little dullly explaining dull. Just a different DB structure did not occur. - Oleg Kruglov
    • I just tried to google another version of the DB structure, possibly with denormalization - without success, and all the examples in the network are of the same type - Oleg Kruglov
    • @OlegKruglov Then why subcategory? What words have little English? type, class, etc ... - Akina
    • @ akina yes, damn it))), this is a conditional name, in my project it is generally called systems and sections, because the site is medical and the article may belong to organ systems, for example, conditionally, head and abdomen, and sections - anatomy and pathology. Just so as not to clutter up the air, I renamed the entities into more running ones. Another question, at the DB organization level, is this a normal structure? - Oleg Kruglov
    • Yes, normal, cho ... there is an essence, it has two some multi-attributes, independent. - Akina