Re^2: Help with MySQL SELECT into multidimensional array

[btongeorge]

It might be possible, and I did start down that road, but the query got pretty scary. I think I'm more comfortable with multiple queries, but maybe I'm wrong. Here are all the queries, the first generates the client list, the rest populate the integer fields:

SELECT c2.id, c2.name as 'client'
  FROM client c2
  WHERE level = 50 and status = 1;

SELECT c2.id, c2.name as 'client', count(*) as 'ext' 
  FROM client c1, client c2
  WHERE c1.level=100 AND c1.status=1 AND ext.type = "phone" 
   AND c1.parent_client_id = c2.id
  GROUP BY c1.parent_client_id ORDER BY c2.name;

SELECT c2,id, c2.name as 'client', count(*) as 'vm'
  FROM extension ext, client c1, client c2, extension_prefs exp
  WHERE ext.client_id = c1.id AND c1.level=100
   AND exp.param="voicemail"
   AND exp.value=1 AND c1.parent_client_id = c2.id
   AND exp.extension_id = ext.id
 GROUP BY c2.name;

SELECT c2.id, c2.name as 'client', count(*) as 'ivr'
   FROM extension ext, client c1, client c2
   WHERE ext.client_id = c1.id AND ext.type = 'ivr'
   AND c1.status = 1 AND c1.parent_client_id = c2.id
   GROUP BY c2.name;

SELECT c2.id, c2.name as 'client', count(*) as 'queues'
  FROM extension ext, client c1, client c2
  WHERE ext.client_id = c1.id AND ext.type = 'queue'
   AND c1.status = 1 AND c1.parent_client_id = c2.id
  GROUP BY c2.name;

SELECT c2.id, c2.name as 'client', sum(if(value<11,1,0)) AS "conf10"
  FROM extension ext, client c1, client c2, extension_prefs exp
  WHERE ext.client_id = c1.id AND exp.param="conf_size"
   AND ext.type="conference" 
   AND c1.parent_client_id = c2.id AND exp.extension_id = ext.id
  GROUP BY c2.name;

SELECT c2.id, c2.name as 'client', 
 sum(if(value<20 AND value>10,1,0)) AS "conf20"
  FROM extension ext, client c1, client c2, extension_prefs exp
  WHERE ext.client_id = c1.id AND exp.param="conf_size"
   AND ext.type="conference"
   AND c1.parent_client_id = c2.id AND exp.extension_id = ext.id
  GROUP BY c2.name;

SELECT c2.id, c2.name as 'client', sum(if(value>20,1,0)) AS "conf30"
  FROM extension ext, client c1, client c2, extension_prefs exp
  WHERE ext.client_id = c1.id AND exp.param="conf_size"
   AND ext.type="conference"
   AND c1.parent_client_id = c2.id AND exp.extension_id = ext.id
  GROUP BY c2.name;

[runrig]

I would consider making views out of the other queries (then the main query and the joins are simple), but I would also probably change the queries. The usual thing to do is to include everything but the aggregate in the GROUP BY clause, but I'm not sure what MySQL does when you don't do that and I don't know, e.g., what the keys of the tables are or if you have duplicate client name's with multiple client id's and how you want to handle that, etc.

[btongeorge]

Very interested in your comments re. changing the queries. Haven't ever used views in MySQL so will look into that. There are no duplicate clients in the table and there won't be, the client ID is the primary key.

How do you suggest that I modify the queries? Not sure I fully understand the implications of your comments.

[runrig]

Something along the lines of this...first I'd combine your conference size queries into one view:

-- Or maybe conf_size_counts ??
CREATE VIEW conf_sizes AS
SELECT
  c2.id,
  sum(if(value<11,1,0)) AS "conf10",
  sum(if(value<20 and value>10,1,0)) AS "conf20",
  sum(if(value>20,1,0)) AS "conf30"
FROM extension ext, client c1, client c2, extension_prefs exp
WHERE ext.client_id = c1.id
  AND exp.param="conf_size"
  AND ext.type="conference"
  AND c1.parent_client_id = c2.id
  AND exp.extension_id = ext.id
GROUP BY c2.id;

Then modify your main query as:

SELECT c.id, c.name, cs.conf10, cs.conf20, cs.conf30
FROM client c LEFT OUTER JOIN conf_sizes cs ON c.id = cs.id
WHERE c.level = 50
  AND c.status = 1

The rest of the views and joins I leave for you...

[Marshall]

I suggest reading the SQL book that I recommended in another part of this thread. It will cover "views". Your application is "read only" so something like that is completely appropriate.

I am no SQL guru, but SQL has several types of table joins where 2 or more tables can be combined into a single "view" which is like a temporary table with the columns of both tables, but read only. On all tricky SQL questions, I defer to wiser Monks than myself.

However, if you can do the SQL, it can make the Perl code easier because you could just get all the results at once.

There is sometimes a tradeoff between Perl voodoo and SQL voodoo. I had one SQL query that I worked on very hard and it worked, but it took 15 minutes. Then I "simplified it" into 1,500 SQL queries and it took only 15 seconds. But I used some serious "Perl-kung-foo" on it!

From what I understand so far, your app is not performance or memory limited. Find some combination of stuff that works for you! Any of the SQL or Perl code that we've discussed so far will be just fine.