Help with Nested MIGXdb

44 Posts

tsnekifox Reply #1, 12 years, 3 months ago

Hello everyone, I am attempting to create a conference agenda system for a project and I need a little help working out the schema to make this all work. Below is the plan I have started with. My main issue is understanding how to connect the data I need between the nested databases.

Below is my schema so far. I am attempting to do the following things:
• Populate spk_conf, loc_conf, and ses_conf with the conf_year
• Populate ses_loc with (loc_building, loc_room)
• Populate ses_spk1 - ses_spk5 with (spk_lname, spk_fname)

<model package="sriagenda" baseClass="xPDOObject" platform="mysql" defaultEngine="MyISAM" version="1.1">
    
    <object class="sriagenda" table="sriagenda" extends="xPDOSimpleObject">
        <field key="conf_year" dbtype="varchar" precision="255" phptype="string" null="false" default=""/>
        <field key="conf_theme" dbtype="varchar" precision="255" phptype="string" null="false" default=""/>
        <field key="conf_start" dbtype="datetime" phptype="datetime" null="false"/>
        <field key="conf_end" dbtype="datetime" phptype="datetime" null="false"/>
        <field key="conf_status" dbtype="varchar" precision="255" phptype="string" null="false" default=""/>
        <field key="createdon" dbtype="datetime" phptype="datetime" null="true"/>
        <field key="deleted" dbtype="tinyint" precision="1" attributes="unsigned" phptype="integer" null="false" default="0"/>
        <field key="published" dbtype="tinyint" precision="1" attributes="unsigned" phptype="integer" null="false" default="1"/>
    </object>
    
    <object class="sriagenda_spk" table="sriagenda_spk" extends="xPDOSimpleObject">
        <field key="spk_salutation" dbtype="varchar" precision="255" phptype="string" null="false" default=""/>
        <field key="spk_fname" dbtype="varchar" precision="255" phptype="string" null="false" default=""/>
        <field key="spk_mname" dbtype="varchar" precision="255" phptype="string" null="false" default=""/>
        <field key="spk_lname" dbtype="varchar" precision="255" phptype="string" null="false" default=""/>
        <field key="spk_suffix" dbtype="varchar" precision="255" phptype="string" null="false" default=""/>
        <field key="spk_credentials" dbtype="varchar" precision="255" phptype="string" null="false" default=""/>
        <field key="spk_affiliation1" dbtype="varchar" precision="255" phptype="string" null="false" default=""/>
        <field key="spk_affiliation2" dbtype="varchar" precision="255" phptype="string" null="false" default=""/>
        <field key="spk_title1" dbtype="varchar" precision="255" phptype="string" null="false" default=""/>
        <field key="spk_title2" dbtype="varchar" precision="255" phptype="string" null="false" default=""/>
        <field key="spk_photo" dbtype="text" phptype="string" null="false" default=""/>
        <field key="spk_bio" dbtype="text" phptype="string" null="false" default=""/>
        <field key="spk_phone" dbtype="varchar" precision="255" phptype="string" null="false" default=""/>
        <field key="spk_email" dbtype="varchar" precision="255" phptype="string" null="false" default=""/>
        <field key="spk_conf" dbtype="varchar" precision="255" phptype="string" null="false" default=""/>
        <field key="spk_type" dbtype="varchar" precision="255" phptype="string" null="false" default=""/>
        <field key="spk_carousel" dbtype="varchar" precision="255" phptype="string" null="false" default=""/>
        <field key="spk_order" dbtype="varchar" precision="255" phptype="string" null="false" default=""/>
        <field key="spk_notes" dbtype="text" phptype="string" null="false" default=""/>
        <field key="createdon" dbtype="datetime" phptype="datetime" null="true"/>
        <field key="deleted" dbtype="tinyint" precision="1" attributes="unsigned" phptype="integer" null="false" default="0"/>
        <field key="published" dbtype="tinyint" precision="1" attributes="unsigned" phptype="integer" null="false" default="1"/>
    </object>
     
    <object class="sriagenda_loc" table="sriagenda_loc" extends="xPDOSimpleObject">
        <field key="loc_conf" dbtype="varchar" precision="255" phptype="string" null="false" default=""/>
        <field key="loc_building" dbtype="varchar" precision="255" phptype="string" null="false" default=""/>
        <field key="loc_room" dbtype="varchar" precision="255" phptype="string" null="false" default=""/>
        <field key="createdon" dbtype="datetime" phptype="datetime" null="true"/>
        <field key="deleted" dbtype="tinyint" precision="1" attributes="unsigned" phptype="integer" null="false" default="0"/>
        <field key="published" dbtype="tinyint" precision="1" attributes="unsigned" phptype="integer" null="false" default="1"/>
    </object>
    
    <object class="sriagenda_ses" table="sriagenda_ses" extends="xPDOSimpleObject">
        <field key="ses_conf" dbtype="varchar" precision="255" phptype="string" null="false" default=""/>
        <field key="ses_title" dbtype="varchar" precision="255" phptype="string" null="false" default=""/>
        <field key="ses_type" dbtype="varchar" precision="255" phptype="string" null="false" default=""/>
        <field key="ses_loc" dbtype="varchar" precision="255" phptype="string" null="false" default=""/>
        <field key="ses_start" dbtype="datetime" phptype="datetime" null="false"/>
        <field key="ses_end" dbtype="datetime" phptype="datetime" null="false"/>
        <field key="ses_desc" dbtype="text" phptype="string" null="false" default=""/>
        <field key="ses_cecredit" dbtype="varchar" precision="255" phptype="string" null="false" default=""/>
        <field key="ses_spk1" dbtype="varchar" precision="255" phptype="string" null="false" default=""/>
        <field key="ses_spk2" dbtype="varchar" precision="255" phptype="string" null="false" default=""/>
        <field key="ses_spk3" dbtype="varchar" precision="255" phptype="string" null="false" default=""/>
        <field key="ses_spk4" dbtype="varchar" precision="255" phptype="string" null="false" default=""/>
        <field key="ses_spk5" dbtype="varchar" precision="255" phptype="string" null="false" default=""/>
        <field key="ses_pres1" dbtype="varchar" precision="255" phptype="string" null="false" default=""/>
        <field key="ses_pres2" dbtype="varchar" precision="255" phptype="string" null="false" default=""/>
        <field key="ses_pres3" dbtype="varchar" precision="255" phptype="string" null="false" default=""/>
        <field key="ses_pres4" dbtype="varchar" precision="255" phptype="string" null="false" default=""/>
        <field key="ses_pres5" dbtype="varchar" precision="255" phptype="string" null="false" default=""/>
        <field key="ses_audio" dbtype="varchar" precision="255" phptype="string" null="false" default=""/>
        <field key="ses_video" dbtype="varchar" precision="255" phptype="string" null="false" default=""/>
        <field key="createdon" dbtype="datetime" phptype="datetime" null="true"/>
        <field key="deleted" dbtype="tinyint" precision="1" attributes="unsigned" phptype="integer" null="false" default="0"/>
        <field key="published" dbtype="tinyint" precision="1" attributes="unsigned" phptype="integer" null="false" default="1"/>
    </object>
    
