Моя персональная страница

 

json -> sql




var json = {
	"select": {
		"id": "id",
		"name": "user_name",
		"age": "user_age"
	},
	"from": {
		"table": "users",
		"as": "user"
	},
	"join": [
		{
			"type": "left join",
			"table": "schools",
			"as": "school",
			"on": ["or",
				["=", "name", ":name"],
				["=", "user.school_id", "school.id"]
			]
		},
		{
			"type": "left join",
			"table": "schools",
			"as": "school",
			"on": ["and",
				["=", "user.school_id", "school.id"],
				["or",
					["=", "user.school_id", "school.id"],
					["and",
						["=", "user.school_id", "school.id"],
						["or",
							["=", "user.school_id", "school.id"],
							["=", "user.school_id", "school.id"],
							["=", "user.school_id", ":id"]
						],
						["=", "user.school_id", "school.id"]
					],
					["=", "user.school_id", "school.id"]
				],
				["=", "user.school_id", "school.id"]
			]
		}
	],
	"where": ["and",
		["=", "name", ":name"],
		["=", "user.school_id", "school.id"],
		["or",
			["<>", "user.id", "user.school_id"],
			["<>", "user.name", ":name"]
		]
	], 
	"group": ["user.name", "school.name"],
	"order": {
		"user.name": "asc",
		"school.name": "desc"
	},
	"limit": 5,
	"offset": 10,
	"params": {
		":name": "vasya"
	}
};

function buildWhere (where, params) {
	var j = where.shift().toUpperCase();
	if ('OR' === j || 'AND' === j) {
		return '(' + where.map(buildWhere).join(' ' + j + ' ') + ')';
	} else {
		return where.join(' ' + j + ' ');
	}
};

var sql = '';
/** select */
var select = [];
for (var field in json.select) {
	select.push(json.select[field] + ' AS ' + field);
}
sql += 'SELECT ' + select.join(', ');
/** from */
sql += ' FROM ' + json.from.table 
	+ ' AS '  + json.from.as;
/** join */
sql += json.join.map(function(join){
	return join.type.toUpperCase() + ' ' + join.table 
		+ ' AS ' + join.as 
		+ ' ON ' + buildWhere(join.on);
}).join(' ');
/** where */
sql += ' WHERE ' + buildWhere(json.where);
/** group */
sql += ' GROUP BY ' + json.group.join(', ');
/** order */
var order = [];
for (var field in json.order) {
	order.push(field + ' ' + json.order[field].toUpperCase());
}
sql += ' ORDER BY ' + order.join(', ');
/** limit */
sql += ' LIMIT ' + json.limit;
/** offset */
sql += ' OFFSET ' + json.offset;


console.log(sql);


Обновлен 09 сен 2016. Создан 28 авг 2016



  Комментарии       
Имя или Email


При указании email на него будут отправляться ответы
Как имя будет использована первая часть email до @
Сам email нигде не отображается!
Зарегистрируйтесь, чтобы писать под своим ником