[Resolved] Many to Many Post Relationship Configuration

[Author]

Posts

[Mike]

Ok I’m back at this. Just read about the new updates regarding importing CSV’s and relationships and this looks promising.

I just cannot for the life of me wrap my head around how to implement and setup the basic structure of the site.

I have two CSV files one contains Voter information the other contains Voter History.

I’ve already created a Voter Post type and imported their information:

Voters Post type:
-Voter Name (Post Title)
-Unique Voter ID Number
-Voter Information (Name, Address, Etc)

I need to create a history for each voter, in past requests I’ve identified this needs to be a M2M relationship. I believe I need the following Post Types:

Elections Post Type
Votes Post Type (intermediary?)

I am struggling with “Elections” and “Votes” Post Types and what fields they should contain this is the data I have in the History CSV:

Voter ID – Unique ID assigned to every Voter, also imported into the Voters post type
Date of Election – The date the election took place, new dates will be added over time
Election – This is the type of election there are only four possibilities: Presidential Primary, State Primary, Presidential Election, State Election.
Method of Vote – There are 3 possibilities: Provisional, At the Polls, or by Mail
Precinct – This is the location of where the voter cast their vote. (don’t think we need this in our database)
Party – This is only in the case of a Presidential Primary or State Primary, and is identified as R or D

Attached is an example of the data

I’m just not sure how to structure the Elections and Votes Post Type.

[Christian]

Ok, let’s try to get your relationship right.

We have:

Post Types:
Elections - Presidential Primary, State Primary, Presidential Election, State Election
Voters - people who can vote (more than once)

As you said we have a m2m relationship between Voters and Election because:
One Election can be voted by many Voters
One Voter can vote for many Elections

I think it makes sense to call this relationship “Votes”, because each associations between Elections and Voters is a “Vote”.

Each “Vote” has the following fields:
- Date of Election
- Method of Vote
- (Precinct - if you want to apply it, it would also be a field of the association)
- (Party - personally I wouldn’t apply this to the database, but if you want it’s also a field of the association)

These are stored on the Intermediary Post Type. The Intermediary Post Type is just there to give the ability to store data related to the association between Parent (Elections) and Children (Voters). By default this would be named “Votes Intermediary Posts”.

Example:
Alfred (Voter) is voting for “Presidential Election” (Election). He vote on the 2017-04-30 (“Date of Election”) by Mail (“Method of Vote”).

One year later: Alfred is voting for “State Election”. He vote on the 2018-01-20 (“Date of Election”) at the Polls (“Method of Vote”).

——

Why I wouldn’t apply party as a field?
It’s a result of a condition. If you want to present your data I would just prove if it’s “Presidential Primary” or “State Primary” and then you know if you have party or not. That’s more flexible than having it on the database in my opinion… but as I said you can also store it on the database.

——

Now to your CSV…

You need one CSV for your “Elections” (or as you only have 4, you may want to add them directly to your WordPress)
You need one CSV for your “Voters”
You need one CSV for your “Votes Intermediary Posts”

I attached screenshots of 3 tables which reflects the above example. The Children (Voters) always holds the data for the relationship. In this case the field is named _toolset_associations_votes. You can read everything about the structure here: https://toolset.com/documentation/user-guides/importing-content-from-csv-with-post-relationships/

Hope that helps.

[Minesh]

Could you please follow the structure given by Christian and try to resolve your issue.

[Mike]

Thank you Christian, this is a great explanation.

Ok, I created the CPT's and intermediary post with custom fields as suggested. Do I need to create a custom field for _toolset_association_votes in the voters CPT?

In this case, the party is important in that the people using the database will need to have the ability to filter by a particular party primary. The example I posted only shows one party letter "D" but there is actually another option "R" So it is important that a user knows this information

I have a question regarding the _toolset_association_votes column and post_title in the intermediary post, there is potential for duplicates. For example:

Alfred voted in the Presidential Election in 2012 and also again in 2016. He would have 2 intermediary posts titled the same see attached screenshots. It seems like there should be a unique ID for each intermediary post?

