使用Query Builder和MySql获取类别和子类别 [英] Using Query Builder and MySql to get categories and sub - categories

问题描述

美好的一天.我正在设计下表中的 API.我正在使用的数据库是一个设计不佳的旧数据库.表格如下所示.

Good day. I am designing an API from the table below. The database I am working with is a poorly designed legacy database. The form is shown below.

现在，标题属于一个类别.比如Title，B，C，D都在Cat B下.所以，想返回两个节点.节点 1 - 返回所有类别，节点 2 - 返回类别下所有标题的数组.

Now,the titles are under a category. Like, Title, B, C, D are under Cat B. So, want to return two nodes. Node 1 - returning all the categories, and node 2 - returning an array of all the titles under the category.

类似这样的——

我正在使用 Laravel 查询生成器.我可以使用下面的代码获取类别.现在的问题是在它下面获取标题.

I am using Laravel Query Builder. I can get the categories with the code below. The problem now is with getting the titles under it.

$rows = DB::connection('mysql3')
            ->table('tbl_form')
            ->selectRaw("DISTINCT UPPER(LTRIM(`type`)) as category")
            ->where('deleted', 0)
            ->orderBy('category', 'asc')
            ->get();

请问，实现这一目标的最佳方法是什么?

Please, what is the best way to achieve this?

推荐答案

我会选择 雄辩的模型

class Category extends Model
{
    protected $table = 'tbl_form'; // 'posts'

    public function posts()
    {
        return $this->hasMany(Post::class, 'category', 'category')->select(['title']);
    }
}

class Post extends Model
{
    protected $table = 'tbl_form'; // 'posts'

    public function category()
    {
        return $this->belongsTo(Category::class, 'category', 'category');
    }
}

然后像这样检索它:

$categories = Category::all();
dd($categories[0]->posts);

这篇关于使用Query Builder和MySql获取类别和子类别的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！