• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 186
  • Last Modified:

What subquery do I need to calculate this report?

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
DrDamnit
Asked:
DrDamnit
  • 2
  • 2
1 Solution
 
quizwedgeCommented:
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
 
Ray PaseurCommented:
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
 
DrDamnitAuthor Commented:
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
 
quizwedgeCommented:
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
 
DrDamnitAuthor Commented:
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

[Webinar] Kill tickets & tabs using PowerShell

Are you tired of cycling through the same browser tabs everyday to close the same repetitive tickets? In this webinar JumpCloud will show how you can leverage RESTful APIs to build your own PowerShell modules to kill tickets & tabs using the PowerShell command Invoke-RestMethod.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now