HUGE query, need help with adding a new sort OR amending...

[glennn.php]

OK, here's the query i'm working with, first of all:

 

(select a.*, a.user as usr, (select answer from answers where question = 'decision' and user = usr limit 1) as decision,

(coalesce((select answer from answers where question = 'rater1' and user = usr limit 1),0) + coalesce((select answer from answers where question = 'rater2' and user = usr limit 1),0) + coalesce((select answer from answers where question = 'rater3' and user = usr limit 1),0) + coalesce((select answer from answers where question = 'rater4' and user = usr limit 1),0)) as coal,

(coalesce((select answer from answers where question = 'rater1' and user = usr limit 1),0) + coalesce((select answer from answers where question = 'rater2' and user = usr limit 1),0) + coalesce((select answer from answers where question = 'rater3' and user = usr limit 1),0) + coalesce((select answer from answers where question = 'rater4' and user = usr limit 1),0)) / (((select answer from answers where question = 'rater1' and user = usr and answer != 0 limit 1) is not null) + ((select answer from answers where question = 'rater2' and user = usr and answer != 0 limit 1) is not null) + ((select answer from answers where question = 'rater3' and user = usr and answer != 0 limit 1) is not null) + ((select answer from answers where question = 'rater4' and user = usr and answer != 0 limit 1) is not null)) as total, (select answer from answers where question = 'rater1' and user = usr limit 1) as rater1, (select answer from answers where question = 'rater2' and user = usr limit 1) as rater2, (select answer from answers where question = 'rater3' and user = usr limit 1) as rater3, (select answer from answers where question = 'rater4' and user = usr limit 1) as rater4 from answers a INNER JOIN (SELECT user FROM answers WHERE app_id = '24550e5c73136d5081f76dea807fd35b' and question = 'date_completed') b ON a.user = b.user WHERE a.question = 'status' and answer = '0' group by user order by decision desc limit 1000) UNION (select a.*,a.user as usr,(select answer from answers where question = 'decision' and user = usr limit 1) as decision,(coalesce((select answer from answers where question = 'rater1' and user = usr limit 1),0) + coalesce((select answer from answers where question = 'rater2' and user = usr limit 1),0) + coalesce((select answer from answers where question = 'rater3' and user = usr limit 1),0) + coalesce((select answer from answers where question = 'rater4' and user = usr limit 1),0)) as coal,

(coalesce((select answer from answers where question = 'rater1' and user = usr limit 1),0) + coalesce((select answer from answers where question = 'rater2' and user = usr limit 1),0) + coalesce((select answer from answers where question = 'rater3' and user = usr limit 1),0) + coalesce((select answer from answers where question = 'rater4' and user = usr limit 1),0)) / (((select answer from answers where question = 'rater1' and user = usr and answer != 0 limit 1) is not null) + ((select answer from answers where question = 'rater2' and user = usr and answer != 0 limit 1) is not null) + ((select answer from answers where question = 'rater3' and user = usr and answer != 0 limit 1) is not null) + ((select answer from answers where question = 'rater4' and user = usr and answer != 0 limit 1) is not null)) as total, (select answer from answers where question = 'rater1' and user = usr limit 1) as rater1, (select answer from answers where question = 'rater2' and user = usr limit 1) as rater2, (select answer from answers where question = 'rater3' and user = usr limit 1) as rater3, (select answer from answers where question = 'rater4' and user = usr limit 1) as rater4 from answers a INNER JOIN (SELECT user FROM answers WHERE app_id = '24550e5c73136d5081f76dea807fd35b' and question = 'date_completed') b ON a.user = b.user WHERE a.question = 'status' and answer = '1' group by user order by decision desc limit 1000) UNION (select a.*,a.user as usr,(select answer from answers where question = 'decision' and user = usr limit 1) as decision,(coalesce((select answer from answers where question = 'rater1' and user = usr limit 1),0) + coalesce((select answer from answers where question = 'rater2' and user = usr limit 1),0) + coalesce((select answer from answers where question = 'rater3' and user = usr limit 1),0) + coalesce((select answer from answers where question = 'rater4' and user = usr limit 1),0)) as coal,

(coalesce((select answer from answers where question = 'rater1' and user = usr limit 1),0) + coalesce((select answer from answers where question = 'rater2' and user = usr limit 1),0) + coalesce((select answer from answers where question = 'rater3' and user = usr limit 1),0) + coalesce((select answer from answers where question = 'rater4' and user = usr limit 1),0)) / (((select answer from answers where question = 'rater1' and user = usr and answer != 0 limit 1) is not null) + ((select answer from answers where question = 'rater2' and user = usr and answer != 0 limit 1) is not null) + ((select answer from answers where question = 'rater3' and user = usr and answer != 0 limit 1) is not null) + ((select answer from answers where question = 'rater4' and user = usr and answer != 0 limit 1) is not null)) as total, (select answer from answers where question = 'rater1' and user = usr limit 1) as rater1, (select answer from answers where question = 'rater2' and user = usr limit 1) as rater2, (select answer from answers where question = 'rater3' and user = usr limit 1) as rater3, (select answer from answers where question = 'rater4' and user = usr limit 1) as rater4 from answers a INNER JOIN (SELECT user FROM answers WHERE app_id = '24550e5c73136d5081f76dea807fd35b' and question = 'date_completed') b ON a.user = b.user WHERE a.question = 'status' and answer = '2' group by user order by decision desc limit 1000) UNION (select a.*,a.user as usr,(select answer from answers where question = 'decision' and user = usr limit 1) as decision,(coalesce((select answer from answers where question = 'rater1' and user = usr limit 1),0) + coalesce((select answer from answers where question = 'rater2' and user = usr limit 1),0) + coalesce((select answer from answers where question = 'rater3' and user = usr limit 1),0) + coalesce((select answer from answers where question = 'rater4' and user = usr limit 1),0)) as coal,

(coalesce((select answer from answers where question = 'rater1' and user = usr limit 1),0) + coalesce((select answer from answers where question = 'rater2' and user = usr limit 1),0) + coalesce((select answer from answers where question = 'rater3' and user = usr limit 1),0) + coalesce((select answer from answers where question = 'rater4' and user = usr limit 1),0)) / (((select answer from answers where question = 'rater1' and user = usr and answer != 0 limit 1) is not null) + ((select answer from answers where question = 'rater2' and user = usr and answer != 0 limit 1) is not null) + ((select answer from answers where question = 'rater3' and user = usr and answer != 0 limit 1) is not null) + ((select answer from answers where question = 'rater4' and user = usr and answer != 0 limit 1) is not null)) as total, (select answer from answers where question = 'rater1' and user = usr limit 1) as rater1, (select answer from answers where question = 'rater2' and user = usr limit 1) as rater2, (select answer from answers where question = 'rater3' and user = usr limit 1) as rater3, (select answer from answers where question = 'rater4' and user = usr limit 1) as rater4 from answers a INNER JOIN (SELECT user FROM answers WHERE app_id = '24550e5c73136d5081f76dea807fd35b' and question = 'date_completed') b ON a.user = b.user WHERE a.question = 'status' and answer = '3' group by user order by decision desc limit 1000) order by answer asc, decision desc, total desc

 

