Discussion Forum

philp

Member

Registered: April 5, 2012

Posts: 25

Many-to-many fields

I'm curious to know how Many-to-many fields should be configured and used. Any info on this would be really useful — can't find anything in the current documentation.

timbuckingham

Administrator

From: Baltimore, MD

Registered: April 2, 2012

Posts: 978

Re: Many-to-many fields

To setup a many-to-many field you'll want to create a table that relates one set of content to the other.  So let's say we have a btx_news table and a btx_categories table and we want to relate a news article to multiple categories.

We'll create a btx_news_categories table to tie them together.  Let's say it looks something like:

CREATE TABLE `btx_news_categories` (
  `news` int(11) unsigned NOT NULL,
  `category` int(11) unsigned NOT NULL
);

If we wanted to allow priority / sorting to the many-to-many relationship we could also add a "position" column that is also int(11).

Once this intermediary table is setup, edit the News module's form and click the "Many To Many" button in the header of the fields table.  It should add a blank row to table with Many To Many as the type.  You'll want to throw in a Title there, in this case "Categories" would be appropriate.

Next, click the Edit icon to pull up the options for the Many To Many field.  Choose "btx_news_categories" for the "Connecting Table" and the My ID and Other ID fields should now be filled with fields from that table.  In this instance we would choose "news" as My ID (since it relates to the table the form currently relates to) and "category" for Other ID.  For "Other Table" we would choose "btx_categories".  That should now populate "Other Descriptor" and "Sort By" with fields from btx_categories.  We'd choose whatever the title field in btx_categories was for "Other Descriptor" and probably use the same field for "Sort By" so it's easy to choose categories.

After that, save the field options and the form and you should now have a selector widget in your form that lets you choose categories to relate to the news item.  It'll populate the btx_news_categories table with the news item's ID and the category's ID for you to query against on the front end of your site.

Let me know if you have any questions!

philp

Member

Registered: April 5, 2012

Posts: 25

Re: Many-to-many fields

Thanks for the info — have had a play around with this and managed to get it working nicely.