Complicated MySQL query for newsletter queue

Question

Hey everyone, I'm back and looking forward to more of your brilliance. I have two tables:

1. newsletters — each row contains a 'id', 'subject', 'body' & 'from' headers for an email
2. newsletter_queue — each row contains an 'id', 'email' address, 'date' added to queue and the 'newsletterid'

My goal is to develop a MySQL query that can pull x amount of rows from 'newsletter_queue', then group them by their 'newsletterid' while using GROUP_CONCAT (or whatever works) to put all the emails into a character separated string which I will parse with PHP. The reason I'd like to have them together is because the mailer library I am using (swiftmailer) accepts an array with emails for batch emails. Also, if possible, it would be very neat to join the two tables together, thereby avoiding a second query.

Here's what I have so far:

SELECT GROUP_CONCAT(email ORDER BY date ASC SEPARATOR '|'), newsletterid, date
FROM newsletter_queue
WHERE status='0'
GROUP BY newsletterid
LIMIT 125

My problem is that the LIMIT 125 is being applied to the already concatenated rows, rendering it useless due to the fact that I'm trying to limit the amount of total emails being sent at a time, not unique newsletters. If anyone could guide me in the right direction, I would be very appreciative. If you wind up writing the example, that's great too.

Answer 1

Wild idea but does this work?

SELECT GROUP_CONCAT(email ORDER BY date ASC SEPARATOR '|'), newsletterid, date
FROM newsletter_queue
WHERE status='0'
GROUP BY newsletterid, ID MOD 125

I'd suspect the maximum amount of email adresses in one record to be 125.
Unfortunately, the amount is not exact 125. It could be between 1 and 125.

Answer 2

SELECT GROUP_CONCAT(email ORDER BY date ASC SEPARATOR '|'), newsletterid, date
FROM 
   (SELECT email, newsletterid, date 
    FROM newsletter_queue
    WHERE status="0"
    ORDER BY date ASC
    LIMIT 125) as Unsent
GROUP BY newsletterid

This applies the limit to the inner query, before the group by statement is executed. It doesn't matter that the group by statement is in the outer query since a group by will need a temporary table and sort anyway. If you need some kind of ordering of the concatenated result, you can just apply it to the outer query, for instance by applying max or min to date and order by it.

Answer 3

I believe sub-queries are the answer. I think this query should work but its syntax need to be corrected:

select a.newsletterid, group_concat(a.email)
from
(
select newsletterid, email from newsletter_queue
where status = '0'
limit 0, 125
) as a
group by a.newsletterid

Answer 4

This should also do what you want:

SELECT substring_index(GROUP_CONCAT(email ORDER BY date ASC SEPARATOR '|'),'|',125), newsletterid, date FROM newsletter_queue WHERE status='0' GROUP BY newsletterid