[?] missed join query for custom field in list view

jcodewalker commented

2023-02-17 17:57:12 +00:00

Steps to reproduce the issue

create a custom field to get id (as key) and title (as value) from another db table

Expected result

in views (list view) title listed

Actual result

in views (list view) key only is listed
In the views model is missing the join-the-other-table query as it was in JCB until version 2.10.13 (that I know of, as I just upgraded to 3.1.13 from there).

System information (as much as possible)

OS Name & Version:
MySql Version:
Apache Version:
PHP Version:
Joomla Version: 3.10.11
JCB Version: 3.1.13
Browser:

Additional comments

So, have we to update all the query manually with custom code in the view?
Just to know what to do.

### Steps to reproduce the issue create a custom field to get id (as key) and title (as value) from another db table ### Expected result in views (list view) title listed ### Actual result in views (list view) key only is listed In the views model is missing the join-the-other-table query as it was in JCB until version 2.10.13 (that I know of, as I just upgraded to 3.1.13 from there). ### System information (as much as possible) - OS Name & Version: - MySql Version: - Apache Version: - PHP Version: - Joomla Version: 3.10.11 - JCB Version: 3.1.13 - Browser: ### Additional comments So, have we to update all the query manually with custom code in the view? Just to know what to do.

Llewellyn commented

2023-02-17 20:18:51 +00:00

Owner

Where are you getting JCB3.1.13?

Where are you getting JCB3.1.13? ![image](/attachments/8b30c92b-2340-453f-819e-a635d0d31301)

image.png

25 KiB

Llewellyn commented

2023-02-17 20:19:52 +00:00

Owner

Only the versions listed here are to be used, and preferably the latest version.

jcodewalker commented

2023-02-18 10:57:36 +00:00

Author

Hello, still with the latest you given me the link of to test compiler issue.

jcodewalker commented

2023-02-23 18:20:13 +00:00

Author

Hello, any update?

Llewellyn commented

2023-02-23 21:50:37 +00:00

Owner

If you are on the latest version? what is that version?

Then you will need to give me more details, screenshots as I am not fully able to follow what exactly is the issue and where and in what circumstances you arrived at this issue.

If you are on the latest version? what is that version? Then you will need to give me more details, screenshots as I am not fully able to follow what exactly is the issue and where and in what circumstances you arrived at this issue.

jcodewalker commented

2023-02-24 08:02:33 +00:00

Author

Hello, I'm using the version you given me the link of to test the "install all above extensions" button issue after the compiler.

Said that, add a custom field to demo component, look view, as follows:

		// Get the user object.
		$user = JFactory::getUser();
		// Get the databse object.
		$db = JFactory::getDBO();
		$query = $db->getQuery(true);
		$query->select($db->quoteName(array('a.id','a.name'),array('id','component_name')));
		$query->from($db->quoteName('#__componentbuilder_joomla_component', 'a'));
		$query->where($db->quoteName('a.published') . ' = 1');
		$query->order('a.name ASC');
		// Implement View Level Access (if set in table)
		if (!$user->authorise('core.options', 'com_componentbuilder'))
		{
			$columns = $db->getTableColumns('#__componentbuilder_joomla_component');
			if(isset($columns['access']))
			{
				$groups = implode(',', $user->getAuthorisedViewLevels());
				$query->where('a.access IN (' . $groups . ')');
			}
		}
		$db->setQuery((string)$query);
		$items = $db->loadObjectList();
		$options = array();
		if ($items)
		{
			$options[] = JHtml::_('select.option', '', JText::_('Select an option'));
			foreach($items as $item)
			{
				$options[] = JHtml::_('select.option', $item->id, $item->component_name);
			}
		}
		return $options;

Compile, install, add a new record to "look" selecting a component from this field.
I would expect to have the component's name shown in "looks" view instead only the "id" is listed.
If you check the model you can see is missing the "join" componentbuilder_joomla_component table in getListQuery method.

I hope this helps you to understand the issue.

Hello, I'm using the version you given me the link of to test the "install all above extensions" button issue after the compiler. Said that, add a custom field to demo component, look view, as follows: ``` // Get the user object. $user = JFactory::getUser(); // Get the databse object. $db = JFactory::getDBO(); $query = $db->getQuery(true); $query->select($db->quoteName(array('a.id','a.name'),array('id','component_name'))); $query->from($db->quoteName('#__componentbuilder_joomla_component', 'a')); $query->where($db->quoteName('a.published') . ' = 1'); $query->order('a.name ASC'); // Implement View Level Access (if set in table) if (!$user->authorise('core.options', 'com_componentbuilder')) { $columns = $db->getTableColumns('#__componentbuilder_joomla_component'); if(isset($columns['access'])) { $groups = implode(',', $user->getAuthorisedViewLevels()); $query->where('a.access IN (' . $groups . ')'); } } $db->setQuery((string)$query); $items = $db->loadObjectList(); $options = array(); if ($items) { $options[] = JHtml::_('select.option', '', JText::_('Select an option')); foreach($items as $item) { $options[] = JHtml::_('select.option', $item->id, $item->component_name); } } return $options; ``` Compile, install, add a new record to "look" selecting a component from this field. I would expect to have the component's name shown in "looks" view instead only the "id" is listed. If you check the model you can see is missing the "join" componentbuilder_joomla_component table in getListQuery method. I hope this helps you to understand the issue.

