Discussion Forum

mbriney

Member

From: Arlington, VA

Registered: December 8, 2016

Posts: 2

SQL join in Many to Many M2M field

What is the easiest way to perform a SQL JOIN on a M2M field in BigTree?  It appears that the storage syntax changes depending on if someone re-ordered the item in the admin field.

Here is an example of what we are working with:

timbuckingham

Administrator

From: Baltimore, MD

Registered: April 2, 2012

Posts: 974

Re: SQL join in Many to Many M2M field

That appears to be a One to Many field. For a Many to Many field you need a intermediary table that stores the relationships.

The storage syntax for One to Many fields did change depending on whether it was re-ordered or not. That was fixed in the most recent release:
https://github.com/bigtreecms/BigTree-C … ac19dcabfb

As far as how to do a join, if you're talking about using it to display data from two different tables (e.g. a person's name and having their department from another table brought in) I would create a custom field type and dupe the default one to many or many to many draw / process files. Then update the draw file to pull in the data from the join'd query rather than the simple single table query.