1

I've the dump of civicrm_mailing_event_delivered table. It has 3 fields - id, event_queue_id and timestamp.

By looking at this table, how do I find the mails belonging to one particular mailing? Why is it wrong to assume that every mail in a mailing is sent at the same time? When I do Administer->Settings->Schedule Jobs-> Send Scheduled mailings -> Execute now, it sends all the mails in the mailing at the same time.

Improve this question
1
  • You might want to do some quick reading on database design, specifically SQL schemas and normalized form. Your gsoc mentor should be Able to get u going on the right patb
    – Donald Lobo
    Commented Jun 8, 2015 at 3:34

3 Answers 3

Reset to default
3

Try the civicrm_mailing_recipients table - mailing_id tells you which Mailing they relate to. Is that what you are after?

Improve this answer
4
  • What does id refer to in that table?
    – Mohit Agarwal
    Commented Jun 7, 2015 at 20:22
  • just the row id for that table
    – petednz - fuzion ♦
    Commented Jun 8, 2015 at 2:28
  • Using just the civicrm_mailing_recipients table, I get to know which email_ids belong to a particular mailing. But civicrm_mailing_event_delivered table does not contain the email ids, rather it stores information about event_queue_id. This means I would have to do join of 3 tables - civicrm_mailing_event_delivered, civicrm_mailing_recipients and civicrm_mailing_event_queue in order to find which all timestamps (of delivered mail) belong to a particular mailing. Am I right?
    – Mohit Agarwal
    Commented Jun 8, 2015 at 9:58
  • yeap, but they all use indexes, so no big deal
    – Xavier
    Commented Jun 8, 2015 at 13:28
3

The event_queue_id points to a row in the event queue table which has a job id associated with it. The mailing job table is a join table which matches a job to a specific mailing.

Check the civimail reports to give a better idea of the SQL used to construct the queries and extract useful info from the tables

Improve this answer
0

Mailings are split into batches (jobs). On small mailings, you will have a single job and all emails will be sent at the same time, but that's definitely not going to be the case when you have more than one job.

you can download the ERD from the wiki, but as Lobo was saying event->job->mailing Alter your query to add the mailing id (lots of join, but on indexes, it won't increase the speed)

http://wiki.civicrm.org/confluence/display/CRMDOC/CiviCRM+4.4+ER+Diagram

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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