search

ralberth / MMM-MysqlQuery

MagicMirror plug-in module that displays a table of MySQL SELECT results
MIT License
8 stars 3 forks source link

Multiple queries not working #10

Closed schiz0phr3ne closed 4 years ago

schiz0phr3ne commented 4 years ago

Hi,

I try to use your plugin with multiple queries in one module instance, because I need to dynamicaly concat aliases.

My queries look like this : 

SET @semaine = WEEK(DATE_SUB(CURDATE(), INTERVAL 7 DAY)) + 1,
@sql = CONCAT(" ***long query with concat***");
PREPARE stmt FROM @SQL;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

Any way to do that ?

Actualy, I only get column headers, but no data (with no concatenation, and only the query in the CONCAT (no SET, CONCAT, PREPARE, etc), I get my data).

ralberth commented 4 years ago

Bonjour, ça va bien? (saw 'semaine' in your post above...)

I don't completely understand your question, so let's start by discussing what you're trying to do first. We'll decide on a strategy and get it working.

Can you first tell me more about what you want MagicMirror to do, without going into the technology? I'm curious about the multiple queries with dynamic content. Where does the dynamic part come from? For example, do you need 3 separate queries each with a different week? Maybe data for last week, and the week before, and the current week...?

A brief description of the table(s) you're querying will help as well, while we discuss it more.

schiz0phr3ne commented 4 years ago

Ça va, merci. Et toi ? :)

Ok, I was not explicit, I'm sorry.

I'm using MagicMirror at work, as a wallboard with tables and graphs. This table is used to display last week hours of our technicians. I can't write the full and real queries now, I'm on my phone and at home.

But, to simplify, imagine this query :

SELECT col1 AS 'column 1', col2 AS 'column 2'
FROM table

I need to add the last week number in one of the aliases. The only thing that worked for me (I need to tell that absolutely not an expert with MySQL/MariaDB), is to declare a variable (@semaine) and concat it in another one (@sql) before execute the resultant query.

With all steps, it's about 5 queries. And I think that with one ;, your plugin does not work (or I missuse it). Without dynamic alias, the query in example is fully working (except that I don't have the dynamic alias).

Have you all needed informations ?

ralberth commented 4 years ago

