Databases
StackOverflow2013
Postsdbo
POOrdering and grouping in a complex MySQL query
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POOrdering and grouping in a complex MySQL query
    primarykey
    data
    text
    <p>I'm needing to do a fairly complex set of MySQL queries to generate data suitable for plotting in D3 from a database of labels assigned to different musical artists. The rows in the relevant table (called "lastfm_annotations") are: user_id, artist_id, tag_id, and tag_month (i.e. we've recorded many instances of a particular user labeling a particular artist with a particular tag at a particular time). This is all embedded in a php script.</p> <p>What I need to generate in the end is a JSON object that contains, for dates in a given range, the number of times each unique tag for a given artist was used in that month (including counts of zero for dates in which a given tag was not used. </p> <p>This is what I have so far (assume $itemID and artist_id are interchangeable here):</p> <pre><code>$cal = array(); $result = mysql_query("select date from calendar;"); // this just gets all possible dates, but of course could be changed to get a different date range if (!$result) { echo 'Could not run query: ' . mysql_error(); exit; } for ($i = 0; $i &lt; mysql_num_rows($result); $i++) { $date =mysql_fetch_row($result)[0]; $result2 = mysql_query("select t2.tag_id, case when t1.freq is null then 0 else t1.freq end as freq from (select distinct tag_id from lastfm_annotations where artist_id='" . $itemID . "') t2 left join (select tag_id, count(*) as freq from lastfm_annotations where artist_id='" . $itemID . "' and tag_month='" . $date . "' group by tag_id) as t1 on t2.tag_id = t1.tag_id group by t2.tag_id"); $current = array(); $current['date'] = $date; for ($j = 0; $j &lt; mysql_num_rows($result2); $j++) { $row = mysql_fetch_row($result2); $tag = $row[0]; $freq = $row[1]; $result3 = mysql_query("select tag_name from lastfm_taglist where tag_id ='" . $tag . "' limit 1;"); $tagName = mysql_fetch_row($result3)[0]; $current[$tagName] = $freq; } array_push($data, $current); } echo json_encode($data); </code></pre> <p>(Edit: the big query follows)</p> <pre><code>select t2.tag_id , case when t1.freq is null then 0 else t1.freq end as freq from (select distinct tag_id from lastfm_annotations where artist_id='$itemID') t2 left join (select tag_id, count(*) as freq from lastfm_annotations where artist_id='$itemID' and tag_month='$date' group by tag_id) as t1 on t2.tag_id = t1.tag_id group by t2.tag_id </code></pre> <p>(End edit.)</p> <p>This works, but has (at least) two big problems that I can't quite figure out. First, in big ugly SQL query, I'm doing redundant work calling</p> <pre><code>(select distinct tag_id from lastfm_annotations where artist_id='" . $itemID . "') </code></pre> <p>every time I go through the loop, even though the value is the same every time. Any idea how I can get around that? Maybe it's possible to somehow save the unique tag_ids as a php array, and then insert that into the query?</p> <p>Second, I need to make sure that the tags are always ordered by their overall frequency (i.e. across all time, not just within a particular month), but I'm not sure how to do that. I can get the proper sequence using a query something like:</p> <pre><code>select tag_id, count(*) as freq from lastfm_annotations where item_id=XXX order by freq desc </code></pre> <p>But I need to ensure that each query in my loop returns the tags in that same order. Any ideas? Perhaps it would be better to handle the sorting in d3 when I actually start plotting data, but it would be preferable if the data started off in the right order when I make the SQL call.</p> <p>Sorry for the big question, and thanks for any help!</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USAlanyst
    1. USmoustachio
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POOrdering and grouping in a complex MySQL query
      1. This table or related slice is empty.
      1. VTApproveEditSuggestion
    1. COThanks for the edit Alanyst - it's much more readable now.
      singulars
      1. POOrdering and grouping in a complex MySQL query
      1. USmoustachio
    2. COSide note: you can use the more concise `ifnull(t1.freq, 0)` instead of the `case when t1.freq is null then 0 else t1.freq end` in the query.
      singulars
      1. POOrdering and grouping in a complex MySQL query
      1. USAlanyst
    3. COIf tag 'foo' is used for artist A in a particular month, but has never been used for artist B, should the results for artist B have rows for tag 'foo' (with `freq` == 0), or should B's results only have tags that have been used for B?
      singulars
      1. POOrdering and grouping in a complex MySQL query
      1. USAlanyst
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload