[RESOLVED] Help with query

[ixalmida]

I've got a query that's messing with my head. I usually try to avoid using IN() lists, but I can't think of how to rewrite this query to avoid it. Hopefully someone can help me correctly aggregate this query.

In my doc management system, I track "owners" (the person who uploaded a document) and revisors - which may be the same or different users. I also track upload dates and revision dates. I need to get a count of documents uploaded or revised in the last 30 days by the user's location_id (in the Users table).

This query works but it seems like very bad form:

foreach($loc_id as $location_id)
  {
  $query = "SELECT COUNT(doc_id) AS doc_count FROM Documents WHERE
    (
    upload_date > CURDATE() - INTERVAL 30 DAY OR rev_date > CURDATE() - INTERVAL 30 DAY
    )
    AND
    (
    owner_id IN (SELECT user_id FROM Users WHERE location_id = $location_id) OR
    rev_id IN (SELECT user_id FROM Users WHERE location_id = $location_id)
    )";
  // Output, etc...
  }

Can someone suggest a better way to write this?

[ixalmida]

I figured out something slightly better (I think). At least it doesn't use a list. 🙂

SELECT location_id, COUNT(doc_count) FROM
  (
  SELECT rev_id AS user_id, COUNT(doc_id) AS doc_count FROM Documents
    WHERE rev_date > NOW() - INTERVAL 30 DAY GROUP BY rev_id
  UNION ALL
  SELECT owner_id AS user_id, COUNT(doc_id) AS doc_count FROM Documents
    WHERE upload_date > NOW() - INTERVAL 30 DAY GROUP BY owner_id
  ) A 
  JOIN Users USING(user_id)
  WHERE location_id = $location_id
  GROUP BY ctc_id

It still grabs more data in the subquery than necessary but I can live with it, I guess.