CiviCRM Community Forums (archive)

When I create an event, after setting up the main event details (tab1),  the event is saved as a record of the table civicrm_event.

Then when I complete and save tab2 (location details), a record of the civicrm_address is created with the details.

But I cannot seem to find a mapping table, that links the two and the event table doesn't store the id of the address record and the address record doesn't store the event id.

There, must be something somewhere that provides a link.

Thanks, but that doesn't seem to be what I am after.

Basicaly, I am trying to pipe all civi events to a google calender.

On the civicrm_post to database hook, I can detect a new event record being created, pull the information I want from that and pipe it to google calender no problems.

But I want to also store the location of the event in g. Calender aswell.

So when the db address record is saved, I can detect this, but I now need to pull the associate event record for this address record as I have a mapping table that maps G.Calender uri to the event record id. So in order to get the gCalender uri I need the event record ID so I can add the details from the location record being saved.

So I need to go from address .ID to event.ID and I cannot seem to find a join anywwhere

Assumption wrong, did not know that

Access denied on the link btw

No go I'm afraid, G Calender do not provide an API call to insert the ical details. It would have to be a manual front end process.

So back to the original question,

How does civi link the event database record with the location record. There must be a link as when I go to the event screen it brings up the correct location record.

I'd prefer not to have to step through each record, or the source code if some one knows

Hi user238 - this is not the recomended way of accessing the data...

but i just found this bit of sql:

SELECT
  civicrm_event.id as event_id,
  civicrm_email.email as email,
  civicrm_event.title as summary,
  civicrm_event.start_date as start,
  civicrm_event.end_date as end,
  civicrm_event.description as description,
  civicrm_event.is_show_location as is_show_location,
  civicrm_option_value.label as event_type,
  civicrm_location.name as location_name,
  civicrm_address.street_address as street_address,
  civicrm_address.supplemental_address_1 as supplemental_address_1,
  civicrm_address.supplemental_address_2 as supplemental_address_2,
  civicrm_address.city as city,
  civicrm_address.postal_code as postal_code,
  civicrm_address.postal_code_suffix as postal_code_suffix,
  civicrm_state_province.abbreviation as state,
  civicrm_country.name as country,
  civicrm_location_type.name as location_type
FROM civicrm_event
LEFT JOIN civicrm_location ON (civicrm_location.entity_table = 'civicrm_event' AND
                               civicrm_event.id = civicrm_location.entity_id )
LEFT JOIN civicrm_address ON civicrm_location.id = civicrm_address.location_id
LEFT JOIN civicrm_state_province ON civicrm_address.state_province_id = civicrm_state_province.id
LEFT JOIN civicrm_country ON civicrm_address.country_id = civicrm_country.id
LEFT JOIN civicrm_location_type ON civicrm_location_type.id = civicrm_location.location_type_id
LEFT JOIN civicrm_email ON civicrm_location.id = civicrm_email.location_id
LEFT JOIN  civicrm_option_value ON (
                                    civicrm_event.event_type_id = civicrm_option_value.value AND
                                    civicrm_option_value.option_group_id = %1 )
WHERE civicrm_event.is_active = 1
      AND civicrm_event.domain_id = %2
      AND civicrm_event.is_public = 1
      AND civicrm_event.start_date >= ". $condition .
" ORDER BY   civicrm_event.start_date ASC";