Also are the "ID" columns in the "elections" and "voters" CSV's custom fields within the custom post type or are they the post ID that are autogenerated with a new post? I would prefer to use an ID from the custom fields since I already have a unique ID for each voter.

Lastly, is there an easy way to populate the _toolset_association_votes column with the intermediary post information I have over 10,000 voters and 26,000 votes? I'm still thinking on this one but any experienced suggestions would be helpful.

[Mike]

Ok, I just tried to connect the posts manually and noticed I cannot add more than one "Presidential Election" to a voter. What I think I may have to do is create a post for each year's election in which case the date field may become irrelevant (or I could include it as part of the "Election" post just so the information is there). So:

Presidential Election 2012
Presidential Election 2016
Statewide General Election 2014
Statewide General Election 2012
Statewide Primary 2014
Statewide Primary 2012
Presidential Primary 2012
Presidential Primary 2016

This is still a relatively small number of posts and easy enough to add a new one after each election. I think this would also solve the duplication problem as each election would have a unique ID.

Still not sure how to populate the _toolset_association_votes column for 10,000 voters.

[Christian]

Hi Robert, sorry for the delay.

Yeah you need to have some automatism here. I don't know how you setup your data (MS Excel, OpenOffice... or maybe even PHP to write the file?).

You know the id/title of the voter and the id/title of the election. So you can automate writing the Intermediary Post Type table using "Vote %election id% + %voter id%" and also the _toolset_association_votes entry:

{!{Presidential Election 2017}!} + {!{Votes: %id of  Presidential Election 2017%- %id of voter%}!}, {!{Presidential Election 2018}!} + {!{Votes:  %id of  Presidential Election 2018%- %id of voter%}!}, and so on...

_toolset_association_votes is just for Types to know there is association, which needs to be imported by Types. Once Types has imported the association it will delete the field _toolset_association_votes of the affected post. So there is no need to setup a custom field _toolset_association_votes.

[Minesh]

Could you please follow the Christian's advise shared with the previous reply.

[Mike]

Sorry for the delay as well.

I have been doing quite a bit of work to get this accomplished.

I have been able to create the CPT's, as well as the CSV files and get them imported. I used excel to format the field properly.

I have run into a couple of problems:

Something I have noticed when I run the Associations it begins to run slow. There may possibly be too many to process, approximately 26,000. It starts off processing them at a decent rate then seems to stall, but I was finally able to let it run through the night. There were a few errors, not many, but these were all due to a formatting mistake I made. I was able to click "start import" this morning and that seemed to complete fine.

Now when I go to a "voter" and scroll down to the related "votes" there is a marching ants bar that just keeps moving like it is processing but it does not go away and the votes never show up.

[Christian]

Hi Robert,

we noticed the performance issue when having a lot of associations to import. It's just caused by the browser, which can not deal with so many table cells... to fix this issue we applied a pagination to the import screen. That solves the performance issue. The release of the fix will be today or tomorrow.

I'm more afraid about your second issue. Is it possible to give me access to your page so I can take a look? I set your next post to "private" so only supporters and you can see your post.

[Minesh]

*** Please make a FULL BACKUP of your database and website.***
I would also eventually need to request temporary access (WP-Admin and FTP) to your site. Preferably to a test site where the problem has been replicated if possible in order to be of better help and check if some configurations might need to be changed.

I would additionally need your permission to de- and re-activate Plugins and the Theme, and to change configurations on the site. This is also a reason the backup is really important. If you agree to this, please use the form fields I have enabled below to provide temporary access details (wp-admin and FTP).

I have set the next reply to private which means only you and I have access to it.

[Christian]

Hi Robert,

thanks for your cooperation.

The table is now working on your site. There was a javascript error that a field could not be loaded. That broke the whole display. I could resolve it for now by disabling all fields on the relationship table and enable them again.

We invest further to prevent this issue.

[Minesh]

Christian has fixed the issue on your install. Could you please check and confirm its working at your end as well.

[Mike]

Great, everything seems to be working now. Thanks for all the help.