Solved

What subquery do I need to calculate this report?

Posted on 2014-07-18
5
165 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
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 108

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Mysql sync between 3-4 mysql db 4 25
Configuring a checkbox in CSS and php 18 33
Scope of $_SESSION 17 30
hosting images 4 12
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…
I imagine that there are some, like me, who require a way of getting currency exchange rates for implementation in web project from time to time, so I thought I would share a solution that I have developed for this purpose. It turns out that Yaho…
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 look for a specific file type in a local or remote server directory using PHP.

863 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

24 Experts available now in Live!

Get 1:1 Help Now