Using Query Builder and MySql to get categories and sub – categories

  database, laravel, mysql, php, query-builder

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.
enter image description here

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.

Something like this –
enter image description here

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')
            ->selectRaw("DISTINCT UPPER(LTRIM(`type`)) as category")
            ->where('deleted', 0)
            ->orderBy('category', 'asc')

Please, what is the best way to achieve this?

Source: Laravel

Leave a Reply