Left / Inner Join with multiple

[marcorensch]

For a project i need to load all items that are related to a group. The Initial view / get is the getgroup get.
Here i have a field called users where all related users are linked to.
The backend field itself is a default Multiple selection that stores its data as {"0":"3","1":"2"}, so now before i can join those values i need them to be decoded.

I need a "json_decode($data->users,1)" that the inner join is working - when i am not doing this the Join is not working because the function JCB creates for joining awaits an array (which is correct) but i cannot see how i am able to bring my data into the correct format...

How did you guys solved such an issue? When you have to join Multiple items based on a MultiSelect?

[Llewellynvdm]

The best way I have with consistent correct linking via sql been able to resolve this is to give that field that stores the json, its own table. So I create a table via the component MySQL tab, and if the component is already sipped to clients I also use the MySQL update area to also create this table. Then in my front-end query I link the data directly via sql.

The sql could look like this:

CREATE TABLE IF NOT EXISTS `#__[[[component]]]_relationships` (
	`area` TINYINT(3) NOT NULL,
	`id` INT(11) NOT NULL COMMENT 'Area item ID',
	`member` INT(11) NOT NULL,
	`type` INT(11) NOT NULL,
	`active` INT(11) NOT NULL DEFAULT 0,
	PRIMARY KEY (`area`, `id`, `member`, `type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 DEFAULT COLLATE=utf8mb4_unicode_ci;;

I do it this way since not all shared hosting environments have the sql method to decode the json.

JCB has very easy way to move a fields values to another table and to retrieve it again, look at this tutorial.

[jsr4id]

Is there any detailed workaround/tutorial for this? With my limited JCB knowledge I don't really understand it.