Llewellyn commented

2023-02-24 08:39:19 +00:00

Owner

I'm sorry, simply telling me what you did or did not do does not provide enough detail to help me diagnose the issue. To help me better understand the problem, I would need to see screenshots of your JCB GUI settings, as the details are critical.

Furthermore, stating that you are using a version of JCB that I gave you is not sufficient. In the opening of this issue, you mentioned using version 3.1.13, which we have deleted minutes after its release because it is broken. You should be using either version 3.1.17 or 3.1.18 - one being the last stable version and the other being the current beta version. Without knowing which version you are using, it is difficult to reproduce your environment.

To give you an idea of the complexity of the situation, I have over 100 large extensions, all with custom fields and front-end queries, and they all work on JCB 3.1.17 and 3.1.18. Therefore, there are three ways this could go: I may ignore the issue because I believe you are doing something incorrectly that is not related to JCB, I may ask for more details to better understand the issue, or we may identify an edge case that I have not encountered in the last five years and write a patch to resolve it.

I'm sorry, simply telling me what you did or did not do does not provide enough detail to help me diagnose the issue. To help me better understand the problem, I would need to see screenshots of your JCB GUI settings, as the details are critical. Furthermore, stating that you are using a version of JCB that I gave you is not sufficient. In the opening of this issue, you mentioned using version 3.1.13, which we have deleted minutes after its release because it is broken. You should be using either version 3.1.17 or 3.1.18 - one being the last stable version and the other being the current beta version. Without knowing which version you are using, it is difficult to reproduce your environment. To give you an idea of the complexity of the situation, I have over 100 large extensions, all with custom fields and front-end queries, and they all work on JCB 3.1.17 and 3.1.18. Therefore, there are three ways this could go: I may ignore the issue because I believe you are doing something incorrectly that is not related to JCB, I may ask for more details to better understand the issue, or we may identify an edge case that I have not encountered in the last five years and write a patch to resolve it.

jcodewalker commented

2023-02-24 08:51:08 +00:00

Author

ok, sorry for my lack of info.

In dashboard I have:
version: 3.1.18
date: 16th February, 2023
As far as I know it should be the latest.

Php: 7.4
Joomla: 3.10.11 fresh new installation

Not other components installed, just JCB.

ok, sorry for my lack of info. In dashboard I have: version: 3.1.18 date: 16th February, 2023 As far as I know it should be the latest. Php: 7.4 Joomla: 3.10.11 fresh new installation Not other components installed, just JCB.

Schermata 2023-02-24 alle 09.46.22.png

734 KiB

Schermata 2023-02-24 alle 09.46.55.png

367 KiB

Schermata 2023-02-24 alle 09.46.37.png

473 KiB

Schermata 2023-02-24 alle 09.47.27.png

124 KiB

Schermata 2023-02-24 alle 09.47.09.png

328 KiB

Llewellyn commented

2023-02-24 09:09:19 +00:00

Owner

There has been a major update on the beta branch, open this link to see the dates and updates. Also I need to see where you created the custom field and where you linked it.

