MeoModo Meeting Finder plugin (WordPress)

Uit De Vliegende Brigade
Versie door Jeroen Strompf (overleg | bijdragen) op 4 okt 2023 om 18:54 (→‎See also)
(wijz) ← Oudere versie | Huidige versie (wijz) | Nieuwere versie → (wijz)
Naar navigatie springen Naar zoeken springen
After installation, a new menu item Meeting Finder becomes available in the left admin menu

There are surely lots of non-public plugins or other solutions for listing 12 Step meetings on WordPress websites. One of these is MeoModo DWS's Meeting Finder. This plugin was custom developed for Alcoholics Anonymous in The Netherlands. As far as I am concerned, it is not supported anymore.

Data model

To start with what might be obvious: Meetings are stored within the WordPress database - Not as a separate database. Why this is pointed out here: I an instance in 2022/2023, I came across a siutuation where there was an additional 'meeting database' active on the same server as the WordPress database. I still don't know what that database is, but it's not for the MeoModo Meeting Finder plugin.

Meeting posts

The basic data object seems to be posts of the type aamf_meeting. E.g.:

select
   *
from
   aanl_posts
where
   post_type="aamf_meeting";

Each record is a group, and not a meeting: A multi-meeting group has only one entry in this table.

post_title

The name of the meeting: Sometimes the various meeting that make up a multi-meeting group, have different names.

post_content

Just a meeting description.

meta fields

All additional data seems to be stored in table aanl_postmeta.

To get an overview of all fields:

select distinct
   meta_key #,
   # meta_value
from
   aanl_posts
join
   aanl_postmeta
   on
   aanl_posts.ID = aanl_postmeta.post_id
where
   post_type="aamf_meeting"
order by
   meta_key;

In total, there seem to be only seven meta fields, two of which are serialised, with the meta field address comprising of several fields of its own.

meta - group_name

  • group_name (actually group but that is a reserved keyword in SQL): The name of the group
  • For multi-meeting groups where meetings have different names from groups: The group name
  • For most groups, this will be the same value as for meeting.

meta - location

A short identifier of the location. E.g. Old Catholic Church Amsterdam, or Theater Polanenstraat Amsterdam

meta - address (serialised)

  • The actual address of the group, in serialised form
  • Some of the fields are machine-intelligable, like the geographic coordinates, but some fields seem quite 'random'
  • Those 'random fields' might just be a serialised table: Every table is on its own. Fields are defined locally only, and there is no consistency.

Example of a face-to-face meeting:

a:3:
{
   s:7:"address";   s:53:"Nassau Church, De Wittenstraat 114, 1052 BB Amsterdam";
   s:3:"lat";       s:10:"52.3825978";
   s:3:"lng";       s:17:"4.877457499999991";
}

Example of a Zoom-meeting - Formatted in pairs (so that a:8: makes sense):

a:8:
{
    s:7:"address";          s:11:"Netherlands";
    s:3:"lat";              d:52.132632999999998;
    s:3:"lng";              d:5.2912659999999994;
    s:4:"zoom";             i:14;
    s:8:"place_id";         s:27:"ChIJu-SH28MJxkcRnwq9_851obM";   # What is this???
    s:4:"name";             s:11:"Netherlands";
    s:7:"country";          s:11:"Netherlands";
    s:13:"country_short";   s:2:"NL";
}

Unclear what the string after s:27: is: Zoom 'coordinates' are stored in the post_content field.

All fields:

  • address: The actual address, e.g., street, number, zip code, city, all in one string without further classification
  • country
  • country_short
  • lat Latitude, stored as string with "." as decimal separator
  • lng Longitude, stored as string with "." as decimal separator
  • name
  • place_id
  • zoom - An integer value

meta - day_of_week (serialised)

day_of_week (actually dayofweek but I like consistency), is a serialised field that indicates the days on which the meeting happens:

  • Sunday = 0
  • Monday = 1
  • Etc.

meta - start_time

Just the local start time. e.g., 17:30.

meta - end_time

Just the local end time. e.g., 18:30.

meta - gsr

A freeform text field to write whatever you want about the GSR. Usually just first name, initial & telephone number.

Tags

These are probably the tags used at this site
When editing a meeting object, these tags are available
Got it! These are just the regular tags for posts

Complete dataset - Except for tags

################################################################################
### Complete dataset - Except for tags
################################################################################
#
select
   ID,
   post_date,
   post_date_gmt,
   post_content,
   post_title,
   post_status,
   post_name,
   meta_group_name.meta_value as group_name,   
   meta_address.meta_value    as address,
   meta_location.meta_value   as location,
   meta_dayofweek.meta_value  as day_of_week,
   meta_start_time.meta_value as start_time,
   meta_end_time.meta_value   as end_time,
   meta_gsr.meta_value        as gsr
from
   aanl_posts
join
   aanl_postmeta as meta_group_name
   on
   aanl_posts.ID = meta_group_name.post_id
   and
   meta_group_name.meta_key = "group"
join
   aanl_postmeta as meta_location
   on
   aanl_posts.ID = meta_location.post_id
   and
   meta_location.meta_key = "location"
