MySQLEncoder

MySQLEncoder.php

<?php

class MySQLEncoder {

	public static function listOfObjects($attrs, $table, $as = '_', $intvals = []) {
		$intvals = array_flip($intvals);
		return 'CONCAT("[", GROUP_CONCAT(CONCAT("{",' . implode(', "," ,', array_map(function($a) use ($table) {
			$is_int = isset($intvals[$a]) || $a === 'id';
			$mysql_a = $table . '.' . $a;
			return '"\"' . $a . '\"' . ':' . '", ' . 
			(!$is_int ? ('IF (' . $mysql_a . ' IS NULL, "", "\"")') : '""') . ', ' . 
			(($a === 'id' ? $mysql_a : 'COALESCE(' . $mysql_a . ', "null")')) . ", " .  
			(!$is_int ? ('IF (' . $mysql_a . ' IS NULL, "", "\"")') : '""');
		}, $attrs)) . ', "}")), "]") AS ' . $as;
	}

	public static function object($attrs, $table, $as = '_', $intvals = []) {
		$intvals = array_flip($intvals);
		return 'CONCAT("{",' . implode(', "," ,', array_map(function($a) use ($table) {
			$is_int = isset($intvals[$a]) || $a === 'id';
			$mysql_a = $table . '.' . $a;
			return '"\"' . $a . '\"' . ':' . '", ' . 
			(!$is_int ? ('IF (' . $mysql_a . ' IS NULL, "", "\"")') : '""') . ', ' . 
			(($a === 'id' ? $mysql_a : 'COALESCE(' . $mysql_a . ', "null")')) . ", " .  
			(!$is_int ? ('IF (' . $mysql_a . ' IS NULL, "", "\"")') : '""');
		}, $attrs)) . ', "}") AS ' . $as;
	}

}

/*

Usage:
You have 2 tables in your SQL database

users                    devices
---------------        --------------------------
| id    title |       | id    user_id     token |
---------------        --------------------------
| 1     test  |       | A       1         XXX   |
---------------        --------------------------
| 2     fuffa |       | B       1         XYZ   |
---------------        --------------------------


To obtain the user with all devices you can simply

$user = DB::select('SELECT U.id, U.title, ' . MySQLEncoder::listOfObjects(['id','token'], 'D', 'devices') . ' FROM users LEFT JOIN devices ON U.id = D.user_id');
$user->devices = json_decode($user->devices);

*/