I have spent many hours pouring over the posts related to DB MRL's.
(I have never posted, so I erred on the premise that more info is better, lol)
This is my objective:
I would like to insert a drop down list in my form to be populated by the names of users in the usergroup "Agents".
The tables available are as such:
#__usergroups: with columns "id" and "title"
#__user_usergroup_map:with columns "user_id" and "group_id"
#__users:with columns (among others) "id" and "name"
In short, I can get a list of usergroup ids with their names, a list of user ids associated with group ids (the lookup table) and a list of users ids with their names.
Note(s):
-Using chronoforms v4 & Joomla 2.5.9
-I was successful in populating a dropdown list (in the same form) with items from the k2_items table.
-All DBMRL's are loaded first before Show HTML, etc.
-Since I know the id of the usergroup (9) I didn't bother loading the #__usergroups table. I may have to adjust this in the future to allow the administrator using the form to choose which usergroup to pull a list from.
-I have a basic (but growing) understanding (not proficiency) of sql, and I am often confused as to what the inputs in the configuration panels in chronoforms refer to regarding the database queries.
-I will be calling the information collected in this form into a chronoconnectivity display table in another part of the site.
So far, I have tried the following:
1st DB MRL Configuration:
Table: #__users
Model ID: agentlist
Fields:name
Load Data: Y
Data Load Type: ALL
Enable Associations: Y
JOIN Type: INNER
JOIN Rule: groups.group_id = users.id AND groups.group_id='9'
Assoc. Models: users,groups
Group Model Data: Y
WHERE statement: groups.group_id = users.id AND groups.group_id='9'
Enable Data Displayer: N
2nd DB MRL Configuration:
Table: #__user_usergroup_map
Model ID: groups
Fields:user_id
Load Data: N
Data Load Type: ALL
Enable Associations: N
JOIN Type: INNER
JOIN Rule:
Assoc. Models:
Group Model Data: Y
WHERE statement: `groups`.`group_id`='9'
Enable Data Displayer: N
3rd DB MRL Configuration:
Table: #__users
Model ID: users
Fields:name
Load Data: No
Data Load Type: ALL
Enable Associations:No
JOIN Type:INNER
JOIN Rule:`groups`.`group_id` = `users`.`id`
Assoc. Models:groups,agentlist
Group Model Data:Y
WHERE statement:groups.group_id = users.id AND groups.group_id='9'
Enable Data Displayer:No
I have tried various orders, associations, etc (probably not the right combination, though!).
My debugger message(for this part) outputs:
Debug Data:
1. db_multi_record_loader:
1.SELECT `name`, `groups`.`user_id` AS `groups.user_id`, `users`.`name` AS `users.name` FROM `hoek1_users` AS `agentlist` INNER JOIN `hoek1_users` AS `users` ON `groups`.`group_id` = `users`.`id` ON groups.group_id = users.id AND groups.group_id='9' INNER JOIN `hoek1_user_usergroup_map` AS `groups` ON `groups`.`group_id`='9' WHERE groups.group_id = users.id AND groups.group_id='9'
I think may either be duplicating queries, associating incorrect tables, or making this way too difficult, in general!
(I have never posted, so I erred on the premise that more info is better, lol)
This is my objective:
I would like to insert a drop down list in my form to be populated by the names of users in the usergroup "Agents".
The tables available are as such:
#__usergroups: with columns "id" and "title"
#__user_usergroup_map:with columns "user_id" and "group_id"
#__users:with columns (among others) "id" and "name"
In short, I can get a list of usergroup ids with their names, a list of user ids associated with group ids (the lookup table) and a list of users ids with their names.
Note(s):
-Using chronoforms v4 & Joomla 2.5.9
-I was successful in populating a dropdown list (in the same form) with items from the k2_items table.
-All DBMRL's are loaded first before Show HTML, etc.
-Since I know the id of the usergroup (9) I didn't bother loading the #__usergroups table. I may have to adjust this in the future to allow the administrator using the form to choose which usergroup to pull a list from.
-I have a basic (but growing) understanding (not proficiency) of sql, and I am often confused as to what the inputs in the configuration panels in chronoforms refer to regarding the database queries.
-I will be calling the information collected in this form into a chronoconnectivity display table in another part of the site.
So far, I have tried the following:
1st DB MRL Configuration:
Table: #__users
Model ID: agentlist
Fields:name
Load Data: Y
Data Load Type: ALL
Enable Associations: Y
JOIN Type: INNER
JOIN Rule: groups.group_id = users.id AND groups.group_id='9'
Assoc. Models: users,groups
Group Model Data: Y
WHERE statement: groups.group_id = users.id AND groups.group_id='9'
Enable Data Displayer: N
2nd DB MRL Configuration:
Table: #__user_usergroup_map
Model ID: groups
Fields:user_id
Load Data: N
Data Load Type: ALL
Enable Associations: N
JOIN Type: INNER
JOIN Rule:
Assoc. Models:
Group Model Data: Y
WHERE statement: `groups`.`group_id`='9'
Enable Data Displayer: N
3rd DB MRL Configuration:
Table: #__users
Model ID: users
Fields:name
Load Data: No
Data Load Type: ALL
Enable Associations:No
JOIN Type:INNER
JOIN Rule:`groups`.`group_id` = `users`.`id`
Assoc. Models:groups,agentlist
Group Model Data:Y
WHERE statement:groups.group_id = users.id AND groups.group_id='9'
Enable Data Displayer:No
I have tried various orders, associations, etc (probably not the right combination, though!).
My debugger message(for this part) outputs:
Debug Data:
1. db_multi_record_loader:
1.SELECT `name`, `groups`.`user_id` AS `groups.user_id`, `users`.`name` AS `users.name` FROM `hoek1_users` AS `agentlist` INNER JOIN `hoek1_users` AS `users` ON `groups`.`group_id` = `users`.`id` ON groups.group_id = users.id AND groups.group_id='9' INNER JOIN `hoek1_user_usergroup_map` AS `groups` ON `groups`.`group_id`='9' WHERE groups.group_id = users.id AND groups.group_id='9'
I think may either be duplicating queries, associating incorrect tables, or making this way too difficult, in general!