the table is configured like this:

app_id | question | answer | user

24550e5c73136d5081f76dea807fd35b | status | 0 | 654a6518d9fas9dad5sdfa8a61ga651

24550e5c73136d5081f76dea807fd35b | name | Bob Smith | 654a6518d9fas9dad5sdfa8a61ga651

24550e5c73136d5081f76dea807fd35b | email | bob@email.com | 654a6518d9fas9dad5sdfa8a61ga651

24550e5c73136d5081f76dea807fd35b | state | NY | 654a6518d9fas9dad5sdfa8a61ga651

24550e5c73136d5081f76dea807fd35b | country | Uganda | 654a6518d9fas9dad5sdfa8a61ga651

24550e5c73136d5081f76dea807fd35b | etc | etc | 654a6518d9fas9dad5sdfa8a61ga651

24550e5c73136d5081f76dea807fd35b | etc | etc | 654a6518d9fas9dad5sdfa8a61ga651

 

 

approximately 30 records for each user TIMES 500. (this is the weirdest database config. i've ever seen - i don't even know if this was smart or not - doesn't seem to be, as there are more than 250 thousand records in this particular table already - but these queries are kicking my ass).

 

 

 

so the client has asked if i can now add a sort by country option. I'm hoping someone can help me rewrite this query to where it will sort by the answer to the question which is country AS WELL as keeping the status grouped (0, then 1, then 2...).  i can do the rest if someone could kindly help me with this...? pretty please...?

 

I'll be very very grateful if someone could show me what needs to be done to include this ability within this query...

 

thanks so much, folks.

 

 

 

if it matters, this is how the query is coded:

 

$statusna = "";
			for ($j=0;$j<=3;$j++)
			{
				$statusna .= "(select a.*,a.user as usr,(select answer from answers where question = 'decision' and user = usr limit 1) as decision,(";

				for ($i=1;$i<=$raters;$i++)
				{
					$statusna .= "coalesce((select answer from answers where question = 'rater$i' and user = usr limit 1),0)";
					if ($i<$raters)
						$statusna .= " + ";
				}	
				$statusna .= ") as coal,(";

				for ($i=1;$i<=$raters;$i++)
				{
					$statusna .= "coalesce((select answer from answers where question = 'rater$i' and user = usr limit 1),0)";
					if ($i<$raters)
						$statusna .= " + ";
				}
				$statusna .= ") / (";
				for ($i=1;$i<=$raters;$i++)
				{
					$statusna .= "((select answer from answers where question = 'rater$i' and user = usr and answer != 0 limit 1) is not null)";
					if ($i<$raters)
						$statusna .= " + ";
				}
				$statusna .= ") as total, ";

				for ($i=1;$i<=$raters;$i++)
				{
					$statusna .= "(select answer from answers where question = 'rater$i' and user = usr limit 1) as rater$i";
					if ($i<$raters)
						$statusna .= ", ";
				}
				$statusna .= " from answers a INNER JOIN (SELECT user FROM answers WHERE app_id = '$_GET[app_id]' and question = 'date_completed') b ON 
		a.user = b.user WHERE a.question = 'status' and answer = '$j' group by user order by decision desc limit 1000)";


				// ORIG >> $statusna .= " from answers where app_id = '$_GET[app_id]' and question = 'status' and answer = '$j' group by user order by decision desc limit 1000)";

				if ($j<3)
					$statusna .= " UNION ";

			}

			 $qry = "$statusna order by answer asc, decision desc, total desc";

[glennn.php]

someone, please help...?

[mikosiko]

that query is really a crazy one no doubt, re-write it will need a deep understanding of the data available and the final objectives (outputs), and I don't believe that someone here will invest the time and effort to do that for you.

 

regarding to the sort... what have you tried already?... did you add the new sort condition in this line in your code?

$qry = "$statusna order by answer asc, decision desc, total desc";

 

maybe writing that line as

$qry = "$statusna order by country, answer asc, decision desc, total desc";

 

did you test that?