Welcome, Guest

TOPIC: Pivot table query

Pivot table query 07 Feb 2017 04:26 #14987

vlemos Online Elite Member Posts: 295 Thank you received: 41 Karma: 21	Hello Admin, please help? I have a n:m relationship. In the form, when <?php echo $field->input; ?> is executed, the selected records are pulled from the pivot table. I know what the query should look like but I am trying to find the code which builds the query to fetch these records. Thanks vlemos
	The administrator has disabled public write access.

Pivot table query 07 Feb 2017 14:06 #14990

admin
Offline
Administrator
Chef
Posts: 3711
Thank you received: 987
Karma: 140

This function automatically treat the field config to browse datas :

XxxxHelperForm::getItems()

But you can create a function called "getItems()" in the field type class.
It depends what you want to do. You can
- fork the 'relation' field type and override the getItems() function
- create a derived field type inherited from 'relation' (You will encounter fork problem if you create a new field type wich doesn't exist in the generated component. It is still a limitation at the moment)
- invent a new parameter in the XML form field declaration to change the behavior of your field. (Taking source from somewhere else for instance)

Another way is also to play with :

prepareModel()

It is another cool way because you are in touch directly with the desired model. You can play with the ORM

Cook is very handly and confortable, isn't it ?

Coding is now a piece of cake

Last Edit: 07 Feb 2017 14:23 by admin.

The administrator has disabled public write access.

The following user(s) said Thank You: vlemos

Pivot table query 07 Feb 2017 14:37 #14991

vlemos
Online
Elite Member
Posts: 295
Thank you received: 41
Karma: 21

Yeah, but I was working recently with orm and found an issue that I am still wondering about. I wanted to get a list of users based on Joomla group-title/name. I only need "id" and "user name" but to get the query correctly I must also return:

       _user_usergroup_map_id_.group_id
       _usergroup_id_.title

In straight sql I can return one column while linking to a hundred tables. But it is highly possible that I don't yet know enough about cook's orm interface.

The administrator has disabled public write access.

Pivot table query 07 Feb 2017 14:44 #14992

admin Offline Administrator Chef Posts: 3711 Thank you received: 987 Karma: 140	You need to declare the 'user_usergroup_map_id' relation in the model constructor, (see how are build the others relations) and also 'usergroup_id' relation It is true that Cook should instance them by default even if they are not used.
	Coding is now a piece of cake The administrator has disabled public write access. The following user(s) said Thank You: vlemos

Pivot table query 07 Feb 2017 14:57 #14993

vlemos
Online
Elite Member
Posts: 295
Thank you received: 41
Karma: 21

Yes did that. . . .

		$this->hasOne('user_usergroup_map_id', // name
			'.user_usergroup_map', // foreignModelClass
			'user_id', // localKey
			'user_id' // foreignKey
		);

		$this->hasOne('usergroup_id', // name
			'.usergroups', // foreignModelClass
			'user_usergroup_map_id.group_id', // localKey
			'id' // foreignKey
		);

But then had to do this as well to link it up correctly.

	$this->orm->select(array(
		'user_id',
		'user_id.name',
		'user_usergroup_map_id.group_id',
		'usergroup_id.title'
	));

The administrator has disabled public write access.

Pivot table query 07 Feb 2017 15:17 #14994

vlemos Online Elite Member Posts: 295 Thank you received: 41 Karma: 21	From what I can tell, linking is a function of the field selection. Therefore you must return specific fields if the query is to be built correctly. Not true?
	The administrator has disabled public write access.

Pivot table query 07 Feb 2017 15:52 #14995

admin Offline Administrator Chef Posts: 3711 Thank you received: 987 Karma: 140	Use the third models for third tables (users / usergroups) In case, create new models Then set this model in the foreignModelClass parameter for the config. I know it is not the best yet, but it will be improved in future to link to tables outside your component. Hope it helps, otherwise, I don't understand your problem. What says SQL ?
	Coding is now a piece of cake The administrator has disabled public write access. The following user(s) said Thank You: vlemos

Pivot table query 07 Feb 2017 17:18 #14996

vlemos Online Elite Member Posts: 295 Thank you received: 41 Karma: 21	Third tables is too much work. I could use straight SQL but this works for me for now, thanks. J-Cook is still the best builder anywhere!
	The administrator has disabled public write access. The following user(s) said Thank You: admin

Pivot table query 07 Feb 2017 17:37 #14997

vlemos Online Elite Member Posts: 295 Thank you received: 41 Karma: 21	I have a n:m relationship populating a multi-select box. Is there a way to pass a filter to the select query? I want to return only users from a specific group but currently the builder returns all the records and displays them in the dropdown. <field name="users" alias="id" label="TEST_FIELD_NAME" filter="array" type="relation" multiple="true" labelKey="_user_id_name"/>
	The administrator has disabled public write access.

Pivot table query 07 Feb 2017 18:30 #15000

admin Offline Administrator Chef Posts: 3711 Thank you received: 987 Karma: 140	Yes. I told you before : prepareModel(&$model) Put this function on your field class and play with the model using ORM or states vars
	Coding is now a piece of cake The administrator has disabled public write access. The following user(s) said Thank You: vlemos

Pivot table query 07 Feb 2017 18:56 #15001

vlemos
Online
Elite Member
Posts: 295
Thank you received: 41
Karma: 21

One other thing I wanted to ask about is in cases where Ajax is selected on a field like created_by/modified_by; should the builder not remove these references from view.html.php?

	$model_created_by = CkJModel::getInstance('ThirdUsers', 'TestsModel');
	$model_created_by->addGroupOrder("a.username");
	$lists['fk']['created_by'] = $model_created_by->getItems();

Would this not add useless chatter and delays to the building of a given view?

The administrator has disabled public write access.