Solved

What subquery do I need to calculate this report?

Posted on 2014-07-18
5
161 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
  • 2
  • 2
5 Comments
 
LVL 14

Expert Comment

by:quizwedge
Comment Utility
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 108

Expert Comment

by:Ray Paseur
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Foreword In the years since this article was written, numerous hacking attacks have targeted password-protected web sites.  The storage of client passwords has become a subject of much discussion, some of it useful and some of it misguided.  Of cou…
Part of the Global Positioning System A geocode (https://developers.google.com/maps/documentation/geocoding/) is the major subset of a GPS coordinate (http://en.wikipedia.org/wiki/Global_Positioning_System), the other parts being the altitude and t…
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

771 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now