</model>

Any help would be appreciated!

This question has been answered by Bruno17. See the first response.

5,888 Posts

Bruno17 Reply #2, 12 years, 3 months ago

shouldn't it be some like that:

<model package="sriagenda" baseClass="xPDOObject" platform="mysql" defaultEngine="MyISAM" version="1.1">
     
    <object class="sriagenda" table="sriagenda" extends="xPDOSimpleObject">
        <field key="conf_year" dbtype="varchar" precision="255" phptype="string" null="false" default=""/>
        <field key="conf_theme" dbtype="varchar" precision="255" phptype="string" null="false" default=""/>
        <field key="conf_start" dbtype="datetime" phptype="datetime" null="false"/>
        <field key="conf_end" dbtype="datetime" phptype="datetime" null="false"/>
        <field key="conf_status" dbtype="varchar" precision="255" phptype="string" null="false" default=""/>
        <field key="createdon" dbtype="datetime" phptype="datetime" null="true"/>
        <field key="deleted" dbtype="tinyint" precision="1" attributes="unsigned" phptype="integer" null="false" default="0"/>
        <field key="published" dbtype="tinyint" precision="1" attributes="unsigned" phptype="integer" null="false" default="1"/>
   
        <composite alias="Sessions" class="sriagenda_ses" local="id" foreign="agenda_id" cardinality="many" owner="local" /> 
        
    </object>
     
    <object class="sriagenda_spk" table="sriagenda_spk" extends="xPDOSimpleObject">
        <field key="spk_salutation" dbtype="varchar" precision="255" phptype="string" null="false" default=""/>
        <field key="spk_fname" dbtype="varchar" precision="255" phptype="string" null="false" default=""/>
        <field key="spk_mname" dbtype="varchar" precision="255" phptype="string" null="false" default=""/>
        <field key="spk_lname" dbtype="varchar" precision="255" phptype="string" null="false" default=""/>
        <field key="spk_suffix" dbtype="varchar" precision="255" phptype="string" null="false" default=""/>
        <field key="spk_credentials" dbtype="varchar" precision="255" phptype="string" null="false" default=""/>
        <field key="spk_affiliation1" dbtype="varchar" precision="255" phptype="string" null="false" default=""/>
        <field key="spk_affiliation2" dbtype="varchar" precision="255" phptype="string" null="false" default=""/>
        <field key="spk_title1" dbtype="varchar" precision="255" phptype="string" null="false" default=""/>
        <field key="spk_title2" dbtype="varchar" precision="255" phptype="string" null="false" default=""/>
        <field key="spk_photo" dbtype="text" phptype="string" null="false" default=""/>
        <field key="spk_bio" dbtype="text" phptype="string" null="false" default=""/>
        <field key="spk_phone" dbtype="varchar" precision="255" phptype="string" null="false" default=""/>
        <field key="spk_email" dbtype="varchar" precision="255" phptype="string" null="false" default=""/>
        <field key="spk_conf" dbtype="varchar" precision="255" phptype="string" null="false" default=""/>
        <field key="spk_type" dbtype="varchar" precision="255" phptype="string" null="false" default=""/>
        <field key="spk_carousel" dbtype="varchar" precision="255" phptype="string" null="false" default=""/>
        <field key="spk_notes" dbtype="text" phptype="string" null="false" default=""/>
        <field key="createdon" dbtype="datetime" phptype="datetime" null="true"/>
        <field key="deleted" dbtype="tinyint" precision="1" attributes="unsigned" phptype="integer" null="false" default="0"/>
        <field key="published" dbtype="tinyint" precision="1" attributes="unsigned" phptype="integer" null="false" default="1"/>
        
        <composite alias="Sessions" class="sriagenda_ses_spk" local="id" foreign="ses_id" cardinality="many" owner="local" />         
    </object>
      
    <object class="sriagenda_loc" table="sriagenda_loc" extends="xPDOSimpleObject">
        <field key="loc_building" dbtype="varchar" precision="255" phptype="string" null="false" default=""/>
        <field key="loc_room" dbtype="varchar" precision="255" phptype="string" null="false" default=""/>
        <field key="createdon" dbtype="datetime" phptype="datetime" null="true"/>
        <field key="deleted" dbtype="tinyint" precision="1" attributes="unsigned" phptype="integer" null="false" default="0"/>
        <field key="published" dbtype="tinyint" precision="1" attributes="unsigned" phptype="integer" null="false" default="1"/>
        
        <composite alias="Sessions" class="sriagenda_ses" local="id" foreign="loc_id" cardinality="many" owner="local" /> 
    </object>
    
    <object class="sriagenda_ses" table="sriagenda_ses" extends="xPDOSimpleObject">
        <field key="agenda_id" dbtype="int" phptype="integer" precision="10" null="false" default="" index="index" />        
        <field key="loc_id" dbtype="int" phptype="integer" precision="10" null="false" default="" index="index" /> 
        <field key="ses_title" dbtype="varchar" precision="255" phptype="string" null="false" default=""/>
        <field key="ses_type" dbtype="varchar" precision="255" phptype="string" null="false" default=""/>
        <field key="ses_start" dbtype="datetime" phptype="datetime" null="false"/>
        <field key="ses_end" dbtype="datetime" phptype="datetime" null="false"/>
        <field key="ses_desc" dbtype="text" phptype="string" null="false" default=""/>
        <field key="ses_cecredit" dbtype="varchar" precision="255" phptype="string" null="false" default=""/>
        <field key="ses_pres1" dbtype="varchar" precision="255" phptype="string" null="false" default=""/>
        <field key="ses_pres2" dbtype="varchar" precision="255" phptype="string" null="false" default=""/>
        <field key="ses_pres3" dbtype="varchar" precision="255" phptype="string" null="false" default=""/>
        <field key="ses_pres4" dbtype="varchar" precision="255" phptype="string" null="false" default=""/>
        <field key="ses_pres5" dbtype="varchar" precision="255" phptype="string" null="false" default=""/>
        <field key="ses_audio" dbtype="varchar" precision="255" phptype="string" null="false" default=""/>
        <field key="ses_video" dbtype="varchar" precision="255" phptype="string" null="false" default=""/>
        <field key="createdon" dbtype="datetime" phptype="datetime" null="true"/>
        <field key="deleted" dbtype="tinyint" precision="1" attributes="unsigned" phptype="integer" null="false" default="0"/>
        <field key="published" dbtype="tinyint" precision="1" attributes="unsigned" phptype="integer" null="false" default="1"/>
        
    	<aggregate alias="Agenda" class="sriagenda" local="agenda_id" foreign="id" cardinality="one" owner="foreign" /> 
        <aggregate alias="Location" class="sriagenda_loc" local="loc_id" foreign="id" cardinality="one" owner="foreign" />             
        <composite alias="Speakers" class="sriagenda_ses_spk" local="id" foreign="ses_id" cardinality="many" owner="local" /> 
          
    </object>
    
    <object class="sriagenda_ses_spk" table="sriagenda_ses_spk" extends="xPDOSimpleObject">
        <field key="ses_id" dbtype="int" phptype="integer" precision="10" null="false" default="" index="index" />
        <field key="spk_id" dbtype="int" phptype="integer" precision="10" null="false" default="" index="index" />
        <field key="order" dbtype="varchar" precision="255" phptype="string" null="false" default=""/>
        <field key="notes" dbtype="text" phptype="string" null="false" default=""/>        
        
        <aggregate alias="Session" class="sriagenda_ses" local="ses_id" foreign="id" cardinality="one" owner="foreign" /> 
        <aggregate alias="Speaker" class="sriagenda_spk" local="spk_id" foreign="id" cardinality="one" owner="foreign" />         
    </object>    
    
    
     
