Procedure to retrieve concerts that has a name

concerts_query.sql

/* 
* This will select all the concerts where at least 
* a position matches some value
*/
SELECT concert_id AS id 
FROM positions p 
WHERE p.content LIKE '%co%' 
GROUP BY concert_id


/*
* This will join the concerts and their positions
*/
SELECT qcs.id as concert, p.id, p.content, p.votes 
FROM (
	SELECT concert_id AS id 
	FROM positions p 
	WHERE p.content LIKE '%co%' 
	GROUP BY concert_id
) 
AS qcs 
JOIN positions p
ON p.concert_id = qcs.id


/*
* This will join the concerts and their positions
* And then the positions with their supports
* Also, this only selects the desired values to keeo going with the query
*/
SELECT qcs.id as concert_id, p.id as position_id, p.votes as position_votes, IF(s.votes_in_favor <=> null, 0, s.votes_in_favor) as votes_up, IF(s.votes_not_in_favor <=> null, 0, s.votes_not_in_favor) as votes_down     
FROM (
	SELECT concert_id AS id 
	FROM positions p 
	WHERE p.content LIKE '%co%' 
	GROUP BY concert_id
) 
AS qcs 
JOIN positions p
ON p.concert_id = qcs.id
LEFT JOIN supports s
ON s.position_id = p.id


/*
* This will sum the votes_up+votes_down of a support grouped by the support id
*/
SELECT q.concert_id as concert_id, q.position_id as position_id, q.position_votes as position_votes, SUM(q.votes_up + q.votes_down) as support_votes
FROM ( 
	SELECT qcs.id AS concert_id, p.id AS position_id, p.votes AS position_votes, IF(s.votes_in_favor <=> null, 0, s.votes_in_favor) AS votes_up, IF(s.votes_not_in_favor <=> null, 0, s.votes_not_in_favor) AS votes_down     
	FROM (
		SELECT concert_id AS id 
		FROM positions p 
		WHERE p.content LIKE '%co%' 
		GROUP BY concert_id
	) 
	AS qcs 
	JOIN positions p
	ON p.concert_id = qcs.id
	LEFT JOIN supports s
	ON s.position_id = p.id
) AS q
GROUP BY q.position_id


/*
* Then we need to sum the position_votes + the support votes grouped by the concert id.
*/
SELECT v.concert_id as concert_id, SUM(v.position_votes + v.support_votes) as votes
FROM (
	SELECT q.concert_id as concert_id, q.position_id as position_id, q.position_votes as position_votes, SUM(q.votes_up + q.votes_down) as support_votes
	FROM ( 
		SELECT qcs.id AS concert_id, p.id AS position_id, p.votes AS position_votes, IF(s.votes_in_favor <=> null, 0, s.votes_in_favor) AS votes_up, IF(s.votes_not_in_favor <=> null, 0, s.votes_not_in_favor) AS votes_down     
		FROM (
			SELECT concert_id AS id 
			FROM positions p 
			WHERE p.content LIKE '%co%' 
			GROUP BY concert_id
		) 
		AS qcs 
		JOIN positions p
		ON p.concert_id = qcs.id
		LEFT JOIN supports s
		ON s.position_id = p.id
	) AS q
	GROUP BY q.position_id
) as v
GROUP BY v.concert_id


/*
* Then we select the correct concerts and then order them
* by tracking and creation date
*/
SELECT cn.id, cn.title, cn.type, cn.status FROM (
	SELECT v.concert_id as concert_id, SUM(v.position_votes + v.support_votes) as votes
	FROM (
		SELECT q.concert_id as concert_id, q.position_id as position_id, q.position_votes as position_votes, SUM(q.votes_up + q.votes_down) as support_votes
		FROM ( 
			SELECT qcs.id AS concert_id, p.id AS position_id, p.votes AS position_votes, IF(s.votes_in_favor <=> null, 0, s.votes_in_favor) AS votes_up, IF(s.votes_not_in_favor <=> null, 0, s.votes_not_in_favor) AS votes_down     
			FROM (
				SELECT concert_id AS id 
				FROM positions p 
				WHERE p.content LIKE '%%' 
				GROUP BY concert_id
			) 
			AS qcs 
			JOIN positions p
			ON p.concert_id = qcs.id
			LEFT JOIN supports s
			ON s.position_id = p.id
		) AS q
		GROUP BY q.concert_id, q.position_id, q.position_votes
	) as v
	GROUP BY v.concert_id
) as r
JOIN concerts cn on cn.id = r.concert_id
ORDER BY r.votes DESC, cn.created_at DESC