Have Geek Brain

Complex MySQL query for news queue

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

  • newsletters - each line contains headers 'id', 'subject', 'body' and 'from' for email
  • newsletter_queue - each line contains the address "id", "email", "date" added to the queue, and "newsletterid"

My goal is to develop a MySQL query that can pull x number of rows from "newsletter_queue" and then group them by their "newsletter" when using GROUP_CONCAT (or whatever works) to put all emails into a string. separated by characters, which I will deal with with PHP. The reason I would like to have them together is because the mailer library I am using (swiftmailer) accepts an array of emails for batch letters. Also, if possible, it would be very convenient to join the two tables together, thereby avoiding the 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 LIMIT 125 is applied to already concatenated strings, making it useless due to the fact that I am trying to limit the number of all emails sent at a time, not unique newsletters. If anyone could guide me in the right direction, I would be very grateful. If you finish writing the example, that's great too.

+1


a source to share


3 answers 


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 to the inner query, before the group statement is executed. It doesn't matter that the group by clause is in the outer query, as the group will need a temporary table and sort anyway. If you need some sort of ordering of the concatenated result, you can simply apply it to the outer query, for example by specifying max or min on the date and order.

+3


a source


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
+2


a source


Wild idea, but does it 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 suspect the maximum number of email addresses in one record is 125.
Unfortunately, the amount is not accurate 125. It can range from 1 to 125.

0


a source






More articles:


All Articles