Query builder CONCAT to JSON?

I am wondering if there is a better way of retrieve related records as json concatenated.

This is an example of the way I am thinking of now:

$query = DB::table('zoos')
        ->select([
            'zoos.name as zooName',
            DB::raw('CONCAT("[", GROUP_CONCAT(CONCAT('{id:"', species.id, '", name:"', species.name, '"}')
            SEPARATOR ", "), "]") AS species)
        ->join('specie_zoo AS sz', 'zoos.id', '=', 'sz.zoo_id')
        ->join('species', 'sz.specie_id', '=', 'species.id')
        ->groupBy('zoos.id')
        ->get();

What do you think? Thanks.

Level 2

Best Answer

Level 53

@GastonUy It looks fine. However, why do you want to do it in the db, instead of eg. using relations?

GastonUy

11 years ago

Level 2

I did it this way in order to reduce amount of queries @JarekTkaczyk .

JarekTkaczyk

11 years ago

Level 53

@GastonUy I see, but do you really need to reduce these queries? If you only want to improve performance, while there's no need for it, you may waste your time during development instead. Your call.

1 like

Please or to participate in this conversation.