</model>

-------------------------------

you can buy me a beer, if you like MIGX

http://webcmsolutions.de/migx.html

Thanks!

44 Posts

tsnekifox Reply #3, 12 years, 2 months ago

That makes a lot of sense. I will give it a try and let you know if I have any other questions.

44 Posts

tsnekifox Reply #4, 12 years, 2 months ago

Bruno
Your post helped a lot! Now I am attempting to use a listbox-multiple for the joining table so you can select more than one speaker for each session. I have been looking though this thread for inspiration, however I have run into issues in editing the code to work for what I need.

https://forums.modx.com/thread/90890/migxdb-cmp---creating-many-to-many-relationships#dis-post-497846

Any help would be greatly appreciated!

5,888 Posts

Bruno17 Reply #5, 12 years, 2 months ago

don't you need an order and additional infos for each speaker?
Then you would be better with a MIGX-TV for adding speakers.
This would store them into a text-field(speakers), as a json-array,
instaed of putting them into an extra-table.

Then you don't need the sriagenda_ses_spk - table.

If you need that sriagenda_ses_spk - table, because you need a way to get also the speakers-sessions easylie, than you would need an
customized update-processor, which gets that json-items and stores them to the sriagenda_ses_spk - table and a customized fields-processor to build the json from that table for the MIGX-field.

