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

LVL 32
DrDamnitAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.