There has been a major update on the beta branch, open [this link to see the dates](https://git.vdm.dev/joomla-beta/pkg-component-builder) and updates. Also I need to see where you created the custom field and where you linked it.

Llewellyn commented

2023-02-24 09:11:18 +00:00

Owner

Here are the list of updates: https://git.vdm.dev/joomla-beta/pkg-component-builder/commits/branch/master

When using the (nightly-build [which is version 3.1.18 at this time]) beta version you must stay at the tip of the branch as it is unstable and each update fixes what has been seen, or reported. This means you should update your version now.

Here are the list of updates: https://git.vdm.dev/joomla-beta/pkg-component-builder/commits/branch/master When using the (nightly-build [which is version 3.1.18 at this time]) beta version you must stay at the tip of the branch as it is unstable and each update fixes what has been seen, or reported. This means you should update your version now.

jcodewalker commented

2023-02-24 10:25:49 +00:00

Author

So, updated JCB just now but still I get the same issue:

	/**
	 * 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 demo_item table
		$query->from($db->quoteName('#__demo_look', '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.
		$_access = $this->getState('filter.access');
		if ($_access && is_numeric($_access))
		{
			$query->where('a.access = ' . (int) $_access);
		}
		elseif (DemoHelper::checkArray($_access))
		{
			// Secure the array for the query
			$_access = ArrayHelper::toInteger($_access);
			// Filter by the Access Array.
			$query->where('a.access IN (' . implode(',', $_access) . ')');
		}
		// Implement View Level Access
		if (!$user->authorise('core.options', 'com_demo'))
		{
			$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.name LIKE '.$search.' OR a.description LIKE '.$search.' OR a.add LIKE '.$search.' OR a.alias LIKE '.$search.')');
			}
		}


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

		return $query;
	}

As you can see the join query to componentbuilder_joomla_component table (see the custom field described above) is still missing.

So, updated JCB just now but still I get the same issue: ``` /** * 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 demo_item table $query->from($db->quoteName('#__demo_look', '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. $_access = $this->getState('filter.access'); if ($_access && is_numeric($_access)) { $query->where('a.access = ' . (int) $_access); } elseif (DemoHelper::checkArray($_access)) { // Secure the array for the query $_access = ArrayHelper::toInteger($_access); // Filter by the Access Array. $query->where('a.access IN (' . implode(',', $_access) . ')'); } // Implement View Level Access if (!$user->authorise('core.options', 'com_demo')) { $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.name LIKE '.$search.' OR a.description LIKE '.$search.' OR a.add LIKE '.$search.' OR a.alias LIKE '.$search.')'); } } // Add the list ordering clause. $orderCol = $this->state->get('list.ordering', 'a.id'); $orderDirn = $this->state->get('list.direction', 'desc'); if ($orderCol != '') { $query->order($db->escape($orderCol . ' ' . $orderDirn)); } return $query; } ``` As you can see the join query to componentbuilder_joomla_component table (see the custom field described above) is still missing.

jcodewalker commented

2023-02-27 17:57:52 +00:00

Author

so, it seems once I fixed a custom field issue (see #978) I get the model compiled correctly. It seems like the compiler didn't complete the process(?) due to custom field issue.

so, it seems once I fixed a custom field issue (see https://git.vdm.dev/joomla/Component-Builder/issues/978) I get the model compiled correctly. It seems like the compiler didn't complete the process(?) due to custom field issue.

jcodewalker closed this issue

2023-02-27 17:57:57 +00:00

jcodewalker reopened this issue

2023-03-01 08:15:06 +00:00

jcodewalker commented

2023-03-01 08:17:54 +00:00

Author

Hello mate,
still I get the same missing join query in another view just created.

JCB version: 3.1.19
Joomla: 3.10.11
PHP: 7.4

What can I do?

Hello mate, still I get the same missing join query in another view just created. JCB version: 3.1.19 Joomla: 3.10.11 PHP: 7.4 What can I do?

Schermata 2023-03-01 alle 09.12.24.png

478 KiB

Schermata 2023-03-01 alle 09.12.47.png

451 KiB

Schermata 2023-03-01 alle 09.13.06.png

471 KiB

Schermata 2023-03-01 alle 09.13.29.png

549 KiB

Schermata 2023-03-01 alle 09.13.45.png

252 KiB

jcodewalker commented

2023-03-01 08:18:32 +00:00

Author

and the compiled query:

Schermata 2023-03-01 alle 09.14.24.png

237 KiB

root commented

2023-03-01 08:43:59 +00:00

Member

Hello there, my friend. Allow me to impart some wisdom unto thee. If Llewellyn assures you that all is well, yet you persist in encountering difficulties, it is probable that you are not configuring it properly. My recommendation is to carefully scrutinize all of the tutorials before expending further time and energy on this task. In so doing, you shall save yourself a considerable amount of trouble in the long haul.

Have you indeed established a seamless connection between the field and the admin view, as I had previously suggested? Were the database values set up in strict accordance with the instructions? And have you thoroughly reviewed all of the tutorials pertaining to custom field creation? Judging from Llewellyn's recent JCB updates, it appears that he has yet to delve into these particular areas. Ergo, if you are encountering complications, it is quite likely that you have made an error.

@Llewellyn, my esteemed colleague, how may I be of assistance? I have returned and have perceived that you could benefit from a helping hand. Tell me what you require aid with?

Hello there, my friend. Allow me to impart some wisdom unto thee. If Llewellyn assures you that all is well, yet you persist in encountering difficulties, it is probable that you are not configuring it properly. My recommendation is to carefully scrutinize all of the tutorials before expending further time and energy on this task. In so doing, you shall save yourself a considerable amount of trouble in the long haul. Have you indeed established a seamless connection between the field and the admin view, as I had previously suggested? Were the database values set up in strict accordance with the instructions? And have you thoroughly reviewed all of the tutorials pertaining to custom field creation? Judging from Llewellyn's recent JCB updates, it appears that he has yet to delve into these particular areas. Ergo, if you are encountering complications, it is quite likely that you have made an error. @Llewellyn, my esteemed colleague, how may I be of assistance? I have returned and have perceived that you could benefit from a helping hand. Tell me what you require aid with?

🎉 1

jcodewalker commented

2023-03-01 09:18:47 +00:00

Author

Hello ro-ot,

Thanks for the time you took to reply.

The field was linked properly to the view but not checked to be listed in list view.
So, thanks for your hint!

Hello ro-ot, Thanks for the time you took to reply. The field was linked properly to the view but **not checked** to be listed in list view. So, thanks for your hint!

jcodewalker closed this issue

2023-03-01 09:18:52 +00:00

root commented

2023-03-01 10:07:14 +00:00

Member

you're welcome!

[?] missed join query for custom field in list view #977

Steps to reproduce the issue

Expected result

Actual result

System information (as much as possible)

Additional comments