-------------------------------

you can buy me a beer, if you like MIGX

http://webcmsolutions.de/migx.html

Thanks!

44 Posts

tsnekifox Reply #6, 12 years, 2 months ago

Hmm, maybe you can help me with understanding what all I need. My intention is to have an Agenda page in the site that will have a tab for each day of the conference. Inside the tab will be a list of all of the sessions for the day which will include the name, session type, start and end times, and a list of speakers. If you select a session you would get a new page that has all of the session information on it and the names of all the speakers. Those speaker names would also be links to speaker profiles which ideally in addition to having all of the main speaker information, would include a list to any sessions that the speaker is assigned. Separately there is a speaker page that will list all of the speakers alphabetically and would link to the speaker profile pages as well.

By the time I get this all figured out I think I'm going to want to buy you a case or two of beer, hah.

5,888 Posts

Bruno17 Reply #7, 12 years, 2 months ago

will put a starting-package together and upload it on github

-------------------------------

you can buy me a beer, if you like MIGX

http://webcmsolutions.de/migx.html

Thanks!

44 Posts

tsnekifox Reply #8, 12 years, 2 months ago

Bruno
I really appreciate that! I cant wait to see what your putting together.

5,888 Posts

Bruno17 Reply #9, 12 years, 2 months ago

here is something for the backend, you can play with

https://github.com/Bruno17/sriagenda

caution! If you install that package, this will override your files in your package and your table-structures, if they are different!

-------------------------------

you can buy me a beer, if you like MIGX

http://webcmsolutions.de/migx.html

Thanks!

discuss.answer

5,888 Posts

Bruno17 Reply #10, 12 years, 2 months ago

see this class, how it saves the MIGX-items to the sriagenda_ses_spk - table

https://github.com/Bruno17/sriagenda/blob/master/core/components/sriagenda/model/sriagenda/sriagenda_ses.class.php

this are all configs:
https://github.com/Bruno17/sriagenda/tree/master/core/components/sriagenda/migxconfigs

you can import them into the grid, by clicking 'import from package'

your menu should have this params:
&configs=sriagenda||sriagenda_spk||sriagenda_loc

sessions is a nested MIGXdb-field inside agendas
speakers is a nested MIGX-field inside sessions

-------------------------------

you can buy me a beer, if you like MIGX

http://webcmsolutions.de/migx.html

Thanks!

Answered Help with Nested MIGXdb