Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

What subquery do I need to calculate this report?

Posted on 2014-07-18
5
Medium Priority
?
181 Views
Last Modified: 2014-07-22
I have two tables: tickets and comments. I need to run a report that shows me which ticket is "neglected" meaning that there has not been a comment in X amount of time.

If I was to do this in PHP, I would load ticket, then do a separate query for each one, see what the most recent timestamp was (max(comment_ts)), and keep the values of the "old tickets" in an array, then display them from there. But, it seems I should be able to do this as a single query via MySQL and it would be much faster, so I am trying to create a view for this.

Assume this is the basic table structure:

    -- MySQL Script generated by MySQL Workbench
    -- 07/18/14 17:29:39
    -- Model: New Model    Version: 1.0
    SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
    SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
    SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';

    -- -----------------------------------------------------
    -- Schema mydb
    -- -----------------------------------------------------
    CREATE SCHEMA IF NOT EXISTS `mydb` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci ;
    USE `mydb` ;

    -- -----------------------------------------------------
    -- Table `mydb`.`tickets`
    -- -----------------------------------------------------
    CREATE TABLE IF NOT EXISTS `mydb`.`tickets` (
      `ticket_id` INT NOT NULL,
      `ticket_description` TEXT NULL,
      PRIMARY KEY (`ticket_id`))
    ENGINE = InnoDB;


    -- -----------------------------------------------------
    -- Table `mydb`.`comments`
    -- -----------------------------------------------------
    CREATE TABLE IF NOT EXISTS `mydb`.`comments` (
      `comments_id` INT NOT NULL,
      `comments_comment` VARCHAR(45) NULL,
      `comments_timestamp` INT NULL,
      PRIMARY KEY (`comments_id`),
      CONSTRAINT `comments_bug`
        FOREIGN KEY (`comments_id`)
        REFERENCES `mydb`.`tickets` (`ticket_id`)
        ON DELETE NO ACTION
        ON UPDATE NO ACTION)
    ENGINE = InnoDB;


    SET SQL_MODE=@OLD_SQL_MODE;
    SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
    SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;

Open in new window

0
Comment
Question by:DrDamnit
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 14

Expert Comment

by:quizwedge
ID: 40205682
You should be able to use the following query to create your view:
select distinct t.ticket_id, ticket_description from `tickets` t join `comments` c on t.ticket_id = c.comments_id where comments_timestamp >= DATE_SUB(NOW(), INTERVAL 1 DAY)

Open in new window

Since ticket_id and comments_id are connected, you can join on those. By only selecting from the tickets table, you can use a distinct so you only get those rows once.
0
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 40206264
If performance is an issue, you might want to consider a GROUP BY clause instead of DISTINCT.  And a LIMIT clause, as well.  You might want to use ORDER BY as well.
0
 
LVL 32

Author Comment

by:DrDamnit
ID: 40207778
Looking at your code, here's what I went with... but it has problems.
SELECT DISTINCT
    tickets.tickets.id,
	tickets.tickets.summary,
	FROM_UNIXTIME(comments.comments_timestamp) as `ts`
FROM
    bugreport.tickets
        LEFT JOIN
    bugreport.comments ON comments_ticket = tickets.id
WHERE from_unixtime(comments_timestamp) >= date_sub(now(), interval 1 day)

Open in new window


The results are not qutie what I wanted. This is, indeed, only giving me 11 itckets out of 800+; however, they are not the "abandoned" tickets I wanted.

The point of the query is to get a list of tickets that have not had a comment on them for X period of time so that I can manage them. This happens with tickets sometimes because they get fixed, but no one marks them resolved. Or, the reporter gets busy, forgets about them, and they languish.

For example, I have many tickets that have been closed and therefore there hasn't been a comment on them in months. None of those show up in that query (as they should - because we are not checking the ticket status in this query).

Thoughts?
0
 
LVL 14

Expert Comment

by:quizwedge
ID: 40209341
I'm a bit confused about your query vs the table structure you posted below.

In your query, you have tickets.tickets.id and tickets.tickets.summary, but from the table structure it looks like it should just be tickets.id and tickets.summary.

In your query, you have comments_ticket but in your table structure you have comments_id

None of that should be the difference between 11 tickets and the number you're expecting, I'm just wondering if I'm missing something else.
0
 
LVL 32

Accepted Solution

by:
DrDamnit earned 0 total points
ID: 40212178
I figured it out. This was the query that I ultimately used:
SELECT 
    tickets.id, tickets.summary, last_comment
FROM
    (SELECT 
        tickets.id,
            MAX(FROM_UNIXTIME(comments.comments_timestamp)) AS last_comment,
            tickets.summary
    FROM
        bugreport.bugs
    LEFT JOIN `comments` ON `comments`.`comments_bug` = tickets.tickets.id
    WHERE
        comments.comments_timestamp < UNIX_TIMESTAMP(NOW() - INTERVAL 2 DAY)
            AND comments.comments_type = 'C'
            AND tickets.tickets.status IN (0 , 5, 10, 15, 20, 25, 30, 40, 50)
    GROUP BY tickets.id) AS mytab ORDER BY last_comment ASC

Open in new window

0

Featured Post

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
By, Vadim Tkachenko. In this article we’ll look at ClickHouse on its one year anniversary.
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
The viewer will learn how to count occurrences of each item in an array.

670 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question