Mysql query in drupal database - groupwise maximum with duplicate data

Question

I'm working on a mysql query in a Drupal database that pulls together users and two different cck content types. I know people ask for help with groupwise maximum queries all the time... I've done my best but I need help.

This is what I have so far:

# the artists
SELECT
    users.uid,
    users.name AS username,
    n1.title AS artist_name
FROM users
LEFT JOIN users_roles ur 
    ON users.uid=ur.uid
INNER JOIN role r 
    ON ur.rid=r.rid 
    AND r.name='artist'
LEFT JOIN node n1 
    ON n1.uid = users.uid 
    AND n1.type = 'submission'
WHERE users.status = 1
ORDER BY users.name;

This gives me data that looks like:

uid username artist_name
1   foo      Joe the Plumber
2   bar      Jane Doe
3   baz      The Tooth Fairy

Also, I've got this query:

# artwork
SELECT
    n.nid, 
    n.uid,
    a.field_order_value
FROM node n
LEFT JOIN content_type_artwork a 
    ON n.nid = a.nid
WHERE n.type = 'artwork' 
ORDER BY n.uid, a.field_order_value;

Which gives me data like this:

nid uid field_order_value
1   1   1
2   1   3
3   1   2
4   2   NULL
5   3   1
6   3   1

Additional relevant info:

• nid is the primary key for an Artwork
• every Artist has one or more Artworks
• valid data for field_order_value is NULL, 1, 2, 3, or 4
• field_order_value is not necessarily unique per Artist - an Artist could have 4 Artworks all with field_order_value = 1.

What I want is the row with the minimum field_order_value from my second query joined with the artist information from the first query. In cases where the field_order_value is not valuable information (either because the Artist has used duplicate values among their Artworks or left that field NULL), I would like the row with the minimum nid from the second query.

---

The Solution

Using divide and conquer as a strategy and mysql views as a technique, and referencing this article about groupwise maximum queries, I solved my problem.

Create the View

# artists and artworks all in one table
CREATE VIEW artists_artwork AS
SELECT
    users.uid,
    users.name AS artist,
    COALESCE(n1.title, 'Not Yet Entered') AS artist_name,
    n2.nid, 
    a.field_image_fid, 
    COALESCE(a.field_order_value, 1) AS field_order_value
FROM users
LEFT JOIN users_roles ur 
    ON users.uid=ur.uid
INNER JOIN role r 
    ON ur.rid=r.rid 
    AND r.name='artist'
LEFT JOIN node n1 
    ON n1.uid = users.uid 
    AND n1.type = 'submission'
LEFT JOIN node n2
    ON n2.uid = users.uid
    AND n2.type = 'artwork'
LEFT JOIN content_type_artwork a ON n2.nid = a.nid
WHERE users.status = 1;

Query the View

SELECT
    a2.uid, 
    a2.artist, 
    a2.artist_name, 
    a2.nid, 
    a2.field_image_fid, 
    a2.field_order_value
FROM (
    SELECT
        uid, 
        MIN(field_order_value) AS field_order_value
    FROM artists_artwork
    GROUP BY uid
    ) a1
JOIN artists_artwork a2
    ON a2.nid = (
        SELECT
            nid
        FROM artists_artwork a
        WHERE a.uid = a1.uid
            AND a.field_order_value = a1.field_order_value
        ORDER BY
            uid ASC, field_order_value ASC, nid ASC
        LIMIT 1
)
ORDER BY artist;

Answer 1

A simple solution to this can be to create views in your database that can then be joined together. This is especially useful if you often want to see the intermediate data in the same way in some other place. While it is possible to mash together the one huge query, I just take the divide and conquer approach sometimes.