How to get related data from DB (mysql) in one array element?

Question

MySQL

Guys, good afternoon!
To be honest, I don’t even know how to correctly formulate a question in order to google it.
In general, the situation is as follows.
There are 3 tables
1) doc_table
2) types
3) docs_types
1st table with documents 2nd table with document
types
3rd table is the connection of these 2
Here is my query to the database

$dataDb = DB::table('doc_table')->join('docs_types', function($join){
      $join->on('docs_types.doc_id', '=', 'id')->where('id', '=', 1);
    })->where('status', 1)->get();

And here is the result

And here is what is underlined in red, I would like it to be in one type_id field
roughly speaking
[type_id] => [1, 2]
How to form such a request? Pure mysql or using the Laravel query builder, it doesn't matter at the moment
Thank you all in advance!)

Answer 1

start with this, it might help

SELECT dta.id, dta.doc_name, dta.doc_title, dta.link, dta.status, GROUP_CONCAT(DISTINCT dty.doc_id ORDER BY dty.doc_id ASC SEPARATOR ', ') AS type_id
FROM doc_table dta
INNER JOIN docs_types dty ON dta.id=dty.doc_id
GROUP BY dta.id;

or

SELECT dta.id, dta.doc_name, dta.doc_title, dta.link, dta.status, GROUP_CONCAT(DISTINCT t.type_name ORDER BY t.id ASC SEPARATOR ', ') AS type_name
FROM doc_table dta
INNER JOIN docs_types dty ON dta.id=dty.doc_id
INNER JOIN types t ON t.id=dty.type_id
GROUP BY dta.id;

Answer 2

Use groupBy('id')