join
   aanl_postmeta as meta_address
   on
   aanl_posts.ID = meta_address.post_id
   and
   meta_address.meta_key = "address"
join
   aanl_postmeta as meta_dayofweek
   on
   aanl_posts.ID = meta_dayofweek.post_id
   and
   meta_dayofweek.meta_key = "dayofweek"
join
   aanl_postmeta as meta_start_time
   on
   aanl_posts.ID = meta_start_time.post_id
   and
   meta_start_time.meta_key = "start_time"
join
   aanl_postmeta as meta_end_time
   on
   aanl_posts.ID = meta_end_time.post_id
   and
   meta_end_time.meta_key = "end_time"
join
   aanl_postmeta as meta_gsr
   on
   aanl_posts.ID = meta_gsr.post_id
   and
   meta_gsr.meta_key = "gsr"

Export for use in TSML

Export of all meeting data, for import in the TSML plugin, The names of the columns, refers to the template that can be downloaded from within the TSML plugin.

################################################################################
### Complete dataset - Except tags » table meeting_tmp
################################################################################
#
drop table if exists
    meeting_tmp;

create table
    meeting_tmp
select
    #
    # start_time - Columns A
    ########################################
    #
    meta_start_time.meta_value as start_time,
    

    # end_time - Column B
    ########################################
    #
    meta_end_time.meta_value as end_time,

   
    # day_of_week - Column C
    ########################################
    #
    meta_dayofweek.meta_value as day_of_week,
    
    
    # meeting_name - Column D
    ########################################
    #
    # post_title is the name of the meeting, NOT of the group
    # Example: group "The Hague Multi-Group":
    # * #778 - "Big Book Study Group - ZOOM MEETING" &
    # * #785 - "Candlelight topic meeting - FACE TO FACE"
    # * #786 - "Saturday Night Discussion Group - FACE TO FACE"
    #
    post_title as meeting_name,


    # meeting_location - Column E
    ########################################
    #
    meta_location.meta_value as meeting_location,


    # meeting_address - Column F
    ########################################
    #
    meta_address.meta_value as meeting_address,


    # meeting_notes - Column J
    ########################################
    #
    post_content as meeting_notes,
    

    # group_name - Column L
    ########################################
    #
    meta_group.meta_value as group_name,    


    # contact_1_name - Column V
    ########################################
    #
    meta_gsr.meta_value as contact_1_name, 


    # contact_1_email - Column W
    ########################################
    #
    # Include this column, because it will be used
    #
    meta_gsr.meta_value as contact_1_email, 


    # contact_1_phone - Column X
    ########################################
    #
    # Include this column, because it will be used
    #
    meta_gsr.meta_value as contact_1_phone, 


    # meeting_slug - Column AI
    ########################################
    #
    post_name as meeting_slug,


    # internal_post_id
    ########################################
    #
    ID as internal_post_id


    # Not used
    ########################################
    #
    # post_date,	# Timestamp creation post - Not relevant
    # post_date_gmt,	# Timestamp creation post - Not relevant
    # post_status,
    #
from
    aanl_posts
join
    aanl_postmeta as meta_group
    on
    aanl_posts.ID = meta_group.post_id
    and
    meta_group.meta_key = "group"
join
    aanl_postmeta as meta_location
    on
    aanl_posts.ID = meta_location.post_id
    and
    meta_location.meta_key = "location"
join
    aanl_postmeta as meta_address
    on
    aanl_posts.ID = meta_address.post_id
    and
    meta_address.meta_key = "address"
join
    aanl_postmeta as meta_dayofweek
    on
    aanl_posts.ID = meta_dayofweek.post_id
    and
    meta_dayofweek.meta_key = "dayofweek"
join
    aanl_postmeta as meta_start_time
    on
    aanl_posts.ID = meta_start_time.post_id
    and
    meta_start_time.meta_key = "start_time"
join
    aanl_postmeta as meta_end_time
    on
    aanl_posts.ID = meta_end_time.post_id
    and
    meta_end_time.meta_key = "end_time"
join
    aanl_postmeta as meta_gsr
    on
    aanl_posts.ID = meta_gsr.post_id
    and
    meta_gsr.meta_key = "gsr"
where
    post_status = "publish";


################################################################################
### Select meetings + tags
################################################################################
#
# These are all tags from the taxonomy "post_tag"
#
#
# Example: tag "Sobriety Chips"
########################################
#
# Table aanl_terms:
# * term_id:	122
# * name: 		Sobriety Chips
# * slug: 		sobriety-chips
#
# Table aanl_term_taxonomy:
# * term_taxonomy_id:	122
# * term_id:			122
# * taxonomy: 		 	post_tag
#
# Table aanl_term_relationships:
# * object_id:			786 ← Only one object associated with this tag
# * term_taxonomy_id:	122
#
select distinct
    meeting_tmp.*,
    group_concat(distinct aanl_terms.name separator ", ") as terms
from
    meeting_tmp
join
    aanl_term_relationships
    on
    meeting_tmp.internal_post_id = aanl_term_relationships.object_id
join
    aanl_terms
    on
    aanl_term_relationships.term_taxonomy_id = aanl_terms.term_id
group by
    internal_post_id

See also

Sources