Left / Inner Join with multiple #636
-
|
For a project i need to load all items that are related to a group. The Initial view / get is the getgroup get. 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? |
Beta Was this translation helpful? Give feedback.
Replies: 3 comments
-
|
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. |
Beta Was this translation helpful? Give feedback.
-
|
Is there any detailed workaround/tutorial for this? With my limited JCB knowledge I don't really understand it. |
Beta Was this translation helpful? Give feedback.
-
|
Anyone who can help? |
Beta Was this translation helpful? Give feedback.
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: