0

I am using the following query to generate a comprehensive download log with data from multiple tables (reduced to three for this example):

SELECT  wp_download_log.ID
       ,wp_download_log.download_date
       ,wp_posts.post_title
       ,wp_postmeta.meta_value
       ,wp_download_log.download_id
       ,wp_download_log.version_id
       ,wp_download_log.user_id
       ,wp_download_log.user_ip
       ,wp_download_log.user_agent
FROM wp_posts
LEFT JOIN wp_download_log ON wp_posts.ID = wp_download_log.download_id
LEFT JOIN wp_postmeta ON wp_download_log.version_id = wp_postmeta.post_id

But to produce a list of downloadable items (the 'master list' for which is in one table, wp_posts) along with the most recent download attempt for each (which is in another table, wp_download_log), I have to open the resulting CSV file in Excel and:

  • Sort by 1) post / download ID, smallest to largest, and 2) download date, latest first
  • Remove duplicates so that only the first instance of each download ID remains (and thus the date of the most recent download attempt for that download)

Even so, the final file will not show downloadable items from wp_posts that have not been downloaded yet simply because they do not exist anywhere in the download log.

I know this can be done with MySQL. But I cannot for the life of me figure out how. The best I can do is query the wp_download_log table alone and come up with a list of downloadable items by ID only, with the latest download date in the next column:

SELECT download_id, MAX(download_date)'
FROM wp_download_log
GROUP BY download_id
ORDER BY MAX(download_date)

This seems to produce the exact result as my Excel operation but without data from joined tables, as the query is on a single table.

By this time I realized that I could join the additional tables to the log table and then group and sort. But this doesn't work because there will still be downloadable items that have not been downloaded yet at the time of the query and so will not exist in what is supposed to be a list rather than a log. So...back to square one.

Does this make any sense? How do I accomplish this? I have spent six to eight hours in the past two days reading about things like max, group by, and subqueries, and trying many examples. But I have yet to find something that doesn't spit out errors.

The end goal is basically:

  • Grab specified columns in wp_posts where wp_posts.post_type = 'dlm_download'
  • Join with wp_download_log table (wp_posts.ID = wp_download_log.download_id)
  • Show in final result most recent wp_download_log.download_date per download item row

EDIT: My original, full query is as follows. I am sharing because I found out that I can add an ORDER BY condition to sort the two columns that I was previously sorting with Excel. Is this the right way to do it? Regardless, now I cannot figure out how to group the results so that the output is only the first of each ID. It looks like the GROUP BY condition has to come before ORDER BY, meaning it won't work for this.

SELECT wp_download_log.ID AS 'Download log ID', wp_download_log.download_date AS 'Download date and time', wp_posts.post_title AS 'Download title',

(SELECT group_concat(wp_terms.name separator ', ') FROM wp_terms 
INNER JOIN wp_term_taxonomy on wp_terms.term_id = wp_term_taxonomy.term_id 
INNER JOIN wp_term_relationships wpr on wpr.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id 
WHERE taxonomy= 'dlm_download_category' and wp_posts.ID = wpr.object_id ) AS "Categories",

MID(wp_postmeta.meta_value, 3, LENGTH(wp_postmeta.meta_value) -4 ) AS 'Downloaded file (direct link)',

wp_download_log.download_id AS 'Download ID', wp_download_log.version_id AS 'Download version ID', wp_download_log.user_id AS 'User ID', wp_download_log.user_ip AS 'User IP address', wp_download_log.user_agent AS 'User agent'

FROM wp_posts
LEFT JOIN wp_download_log ON wp_posts.ID = wp_download_log.download_id
LEFT JOIN wp_postmeta ON wp_download_log.version_id = wp_postmeta.post_id
WHERE wp_postmeta.meta_key = '_files'
ORDER BY wp_download_log.download_id ASC, wp_download_log.download_date DESC

One weird thing to note is this query returns 21,032 results, where as the following query returns 21,037:

SELECT *
FROM wp_download_log
2
  • Please add Mysql version
    – user153556
    Mar 8, 2020 at 2:18
  • @Sam, in my live environment, I am using MySQL 5.7, I believe. Mar 8, 2020 at 2:32

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.