(bientot j'espere...stressé aujourd'hui, l'ordinateur est lent :-)

OK, I will definitely need more to help, but we'll figure it out!

Would you please reply with:

  1. The table definitions you are querying (table names and columns)
  2. Is there a query that works? (the whole query)
  3. Do you have a sample of what is supposed to change from one query to the next?
  4. Do you need separate tables to appear on MagicMirror, or would it be OK to have one table with all the data somehow?

Thanks.

schiz0phr3ne commented 4 years ago

Hello Rich,

I'm at work, so I can give you these informations.

  1. Table names and columns : a. xtechnicien (contains technicians details) :
    • codetechnicien (char ; contains unique code for each technician)
    • nomtechnicien (char ; contains full name of each technician)

b. interarticle (cointains articles for each project) :

  1. The whole queries that work : a. Without dynamic alias : 

    SELECT b.nomtechnicien AS 'Technicien', SUM(a.qtecumulreel) AS 'Heures semaine'
FROM interarticle a, xtechnicien b
WHERE a.dateconso BETWEEN (SELECT DATE_SUB(DATE_SUB(CURDATE(), INTERVAL 7 DAY), INTERVAL (SELECT WEEKDAY(DATE_SUB(CURDATE(), INTERVAL 7 DAY))) DAY)) AND (SELECT DATE_ADD(DATE_SUB(DATE_SUB(CURDATE(), INTERVAL 7 DAY), INTERVAL (SELECT WEEKDAY(DATE_SUB(CURDATE(), INTERVAL 7 DAY))) DAY), INTERVAL 6 DAY))
AND a.codearticle NOT IN ('Z1','Z2','Z3','Z4','Z5','Z6')
AND a.codearticle LIKE '%MO%'
AND b.codetechnicien=a.codetechnicien  
GROUP BY b.nomtechnicien;

    b. With dynamic alias (only the second column alias is dynamic) : 

    SET @semaine = WEEK(DATE_SUB(CURDATE(), INTERVAL 7 DAY)) + 1,
@sql = CONCAT("SELECT b.nomtechnicien AS 'Technicien', SUM(a.qtecumulreel) AS 'Heures semaine ", @semaine, "' FROM interarticle a, xtechnicien b WHERE a.dateconso BETWEEN (SELECT DATE_SUB(DATE_SUB(CURDATE(), INTERVAL 7 DAY), INTERVAL (SELECT WEEKDAY(DATE_SUB(CURDATE(), INTERVAL 7 DAY))) DAY)) AND (SELECT DATE_ADD(DATE_SUB(DATE_SUB(CURDATE(), INTERVAL 7 DAY), INTERVAL (SELECT WEEKDAY(DATE_SUB(CURDATE(), INTERVAL 7 DAY))) DAY), INTERVAL 6 DAY)) AND a.codearticle NOT IN ('Z1','Z2','Z3','Z4','Z5','Z6') AND a.codearticle LIKE '%MO%' AND b.codetechnicien=a.codetechnicien GROUP BY b.nomtechnicien");
PREPARE stmt FROM @SQL;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

  2. Result samples : a. Without dynamic alias : 

    ********************************************************
*       Technicien            *     Heure semaine      *
********************************************************
*       technicien1           *           40           *
*       technicien2           *           41           *
*       technicien3           *           40           *
********************************************************

    b. With dynamic alias : 

    ********************************************************
*       Technicien            *    Heure semaine 48    *
********************************************************
*       technicien1           *           40           *
*       technicien2           *           41           *
*       technicien3           *           40           *
********************************************************

  3. It's OK for only one table to appear, because I need (for this query) only one table ;)

ralberth commented 4 years ago

[for non francophiles, "semaine" is "week", "nom" (in 'nomtechnicien') is "name", "Heures" is "hours"]

Some small SQL improvements

Unneeded parts of SQL

In your sample query,

AND a.codearticle NOT IN ('Z1','Z2','Z3','Z4','Z5','Z6')
AND a.codearticle LIKE '%MO%'

The "NOT IN" isn't doing anything since none of those values are like '%MO%', so you can remove it.

Break up with functions

Consider writing 2 functions in the database that do the date calculations for the (SELECT DATE_SUB(... stuff.

The final queries would look more like this:

...
WHERE a.dateconso BETWEEN AvantSemaine(CURDATE()) AND ApresSemaine(CURDATE())

Views

There is likely a need for running the same basic query more than once: one time for per-Technicien rows, and another time to compute the total. A view could help:

create view technicien_heures_semaine AS
    SELECT nomtechnicien AS 'Technicien',
           SUM(a.qtecumulreel) AS 'Heures semaine'
    FROM interarticle a
    INNER JOIN xtechnicien b on a.codetechnicien = b.codetechnicien
    WHERE a.dateconso BETWEEN (SELECT DATE_SUB(...
    AND a.codearticle LIKE '%MO%';

Solution 1

If you are happy with the existing code, you could wrap it in a stored procedure, and then call it from MagicMirror. MySQL allows you to create a stored procedure that returns columnar data and then call it in the same places you would normally use a SQL SELECT statement.

Check out MMM-MysqlQuery Issue 4 for an example of doing this.

However, dynamic column names are not a common pattern in SQL. For example, in MMM.MysqlQuery, you can set column attributes only by the column name. If the name is dynamic, you couldn't set a cssClass, prefix, or precision.

Solution 2

Create a new row with the totals ("Heure semaine 48" in your example) instead of putting the total in the header, and combine it with some styling so the total row looks distinctive.

SELECT 1 as rectype,
       nomtechnicien AS 'Technicien',
       SUM(a.qtecumulreel) AS 'Heures semaine'
FROM interarticle a
INNER JOIN xtechnicien b on a.codetechnicien = b.codetechnicien
WHERE a.dateconso BETWEEN (SELECT DATE_SUB(...
AND a.codearticle LIKE '%MO%'
GROUP BY b.nomtechnicien
UNION ALL
SELECT 2 as rectype,
       'HEURES SEMAINE TOTALE:' as 'Technicien',
       SUM(a.qtecumulreel) as 'Heures semaine'
FROM interarticle a
INNER JOIN xtechnicien b on a.codetechnicien = b.codetechnicien
WHERE a.dateconso BETWEEN (SELECT DATE_SUB(...
AND a.codearticle LIKE '%MO%'
ORDER BY rectype;

Notes:

  1. UNION ALL just combines the rows from the first and second queries into one big result set
  2. The ORDER BY applies to the rows from both queries (on both sides of the UNION ALL). "rectype" will sort all technicien rows above the final 'total' row (with rectype value 2).

Config

When this is embedded in MMM-MysqlQuery, we can use the "columns" part of the config.js file to pick only the "Technicien" and "Heures semaine" columns to display. The user will just see the two columns, but the total row will always be at the bottom.

We can also get fancy and bold or color that total row so it stands out more. Check out the "column[n].displayType" from the README: you can set this to "html", and change the query to:

SELECT ...
       '<b><font color="red">Heures semaine totale:</font></b>' as 'Heures semaine'

and it'll draw it in red bold text.

schiz0phr3ne commented 4 years ago

Thank you Rich, for your time and your answer.

To resume : your great module can't support multiple SELECT, and I understand it. I will use the solution 2, because I don't have write right on our database.

Thank you again !