Bug with store as JSON for custom (different component) #275

New Issue

Closed

opened 2018-05-02 20:50:39 +00:00 by cpaschen · 11 comments

cpaschen commented 2018-05-02 20:50:39 +00:00 (Migrated from github.com)

Copy Link

Steps to reproduce the issue

Create a custom field (extends list).
Set

• table = existing external table [#__kr_books_system_sp_category]
• value_field = existing field in the external table [catregory_name]
• key_field = id
• Data Base - Store Method - JSON

Add the field to the admin view, display in list

Expected result

Show the related data.

Actual result

Table 'krcbdev.#_kr_books_serviceproviders#__kr_books_system_sp_category' doesn't exist

System information (as much as possible)

• OS Name & Version: WAMP
• MySql Version: 5.5.16
• Apache Version: 2.4.27
• PHP Version: 7.1.9
• Joomla Version: 3.8.7
• JCB Version: Staging from 2018-05-01
• Browser: Chrome

Additional comments

The problem appears that in the helper file, in the getVar function, the 2nd if statement (if (empty($table)), if the $table variable has a table name (in this case '#__kr_books_system_sp_category') it is adding the $main as a prefix to the $table in this line:

$query->from($db->quoteName('#__'.$main.'_'.$table));

I'm not sure why it would need to add the $main and $table together, and why it needs to add the "#__' prefix (because it is already in the table value for the field.

NOTE: The custom lookup table (#__kr_books_system_sp_category) is not from the current component but is puling categories from a different component (created with JCB also).

ALSO ... If I set this to store as 'default' then it work fine, except that the list view only shows the ID numbers of the selected categories.

Is this a bug or is there some sort of different configuration needed when using the JSON storage method.

### Steps to reproduce the issue Create a custom field (extends list). Set - table = existing external table [#__kr_books_system_sp_category] - value_field = existing field in the external table [catregory_name] - key_field = id - Data Base - Store Method - JSON Add the field to the admin view, display in list ### Expected result Show the related data. ### Actual result Table 'krcbdev.#__kr_books_serviceproviders_#__kr_books_system_sp_category' doesn't exist ### System information (as much as possible) - OS Name & Version: WAMP - MySql Version: 5.5.16 - Apache Version: 2.4.27 - PHP Version: 7.1.9 - Joomla Version: 3.8.7 - JCB Version: Staging from 2018-05-01 - Browser: Chrome ### Additional comments The problem appears that in the helper file, in the getVar function, the 2nd if statement (if (empty($table)), if the $table variable has a table name (in this case '#__kr_books_system_sp_category') it is adding the $main as a prefix to the $table in this line: `$query->from($db->quoteName('#__'.$main.'_'.$table));` I'm not sure why it would need to add the $main and $table together, and why it needs to add the "#__' prefix (because it is already in the table value for the field. NOTE: The custom lookup table (#__kr_books_system_sp_category) is not from the current component but is puling categories from a different component (created with JCB also). ALSO ... If I set this to store as 'default' then it work fine, except that the list view only shows the ID numbers of the selected categories. Is this a bug or is there some sort of different configuration needed when using the JSON storage method.

cpaschen commented 2018-05-02 21:18:47 +00:00 (Migrated from github.com)

Copy Link

Sorry, that last section wasn't clear.

When I set the Data Base Store Method to 'default' then it works fine (although the list view only shows the ID numbers of the selected categories).

I did try changing the above indicated query line to this:

$query->from($db->quoteName($table));

But then I get an error where query is trying to use one of the values of the JSON data a a field name.

So either I'm completely lost with how this code is supposed to work (and how I should be properly configuring the custom field to work with JSON storage) or there is a bug there. (but I'm guessing that it is the first issue - not the code).

Sorry, that last section wasn't clear. When I set the Data Base Store Method to 'default' then it works fine (although the list view only shows the ID numbers of the selected categories). I did try changing the above indicated query line to this: $query->from($db->quoteName($table)); But then I get an error where query is trying to use one of the values of the JSON data a a field name. So either I'm completely lost with how this code is supposed to work (and how I should be properly configuring the custom field to work with JSON storage) or there is a bug there. (but I'm guessing that it is the first issue - not the code).

Llewellyn commented 2018-05-02 21:28:48 +00:00

Copy Link

Classic edge case issue. Soon as you leave the beaten path of convention... things do not work that well, and custom scripting have to kick in.

I have seen my self constantly improving JCB to handle more and more dynamic edge case implementations. But alas I still see more come up as we hammer out more advance use cases.

Okay having said all that, lets look at this. You custom field is getting values from another component, this is the issue. the getVar method can get values from another component but needs help to know that.

JCB has conventions that follow the Joomla conventions of table naming. You always have the #__ in php all over to insure that the correct prefix is used. Then the second word in this case $main is the component name, and then lastly the last part is the $table being the view name. So looking at the model getItems method you will see we are trying to convert your json string to a normal string, but at the same time get the names instead of the ids to show in the list view. We are using the jsonToString method. Then in this jsonToString method we are using the getVar method to try and get these names. I mean you do realize that we are flying in the dark here, and the possibilities are innumerable. So I have assumed that we are trying to do so inside the same component, as this is normally how you should do this, seeing that reaching for info in another component should naturally mean custom coding.

Now I did say that the getVar method is able to get values from another component, but needs some help. Just go look at its notes...

Let me take some time and see if I can push the boundary to also be able to detect that you are actually going outside the current component.

But you can start by giving me a screen shot of the custom field settings and also the PHP for the selection. We take it from there.

Classic edge case issue. Soon as you leave the beaten path of convention... things do not work that well, and custom scripting have to kick in. I have seen my self constantly improving JCB to handle more and more dynamic edge case implementations. But alas I still see more come up as we hammer out more advance use cases. Okay having said all that, lets look at this. You custom field is getting values from another component, this is the issue. the `getVar` method can get values from another component but needs help to know that. JCB has conventions that follow the Joomla conventions of table naming. You always have the `#__` in php all over to insure that the correct prefix is used. Then the second word in this case `$main` is the component name, and then lastly the last part is the `$table` being the view name. So looking at the model getItems method you will see we are trying to convert your json string to a normal string, but at the same time get the names instead of the ids to show in the list view. We are using the `jsonToString` method. Then in this `jsonToString` method we are using the `getVar` method to try and get these names. I mean you do realize that we are flying in the dark here, and the possibilities are innumerable. So I have assumed that we are trying to do so inside the same component, as this is normally how you should do this, seeing that reaching for info in another component should naturally mean custom coding. Now I did say that the `getVar` method is able to get values from another component, but needs some help. Just go [look at its notes...](https://github.com/vdm-io/Joomla-Component-Builder/blob/staging/admin/helpers/componentbuilder.php#L4041) Let me take some time and see if I can push the boundary to also be able to detect that you are actually going outside the current component. But you can start by giving me a screen shot of the custom field settings and also the PHP for the selection. We take it from there.

Llewellyn commented 2018-05-02 21:35:04 +00:00

Copy Link

Can you also give me the actual code of the getItems and getListQuery method in the model. This will help to make sure I see all the issues, as it seems the database query is also broken.

Can you also give me the actual code of the `getItems` and `getListQuery` method in the model. This will help to make sure I see all the issues, as it seems the database query is also broken.

Llewellyn commented 2018-05-02 21:59:34 +00:00

Copy Link

Okay I think it should work now... please check out the latest push to staging branch.

What I have done is to improve the jsonToString method to detect that it is working with another component, and so tell the getVar method to also look outside the current scope (component). Really hope this does the trick.

Let me know.

Okay I think it should work now... please check out the latest push to staging branch. What I have done is to improve the `jsonToString` method to detect that it is working with another component, and so tell the `getVar` method to also look outside the current scope (component). Really hope this does the trick. Let me know.

cpaschen commented 2018-05-03 00:02:35 +00:00 (Migrated from github.com)

Copy Link

Well, got past the initial error. Now I'm getting:

Unknown column 'Book designer' in 'field list'

'Book designer' is one of the categories in the #__kr_books_system_sp_category table (and selected in a record that should be displayed by the list).

Here's the various code you requested:

1, a screen shot of the custom field settings

2. the PHP for the selection
   -not sure exactly which code you are looking for for this

3. the actual code of the getItems method

/**
	 * Method to get an array of data items.
	 *
	 * @return  mixed  An array of data items on success, false on failure.
	 */
	public function getItems()
	{ 
		// check in items
		$this->checkInNow();

		// load parent items
		$items = parent::getItems();

		// set values to display correctly.
		if (Kr_books_serviceprovidersHelper::checkArray($items))
		{
			foreach ($items as $nr => &$item)
			{
				// convert category
				$item->category = Kr_books_serviceprovidersHelper::jsonToString($item->category, ', ', '#__kr_books_system_sp_category', 'id', 'category_name');
			}
		}  
        
		// return items
		return $items;
	}

4. the actual code of the getListQuery method

	/**
	 * Method to build an SQL query to load the list data.
	 *
	 * @return	string	An SQL query
	 */
	protected function getListQuery()
	{
		// Get the user object.
		$user = JFactory::getUser();
		// Create a new query object.
		$db = JFactory::getDBO();
		$query = $db->getQuery(true);

		// Select some fields
		$query->select('a.*');

		// From the kr_books_serviceproviders_item table
		$query->from($db->quoteName('#__kr_books_serviceproviders_provider', 'a'));

		// Filter by published state
		$published = $this->getState('filter.published');
		if (is_numeric($published))
		{
			$query->where('a.published = ' . (int) $published);
		}
		elseif ($published === '')
		{
			$query->where('(a.published = 0 OR a.published = 1)');
		}

		// Join over the asset groups.
		$query->select('ag.title AS access_level');
		$query->join('LEFT', '#__viewlevels AS ag ON ag.id = a.access');
		// Filter by access level.
		if ($access = $this->getState('filter.access'))
		{
			$query->where('a.access = ' . (int) $access);
		}
		// Implement View Level Access
		if (!$user->authorise('core.options', 'com_kr_books_serviceproviders'))
		{
			$groups = implode(',', $user->getAuthorisedViewLevels());
			$query->where('a.access IN (' . $groups . ')');
		}
		// Filter by search.
		$search = $this->getState('filter.search');
		if (!empty($search))
		{
			if (stripos($search, 'id:') === 0)
			{
				$query->where('a.id = ' . (int) substr($search, 3));
			}
			else
			{
				$search = $db->quote('%' . $db->escape($search) . '%');
				$query->where('(a.business_name LIKE '.$search.' OR a.business_email LIKE '.$search.')');
			}
		}


		// Add the list ordering clause.
		$orderCol = $this->state->get('list.ordering', 'a.id');
		$orderDirn = $this->state->get('list.direction', 'asc');	
		if ($orderCol != '')
		{
			$query->order($db->escape($orderCol . ' ' . $orderDirn));
		}

		return $query;
	}

Also ... if this is beyond functionality within the JCB 'system' and just needs to be done with custom code, just tell me and I'll go hand-code it.

Well, got past the initial error. Now I'm getting: Unknown column 'Book designer' in 'field list' 'Book designer' is one of the categories in the #__kr_books_system_sp_category table (and selected in a record that should be displayed by the list). Here's the various code you requested: 1, a screen shot of the custom field settings  2. the PHP for the selection -not sure exactly which code you are looking for for this 3. the actual code of the getItems method ``` /** * Method to get an array of data items. * * @return mixed An array of data items on success, false on failure. */ public function getItems() { // check in items $this->checkInNow(); // load parent items $items = parent::getItems(); // set values to display correctly. if (Kr_books_serviceprovidersHelper::checkArray($items)) { foreach ($items as $nr => &$item) { // convert category $item->category = Kr_books_serviceprovidersHelper::jsonToString($item->category, ', ', '#__kr_books_system_sp_category', 'id', 'category_name'); } } // return items return $items; } ``` 4. the actual code of the getListQuery method ``` /** * Method to build an SQL query to load the list data. * * @return string An SQL query */ protected function getListQuery() { // Get the user object. $user = JFactory::getUser(); // Create a new query object. $db = JFactory::getDBO(); $query = $db->getQuery(true); // Select some fields $query->select('a.*'); // From the kr_books_serviceproviders_item table $query->from($db->quoteName('#__kr_books_serviceproviders_provider', 'a')); // Filter by published state $published = $this->getState('filter.published'); if (is_numeric($published)) { $query->where('a.published = ' . (int) $published); } elseif ($published === '') { $query->where('(a.published = 0 OR a.published = 1)'); } // Join over the asset groups. $query->select('ag.title AS access_level'); $query->join('LEFT', '#__viewlevels AS ag ON ag.id = a.access'); // Filter by access level. if ($access = $this->getState('filter.access')) { $query->where('a.access = ' . (int) $access); } // Implement View Level Access if (!$user->authorise('core.options', 'com_kr_books_serviceproviders')) { $groups = implode(',', $user->getAuthorisedViewLevels()); $query->where('a.access IN (' . $groups . ')'); } // Filter by search. $search = $this->getState('filter.search'); if (!empty($search)) { if (stripos($search, 'id:') === 0) { $query->where('a.id = ' . (int) substr($search, 3)); } else { $search = $db->quote('%' . $db->escape($search) . '%'); $query->where('(a.business_name LIKE '.$search.' OR a.business_email LIKE '.$search.')'); } } // Add the list ordering clause. $orderCol = $this->state->get('list.ordering', 'a.id'); $orderDirn = $this->state->get('list.direction', 'asc'); if ($orderCol != '') { $query->order($db->escape($orderCol . ' ' . $orderDirn)); } return $query; } ``` Also ... if this is beyond functionality within the JCB 'system' and just needs to be done with custom code, just tell me and I'll go hand-code it.

Llewellyn commented 2018-05-03 14:27:00 +00:00

Copy Link

We can start by giving a side note... in the creation of a field, when the property is not mandatory and you are not adding value to it, best remove it.

I do not see the column Book designer in any of this code... where is it being called? this could be another issue unrelated to this one.

We can start by giving a side note... in the creation of a field, when the property is not mandatory and you are not adding value to it, best remove it. I do not see the column `Book designer` in any of this code... where is it being called? this could be another issue unrelated to this one.

Llewellyn commented 2018-05-03 14:28:59 +00:00

Copy Link

Do you have other custom field targeting that column? try to just remove it for the moment to isolate this issue, so to deal with one at a time.

Do you have other custom field targeting that column? try to just remove it for the moment to isolate this issue, so to deal with one at a time.

cpaschen commented 2018-05-03 16:16:24 +00:00 (Migrated from github.com)

Copy Link

That is the strange thing, 'Book designer' is a value in the #__kr_books_system_sp_category table (that we are looking up. I did a check through all the code and 'Book designer' doesn't appear anywhere. And it's not in any other field in this component (in the code) or the other component (where the source table is located). Also, there are no other Custom fields in this component.
I'm starting to think that I should just either manually override this code or possibly even just figure out a way to combined the two components into one. Although part of me also wants to figure out why this isn't working. I have an instance where I know I will need to do this with a 3rd party component data (specifically Community Builder) in our next round so I will eventually need to figure this out. However, manually overriding might be the best solution.

You mentioned 'when the property is not mandatory and you are not adding value to it, best remove it.'
I'm wondering if the 'button' field is set to false if the 'component', 'view' and 'views' entries are actually mandatory?

That is the strange thing, 'Book designer' is a value in the #__kr_books_system_sp_category table (that we are looking up. I did a check through all the code and 'Book designer' doesn't appear anywhere. And it's not in any other field in this component (in the code) or the other component (where the source table is located). Also, there are no other Custom fields in this component. I'm starting to think that I should just either manually override this code or possibly even just figure out a way to combined the two components into one. Although part of me also wants to figure out why this isn't working. I have an instance where I know I will need to do this with a 3rd party component data (specifically Community Builder) in our next round so I will eventually need to figure this out. However, manually overriding might be the best solution. You mentioned 'when the property is not mandatory and you are not adding value to it, best remove it.' I'm wondering if the 'button' field is set to false if the 'component', 'view' and 'views' entries are actually mandatory?

cpaschen commented 2018-05-03 16:26:49 +00:00 (Migrated from github.com)

Copy Link

FYI ... it appears that those fields ('component', 'view' and 'views') are NOT mandatory IF you have the 'button' field set to false.
Although deleting them (and other non-assigned properties) doesn't resolve the issue with a value ('Book designer') being seen as a column in the query.
ALSO ... the issue must be in the JSON code because the system seems to work fine with the 'Default' table storage method is used. The only difference is that the list view only displays the IDs of the items chosen. (The selector in the edit view displays the values as it should).

FYI ... it appears that those fields ('component', 'view' and 'views') are NOT mandatory IF you have the 'button' field set to false. Although deleting them (and other non-assigned properties) doesn't resolve the issue with a value ('Book designer') being seen as a column in the query. ALSO ... the issue must be in the JSON code because the system seems to work fine with the 'Default' table storage method is used. The only difference is that the list view only displays the IDs of the items chosen. (The selector in the edit view displays the values as it should).

Llewellyn commented 2018-05-03 16:39:24 +00:00

Copy Link

Those field are actually mandatory... as you can see we need it at times to build the conversion to string.

I think I have to update those to show as mandatory...

The Json ecoding option is the only one that trigger this conversion at this time. I can try and move it into the default as well, but only once we get it to work here as expected.

I think there is intense complexity here, that I will only be able to debug this further with teamviewer or something, but I do not have the time now.

To me, in my test this works better then ever before, and I would think that the issue remains in that table setup. The fact that it has errors can be so many things, especially since it is not directly related to this query.

So so go head and do the custom code, it is only in this line that all the work should be done:

// convert category
$item->category = Kr_books_serviceprovidersHelper::jsonToString($item->category, ', ', '#__kr_books_system_sp_category', 'id', 'category_name');

And as you can see it is completly isolated from all other code really, so these error must be something else. What happens if you change this code to:

$item->category = 'just testing';

Well this is what you should look at! All the other things are distractions(other issues), and have no bearing on this issue at all.

Those field are actually mandatory... as you can see we need it at times to build the conversion to string. I think I have to update those to show as mandatory... The Json ecoding option is the only one that trigger this conversion at this time. I can try and move it into the default as well, but only once we get it to work here as expected. I think there is intense complexity here, that I will only be able to debug this further with teamviewer or something, but I do not have the time now. To me, in my test this works better then ever before, and I would think that the issue remains in that table setup. The fact that it has errors can be so many things, especially since it is not directly related to this query. So so go head and do the custom code, it is only in this line that all the work should be done: ``` // convert category $item->category = Kr_books_serviceprovidersHelper::jsonToString($item->category, ', ', '#__kr_books_system_sp_category', 'id', 'category_name'); ``` And as you can see it is completly isolated from all other code really, so these error must be something else. What happens if you change this code to: ``` $item->category = 'just testing'; ``` Well this is what you should look at! All the other things are distractions(other issues), and have no bearing on this issue at all.

Llewellyn commented 2018-05-05 00:35:36 +00:00

Copy Link

The fix to this issue was made, and is now part of the master branch. So I would like to close this issue, let me know if you have more news on this.

The fix to this issue was made, and is now part of the master branch. So I would like to close this issue, let me know if you have more news on this.

Sign in to join this conversation.

No Branch/Tag Specified

Branches Tags

5.x

4.x

3.x

master

staging

v5.0.1-alpha2

v4.0.1-alpha2

v3.2.2-alpha2

v3.2.2-alpha1

v4.0.1-alpha1

v5.0.1-alpha1

v5.0.0

v4.0.0

v3.2.1

v3.2.0

v3.1.28

v3.1.27

v3.1.24

v3.1.19

v3.1.17

v3.1.15

v3.1.12

v3.1.11

v3.1.10

v3.1.9

v3.1.8

v3.1.7

v3.1.6

v3.1.5

v3.1.4

v3.1.3

v3.1.2

v2.13.1

v2.13.0

v2.12.16

v2.12.15

v2.12.14

v2.12.10

v2.12.9

v2.12.8

v2.12.7

v2.12.6

v2.12.4

v2.12.3

v2.12.2

v2.11.7

v2.11.4

v2.11.3

v2.11.2

v2.11.0

v2.10.13

v2.10.11

v2.10.9

v2.10.5

v2.10.1

v2.10.0

v2.9.21

v2.9.18

v2.9.14

v2.9.11

v2.9.10

v2.9.8

v2.9.7

v2.9.1

v2.9.0

v2.8.5

v2.8.4

v2.8.2

v2.8.0

v2.7.10

v2.7.7

v2.7.6

v2.7.5

v2.7.4

v2.7.1

v2.7.0

v2.6.17

v2.6.16

v2.6.15

v2.6.14

v2.6.13

v2.6.12

v2.6.11

v2.6.10

v2.6.9

v2.6.8

v2.6.7

v2.6.6

v2.6.5

v2.6.3

v2.6.2

v2.6.1

v2.6.0

v2.5.8

v2.5.6

v2.5.5

v2.5.4

v2.5.1

v2.5.0

v2.4.10

v2.4.9

v2.4.8

v2.4.7

v2.4.6

v2.4.5

v2.4.4

v2.4.3

v2.4.2

v2.4.1

v2.4.0

v2.3.10

v2.3.9

v2.3.8

v2.3.7

v2.3.6

v2.3.5

v2.3.4

v2.3.2

v2.3.1

v2.3.0

v2.2.10

v2.2.9

v2.2.6

v2.2.5

v2.2.4

v2.2.3

v2.2.2

v2.2.0

v2.1.21

v2.1.20

v2.1.19

v2.1.18

v2.1.17

v2.1.16

v2.1.14

v2.1.13

v2.1.12

v2.1.11

v2.1.10

v2.1.9

v2.1.8

v2.1.7

v2.1.6

v2.1.5

v2.1.4

v2.1.3

v2.1.2

v2.1.1

v2.1.0

v2.0.9

v2.0.8

Labels

Clear labels   

Beta

Issues related to the a beta release

  

Bug

  

Documentation

  

enhancement

  

help wanted

  

invalid

  

joomla

Joomla related changes

  

question

  

URGENT

  

wontfix

No Label

Beta

Bug

Documentation

enhancement

help wanted

invalid

joomla

question

URGENT

wontfix

Milestone

Clear milestone

No items

No Milestone

Projects

Clear projects

No project

Assignees

Clear assignees

No Assignees

1 Participants

Notifications

Subscribe

Due Date

The due date is invalid or out of range. Please use the format 'yyyy-mm-dd'.

No due date set.

Dependencies

No dependencies set.

Reference: joomla/Component-Builder#275

No description provided.