Welcome, Guest
Username: Password: Remember me
  • Page:
  • 1

TOPIC:

Pivot table query 07 Feb 2017 04:26 #14987

  • vlemos
  • vlemos's Avatar Topic Author
  • Offline
  • Elite Member
  • Elite Member
  • Posts: 295
  • Thank you received: 41
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

Please Log in or Create an account to join the conversation.

Pivot table query 07 Feb 2017 14:06 #14990

  • admin
  • admin's Avatar
  • Offline
  • Administrator
  • Administrator
  • Chef
  • Posts: 3711
  • Thank you received: 986
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
The following user(s) said Thank You: vlemos

Please Log in or Create an account to join the conversation.

Last edit: by admin.

Pivot table query 07 Feb 2017 14:37 #14991

  • vlemos
  • vlemos's Avatar Topic Author
  • Offline
  • Elite Member
  • Elite Member
  • Posts: 295
  • Thank you received: 41
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.
:cheer:

Please Log in or Create an account to join the conversation.

Pivot table query 07 Feb 2017 14:44 #14992

  • admin
  • admin's Avatar
  • Offline
  • Administrator
  • Administrator
  • Chef
  • Posts: 3711
  • Thank you received: 986
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 following user(s) said Thank You: vlemos

Please Log in or Create an account to join the conversation.

Pivot table query 07 Feb 2017 14:57 #14993

  • vlemos
  • vlemos's Avatar Topic Author
  • Offline
  • Elite Member
  • Elite Member
  • Posts: 295
  • Thank you received: 41
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'
	));

Please Log in or Create an account to join the conversation.

Pivot table query 07 Feb 2017 15:17 #14994

  • vlemos
  • vlemos's Avatar Topic Author
  • Offline
  • Elite Member
  • Elite Member
  • Posts: 295
  • Thank you received: 41
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?

Please Log in or Create an account to join the conversation.

Pivot table query 07 Feb 2017 15:52 #14995

  • admin
  • admin's Avatar
  • Offline
  • Administrator
  • Administrator
  • Chef
  • Posts: 3711
  • Thank you received: 986
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 following user(s) said Thank You: vlemos

Please Log in or Create an account to join the conversation.

Pivot table query 07 Feb 2017 17:18 #14996

  • vlemos
  • vlemos's Avatar Topic Author
  • Offline
  • Elite Member
  • Elite Member
  • Posts: 295
  • Thank you received: 41
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! B)
The following user(s) said Thank You: admin

Please Log in or Create an account to join the conversation.

Pivot table query 07 Feb 2017 17:37 #14997

  • vlemos
  • vlemos's Avatar Topic Author
  • Offline
  • Elite Member
  • Elite Member
  • Posts: 295
  • Thank you received: 41
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"/>

Please Log in or Create an account to join the conversation.

Pivot table query 07 Feb 2017 18:30 #15000

  • admin
  • admin's Avatar
  • Offline
  • Administrator
  • Administrator
  • Chef
  • Posts: 3711
  • Thank you received: 986
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 following user(s) said Thank You: vlemos

Please Log in or Create an account to join the conversation.

Pivot table query 07 Feb 2017 18:56 #15001

  • vlemos
  • vlemos's Avatar Topic Author
  • Offline
  • Elite Member
  • Elite Member
  • Posts: 295
  • Thank you received: 41
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?

Please Log in or Create an account to join the conversation.

  • Page:
  • 1
Time to create page: 0.073 seconds
Great UX great performance and definitely a time saver. It only took me 10 minutes of playing around before purchasing a subscription and right now I just want to throw more money at you! I just started a new project and after just 4h I can already tell it probably saved me several days of work. I'm building a wholesale ordering system with sales rep management. I have not looked at the code yet but so far: impressive.
flip (Forum)

Get Started