Mysql – Trouble with a query subselect

errorsMySQLsubquery

I am trying to merge 2 large queries together and can't get it to work. I have an almost working query, except I can't get the server to accept the p.products_id in the nested query. Can anyone help work around this?

The strange inner select with many UNION ALL generates a list of all dates in a range. (adapted from https://stackoverflow.com/questions/3538858/mysql-how-to-fill-missing-dates-in-range).

The number of levels of nesting seems excessive, but it's the only way I could get the query to work at all.

select p.products_id, 
       ( SELECT count(*) 
         from ( select ( SELECT if(v1.quantity, v1.quantity, null) 
                         FROM inventory_history v1 
                         where v1.products_id = p.products_id 
                           AND v1.inventory_date <= t1.date 
                         ORDER BY v1.inventory_date desc limit 1 
                        ) as quantity 
                FROM ( SELECT a.date
                       FROM ( SELECT curdate() - INTERVAL (a.a + (10 * b.a) + (100 * c.a)) DAY AS date
                              FROM (SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS a
                              CROSS JOIN (SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS b
                              CROSS JOIN (SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS c
                             ) a
                       WHERE a.date >= curdate() - INTERVAL 400 DAY
                      ) t1
                having quantity is not null
               ) x
        ) as count 
from products p 
where p.master_categories_id=264;

ERROR 1054 (42S22): Unknown column 'p.products_id' in 'where clause'

What I'm trying to do: inventory_history contains snapshots of inventory levels by date. The data is stored sparsely — only adds a record when inventory changes. Most of the query is converting that sparse data into results for all dates in range, then I want to count the number of days where the inventory quantity is > 0.

Server version: 10.1.35-MariaDB FreeBSD Ports

Fiddle: https://dbfiddle.uk/?rdbms=mysql_5.7&fiddle=e199d540f40e4362e6aa1d667e17fcac

Best Answer

There might be a way to rearrange parts of the correlated subquery so that it can reference the outer level's products_id directly, rather than in a subquery/derived table of its own, but in the end I had too little patience to find it.

Instead, I can offer you a different approach to calculating the same results, which uses variables:

SELECT
  p.products_id
, (
    SELECT
      SUM(
        DATEDIFF(to_date, GREATEST(from_date, CURDATE() - INTERVAL 400 DAY)) * factor
      )
    FROM
      (
        SELECT
          IF(h.products_id = @p, @d, CURDATE() + INTERVAL 1 DAY) AS to_date
        , @d := h.inventory_date                                 AS from_date
        , (quantity > 0)                                         AS factor
        , @p := h.products_id                                    AS products_id
        FROM
          inventory_history AS h
        ORDER BY
          h.products_id ASC
        , h.inventory_date DESC
      ) AS v1
    WHERE
      v1.products_id = p.products_id
      AND v1.to_date >= CURDATE() - INTERVAL 400 DAY
  ) AS count
FROM
  (SELECT @p := null, @d := null) AS init
, products AS p
WHERE
  p.master_categories_id=264
;

With the help of two variables – @p to keep track of the current products_id, and @d to carry over inventory_date to the preceding record of the same product – the v1 derived table turns inventory_history into a set of date ranges, with a flag (factor) to indicate whether the range qualifies for the count result or not.

The derived table's definition is two levels deep, so it cannot reference the main query's products_id. It simply returns the entire set. However, the SELECT that is using v1 is where p.products_id is valid, and that is where filtering on products_id happens.

The count itself is then simply a matter of adding up the date ranges' lengths, using the DATEDIFF(end_date, start_date) function. The starting day expression (GREATEST(...)) simply makes sure that the earliest date range's starting point, for the purposes of the query, is no earlier than 400 days from now.

Here is a link to a live demo of this solution at dbfiddle.uk using your test setup:

https://dbfiddle.uk/?rdbms=mariadb_10.3&fiddle=dc5baefec8902a99bf807115aaa235ba

Related Solutions

Mysql – Individual queries run on 10ms, with UNION ALL they are taking 290ms+ (7.7M records MySQL DB). How to optimise

Firstly, I think your original query may not be "correct"; With reference to your SQLFiddle, it looks to me as though you should be returning rows with ID = 2, 3 and 4 (in addition to the row with ID = 1 you are getting from this half), because your existing logic appears as though you intended for these other rows to be included, as they explicitly meet the OR (date_from >= '2014-04-10 08:00:00') part of your second WHERE clause.

The GROUP BY teacher_id clause in your second part of your UNION is causing you to lose those rows. This is because you're not actually aggregating any columns in your select list, and in this case the GROUP BY will cause 'difficult to define' behaviour.

Also, while I can't explain the poor performance of your UNION, I can work around it for you by outright removing it from your query:

Rather than using two separate (and in parts, repeating) sets of logic to get rows from the same table, I've consolidated your logic into one query with the differences in your logic ORed together - ie if a row meets one or the other of your original WHERE clauses, it's included. This is possible because I've replaced the (INNER) JOIN you were using to find the closestDate with a LEFT JOIN.

This LEFT JOIN means we are now also able to distinguish which set of logic should be applied to a row; If the join works (closestDate IS NOT NULL) we apply your logic from the first half, but if the join fails (closestDate IS NULL) then we apply the logic from your second half.

So this will return all the rows that your query returned (in the fiddle), and it's also picking up those additional ones.

  SELECT
    *

  FROM 
    teacher_slots ts

    LEFT JOIN 
    (
      SELECT 
        teacher_id,
        DATE(MIN(date_from)) as closestDay

      FROM 
        teacher_slots

      WHERE   
        date_from >= '2014-04-10 08:00:00' 
        AND order_of_arrival = 0
        AND status = 0 
        AND city_id = 6015 
        AND subject_id = 1

      GROUP BY 
        teacher_id

    ) a
    ON a.teacher_id = ts.teacher_id
    AND a.closestDay = DATE(ts.date_from)

  WHERE 
    /* conditions that were common to both halves of the union */
    ts.status = 0
    AND ts.city_id = 6015
    AND ts.subject_id = 1

    AND
    (
      (
        /* conditions that were from above the union 
           (ie when we joined to get closest future date) */
        a.teacher_id IS NOT NULL
        AND ts.date_from >= '2014-04-10 08:00:00'
        AND ts.order_of_arrival = 0
      ) 
      OR
      (
        /* conditions that were below the union 
          (ie when we didn't join) */
        a.teacher_id IS NULL       
        AND ts.order_of_arrival = 1 
        AND 
        (
          (
            date_from <= '2014-04-10 08:00:00' 
            AND  
            date_to >= '2014-04-10 08:00:00'
          )

          /* rows that met this condition were being discarded 
             as a result of 'difficult to define' GROUP BY behaviour. */
          OR date_from >= '2014-04-10 08:00:00' 
        )
      )
    )

  ORDER BY 
   ts.date_from ASC;

Further, you can "tidy up" your query further so that you don't need to "plug in" your status, city_id and subject_id parameters more than once.

To do this, change the subquery a to also select those columns, and to also group on those columns. Then, the JOIN's ON clause would need to map those columns to their ts.xxx equivalents.

I don't think this will negatively effect performance, but couldn't be sure without testing on a large dataset.

So your join will look more like:

LEFT JOIN 
(
  SELECT 
    teacher_id,
    status,
    city_id,
    subject_id,
    DATE(MIN(date_from)) as closestDay

  FROM 
    teacher_slots

  WHERE   
    date_from >= '2014-04-10 08:00:00' 
    AND order_of_arrival = 0
  /* These no longer required here...
    AND status = 0 
    AND city_id = 6015 
    AND subject_id = 1
  */

  GROUP BY 
    teacher_id,
    status,
    city_id,
    subject_id

) a
ON a.teacher_id = ts.teacher_id
AND a.status = ts.status 
AND a.city_id = ts.city_id 
AND a.subject_id = ts.city_id
AND a.closestDay = DATE(ts.date_from)

Mysql – how to write conditional where clause query

It can be done using some multiple joins from table1 into table2 and then use those joins combined with case/calculation logic, or using sub-selects. But it seems rather complex to want to do that in a single query/single join - that I'd recommend finding an alternative way to do it. It's something which is a good candidate to solve in application logic.

However, I think the route you want to go is something like this:

SELECT
  T1.*,
  CASE
    WHEN T2.value = '1' THEN 'CalculateStuffBasedOnT3OrT4'
    WHEN T2.value = '2' THEN 
        CASE         
          WHEN t3.value IS NULL THEN 'CalculateOtherStuffBasedOnT3OrT4'
          WHEN T3.value = '2' THEN 'CalculateMoreStuffBasedOnT3OrT4'
          ELSE 'SomethingElse'
        END
     ELSE 'Something'
    END
FROM tbl1 T1
LEFT JOIN tbl2 T2 ON T1.id = T2.id AND T2.key = 'm_type'
LEFT JOIN tbl2 T3 ON T1.id = T3.id AND T3.key = 'm_routine'
LEFT JOIN tbl2 T4 ON T1.id = T4.id AND T4.key = 'm_date'

Such a query can then be used as foundation in a subquery to select the posts you want.

But this will quickly become quite complex and unmaintainable. So I'd advice you to rethink the strategy and go with designing the query dynamically in you php code, or rethink the data structure, or utilize multiple queries to solve it.

Best Answer

Related Solutions

Mysql – Individual queries run on 10ms, with UNION ALL they are taking 290ms+ (7.7M records MySQL DB). How to optimise

Mysql – how to write conditional where clause query

Related Question