current community

your communities

more stack exchange communities

Stack Exchange
tour help
careers 2.0
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 4 down vote favorite

I did multiple searches for an answer to this (on SO and elsewhere), but haven't found an answer that really fit my needs (if it's out there, I apologize in advance).

I have a query, using PHP, that returns an array from a database (WordPress). Basically what I want to do is look at a column's value, and then LIMIT based on that value. Here's the array that's returned for a better idea:

http://pastebin.com/AC043qfh

In the query, you'll notice that the value for post_parent repeats for several of returned arrays. What I want to do is have it LIMIT to 3 based on the post_parent value e.g. I want 3 entries for post_parent 79, 87, 100, etc.

I'm not well versed (see: at all) in MySQL queries, but this is what I have to get that array:

SELECT DISTINCT ID, guid, post_parent, post_title 
FROM $wpdb->posts p 
WHERE p.post_type = 'attachment'
    AND p.post_mime_type LIKE 'image/%'
    AND p.post_status = 'inherit'
    AND p.post_parent IN
        (SELECT object_id FROM $term_relationships WHERE term_taxonomy_id = $post_term)

I've tried using GROUP BY, but that didn't get me what I wanted. Any help is appreciated.

EDIT Just to clarify, these are the results I want: http://pastebin.com/pWXdUuXv

share|improve this question
    
Could you explain the rules to your limit? I mean, I can give you a query that will limit it to three, but that doesn't sound like what you want. –  Justin Wood Sep 13 '13 at 15:59
    
@Justin Wood He did - "I want 3 entries for post_parent 79, 87, 100, etc." –  SamV Sep 13 '13 at 16:00
    
I'd recommend creating a "rank" and then use a conditional based on that rank... Changing LIMIT would require dynamic SQL at the least. –  ebyrob Sep 13 '13 at 16:01
    
@Sam and like I said, I can create a query that can limit it to three, but he wants it based off of that field, which means it may not ALWAYS be a limit of three. –  Justin Wood Sep 13 '13 at 16:02
2  
There's a tag for this: greatest-n-per-group. Try searching that tag. –  Barmar Sep 13 '13 at 16:26
show 5 more comments

1 Answer

up vote 3 down vote accepted

This might do the trick: (I'm assuming ID is unique, if not substitute something that is)

SELECT
  p.ID, guid, post_parent, post_title
FROM (
SELECT
  a.ID as ID,
  COUNT(*) as rank
FROM (
  SELECT ID, post_parent
  FROM $wpdb->posts
  WHERE post_type = 'attachment'
    AND post_mime_type LIKE 'image/%'
    AND post_status = 'inherit'
  ) AS a
JOIN (
  SELECT ID, post_parent
  FROM $wpdb->posts
  WHERE post_type = 'attachment'
    AND post_mime_type LIKE 'image/%'
    AND post_status = 'inherit'
  ) AS b ON b.ID <= a.ID AND b.post_parent = a.post_parent
GROUP BY a.ID
) AS r
JOIN $wpdb->posts p ON r.ID = p.ID AND r.rank <= 3
WHERE p.post_parent IN (
  SELECT object_id FROM $term_relationships
  WHERE term_taxonomy_id = $post_term)
GROUP BY p.ID
;

EDIT: Attempt to include category in rank so it'll actually work.

Specifying conditions twice is a bit ugly, but I didn't see an easy way around it.

share|improve this answer
    
Yeah, ID is unique to each image in the DB. post_parent is unique to the post that the image appears on, but obviously some images will share the same post_parent value (considering the question at hand). I tried running this as is and I didn't get any results, however, I'll take a look at modifying it as I see what you are getting at doing. –  disinfor Sep 13 '13 at 17:03
1  
doh, forgot a critical component of the rank, working on fixing... –  ebyrob Sep 13 '13 at 18:10
    
ebyrob your edit did the trick and does exactly what I wanted to do. Thank you! –  disinfor Sep 13 '13 at 18:24
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.