[BUG]: dynamic get where clauses for joins not distributed properly

Tom van der Laan commented

2025-08-27 19:49:46 +00:00

Member

What Happened?

I have a dynamic get type getListQuery and i join table b on table a. and table c on table b.

Both return row types for the joins are "multiple. See attached screenshots for detailed info's.

Now i was expecting that the where clauses would be put into their respective functions (queries) where those join queries are run.

But for the table b it did not work. It was added to the main query of table a. But for table c it did end up in the right spot inside the query of table c.

So did i find a bug or is it not possible and shouldn't have worked for table c either?

here is the generated code:

	/**
	 * Method to build an SQL query to load the list data.
	 *
	 * @return   string  An SQL query
	 * @since    1.6
	 */
	protected function getListQuery()
	{
		// Make sure all records load, since no pagination allowed.
		$this->setState('list.limit', 0);
		// Get a db connection.
		$db = $this->getDatabase();

		// Create a new query object.
		$query = $db->getQuery(true);

		// Get from #__eventschedule_schedule as a
		$query->select($db->quoteName(
			array('a.id','a.asset_id','a.name','a.published','a.created_by','a.modified_by','a.created','a.modified','a.version','a.hits','a.ordering'),
			array('id','asset_id','name','published','created_by','modified_by','created','modified','version','hits','ordering')));
		$query->from($db->quoteName('#__eventschedule_schedule', 'a'));
		// Check if $this->state->get('parameters.menu')->get('schedule') is a string or numeric value.
		$checkValue = $this->state->get('parameters.menu')->get('schedule');
		if (isset($checkValue) && StringHelper::check($checkValue))
		{
			$query->where('a.id = ' . $db->quote($checkValue));
		}
		elseif (is_numeric($checkValue))
		{
			$query->where('a.id = ' . $checkValue);
		}
		// Get where a.published is 1
		$query->where('a.published = 1');
		// Get where b.published is 1
		$query->where('b.published = 1');

		// return the query object
		return $query;
	}

	/**
	 * Method to get an array of data items.
	 *
	 * @return  mixed  An array of data items on success, false on failure.
	 * @since   1.6
	 */
	public function getItems()
	{
		$user = $this->user;
		// check if this user has permission to access item
		if (!$user->authorise('site.schedule.access', 'com_eventschedule'))
		{
			$app = Factory::getApplication();
			$app->enqueueMessage(Text::_('COM_EVENTSCHEDULE_NOT_AUTHORISED_TO_VIEW_SCHEDULE'), 'error');
			// redirect away to the home page if no access allowed.
			$app->redirect(Uri::root());
			return false;
		}
		// load parent items
		$items = parent::getItems();

		// Get the global params
		$globalParams = ComponentHelper::getParams('com_eventschedule', true);

		// Insure all item fields are adapted where needed.
		if (UtilitiesArrayHelper::check($items))
		{
			foreach ($items as $nr => &$item)
			{
				// Always create a slug for sef URL's
				$item->slug = ($item->id ?? '0') . (isset($item->alias) ? ':' . $item->alias : '');
				// set idScheduleContainerB to the $item object.
				$item->idScheduleContainerB = $this->getIdScheduleContainerFfae_B($item->id);
			}
		}

		// return items
		return $items;
	}

/**
	 * Method to get an array of Container Objects.
	 *
	 * @return mixed  An array of Container Objects on success, false on failure.
	 *
	 */
	public function getIdScheduleContainerFfae_B($id)
	{
		// Get a db connection.
		$db = $this->getDatabase();

		// Create a new query object.
		$query = $db->getQuery(true);

		// Get from #__eventschedule_container as b
		$query->select($db->quoteName(
			array('b.id','b.asset_id','b.schedule','b.name','b.published'),
			array('id','asset_id','schedule','name','published')));
		$query->from($db->quoteName('#__eventschedule_container', 'b'));
		$query->where('b.schedule = ' . $db->quote($id));

		// Reset the query using our newly populated query object.
		$db->setQuery($query);
		$db->execute();

		// check if there was data returned
		if ($db->getNumRows())
		{
			$items = $db->loadObjectList();

			// Convert the parameter fields into objects.
			foreach ($items as $nr => &$item)
			{
				// set idContainerSectionC to the $item object.
				$item->idContainerSectionC = $this->getIdContainerSectionFfae_C($item->id);
			}
			return $items;
		}
		return false;
	}

	/**
	 * Method to get an array of Section Objects.
	 *
	 * @return mixed  An array of Section Objects on success, false on failure.
	 *
	 */
	public function getIdContainerSectionFfae_C($id)
	{
		// Get a db connection.
		$db = $this->getDatabase();

		// Create a new query object.
		$query = $db->getQuery(true);

		// Get from #__eventschedule_section as c
		$query->select($db->quoteName(
			array('c.id','c.asset_id','c.container','c.name','c.published'),
			array('id','asset_id','container','name','published')));
		$query->from($db->quoteName('#__eventschedule_section', 'c'));
		$query->where('c.container = ' . $db->quote($id));
		// Get where c.published is 1
		$query->where('c.published = 1');

		// Reset the query using our newly populated query object.
		$db->setQuery($query);
		$db->execute();

		// check if there was data returned
		if ($db->getNumRows())
		{
			return $db->loadObjectList();
		}
		return false;
	}

Steps to reproduce the Bug

Create a dynamic get, add two joins. table b -> a and table c -> b. type multi row.

Add 3 where options:

a.published = 1
b.published = 1
c.published = 1

compile and review code.

c.published is in right spot. b.published is not.

Which Joomla version are you compiling in?

5.3.3

Which PHP version are you compiling in?

8.3

Which Joomla versions are you targeting?

5

Which PHP version are you targeting?

8.3

Which Web server is JCB running on?

litespeed

Which Relational Database is JCB running on?

mariadb 10.11

Which OS is JCB running on?

almalinux 9

Which JCB version are you using?

5.1.1 stable

Where in JCB did this issue occur?

Dynamic Get(admin_view)

On which browsers did you encounter the issue?

Safari

Additional Comments

No response

### What Happened? I have a dynamic get type getListQuery and i join table b on table a. and table c on table b. Both return row types for the joins are "multiple. See attached screenshots for detailed info's. Now i was expecting that the where clauses would be put into their respective functions (queries) where those join queries are run. But for the table b it did not work. It was added to the main query of table a. But for table c it did end up in the right spot inside the query of table c. So did i find a bug or is it not possible and shouldn't have worked for table c either? here is the generated code: ``` /** * Method to build an SQL query to load the list data. * * @return string An SQL query * @since 1.6 */ protected function getListQuery() { // Make sure all records load, since no pagination allowed. $this->setState('list.limit', 0); // Get a db connection. $db = $this->getDatabase(); // Create a new query object. $query = $db->getQuery(true); // Get from #__eventschedule_schedule as a $query->select($db->quoteName( array('a.id','a.asset_id','a.name','a.published','a.created_by','a.modified_by','a.created','a.modified','a.version','a.hits','a.ordering'), array('id','asset_id','name','published','created_by','modified_by','created','modified','version','hits','ordering'))); $query->from($db->quoteName('#__eventschedule_schedule', 'a')); // Check if $this->state->get('parameters.menu')->get('schedule') is a string or numeric value. $checkValue = $this->state->get('parameters.menu')->get('schedule'); if (isset($checkValue) && StringHelper::check($checkValue)) { $query->where('a.id = ' . $db->quote($checkValue)); } elseif (is_numeric($checkValue)) { $query->where('a.id = ' . $checkValue); } // Get where a.published is 1 $query->where('a.published = 1'); // Get where b.published is 1 $query->where('b.published = 1'); // return the query object return $query; } /** * Method to get an array of data items. * * @return mixed An array of data items on success, false on failure. * @since 1.6 */ public function getItems() { $user = $this->user; // check if this user has permission to access item if (!$user->authorise('site.schedule.access', 'com_eventschedule')) { $app = Factory::getApplication(); $app->enqueueMessage(Text::_('COM_EVENTSCHEDULE_NOT_AUTHORISED_TO_VIEW_SCHEDULE'), 'error'); // redirect away to the home page if no access allowed. $app->redirect(Uri::root()); return false; } // load parent items $items = parent::getItems(); // Get the global params $globalParams = ComponentHelper::getParams('com_eventschedule', true); // Insure all item fields are adapted where needed. if (UtilitiesArrayHelper::check($items)) { foreach ($items as $nr => &$item) { // Always create a slug for sef URL's $item->slug = ($item->id ?? '0') . (isset($item->alias) ? ':' . $item->alias : ''); // set idScheduleContainerB to the $item object. $item->idScheduleContainerB = $this->getIdScheduleContainerFfae_B($item->id); } } // return items return $items; } /** * Method to get an array of Container Objects. * * @return mixed An array of Container Objects on success, false on failure. * */ public function getIdScheduleContainerFfae_B($id) { // Get a db connection. $db = $this->getDatabase(); // Create a new query object. $query = $db->getQuery(true); // Get from #__eventschedule_container as b $query->select($db->quoteName( array('b.id','b.asset_id','b.schedule','b.name','b.published'), array('id','asset_id','schedule','name','published'))); $query->from($db->quoteName('#__eventschedule_container', 'b')); $query->where('b.schedule = ' . $db->quote($id)); // Reset the query using our newly populated query object. $db->setQuery($query); $db->execute(); // check if there was data returned if ($db->getNumRows()) { $items = $db->loadObjectList(); // Convert the parameter fields into objects. foreach ($items as $nr => &$item) { // set idContainerSectionC to the $item object. $item->idContainerSectionC = $this->getIdContainerSectionFfae_C($item->id); } return $items; } return false; } /** * Method to get an array of Section Objects. * * @return mixed An array of Section Objects on success, false on failure. * */ public function getIdContainerSectionFfae_C($id) { // Get a db connection. $db = $this->getDatabase(); // Create a new query object. $query = $db->getQuery(true); // Get from #__eventschedule_section as c $query->select($db->quoteName( array('c.id','c.asset_id','c.container','c.name','c.published'), array('id','asset_id','container','name','published'))); $query->from($db->quoteName('#__eventschedule_section', 'c')); $query->where('c.container = ' . $db->quote($id)); // Get where c.published is 1 $query->where('c.published = 1'); // Reset the query using our newly populated query object. $db->setQuery($query); $db->execute(); // check if there was data returned if ($db->getNumRows()) { return $db->loadObjectList(); } return false; } ``` ### Steps to reproduce the Bug Create a dynamic get, add two joins. table b -> a and table c -> b. type multi row. Add 3 where options: a.published = 1 b.published = 1 c.published = 1 compile and review code. c.published is in right spot. b.published is not. ### Which Joomla version are you compiling in? 5.3.3 ### Which PHP version are you compiling in? 8.3 ### Which Joomla versions are you targeting? 5 ### Which PHP version are you targeting? 8.3 ### Which Web server is JCB running on? litespeed ### Which Relational Database is JCB running on? mariadb 10.11 ### Which OS is JCB running on? almalinux 9 ### Which JCB version are you using? 5.1.1 stable ### Where in JCB did this issue occur? Dynamic Get(admin_view) ### On which browsers did you encounter the issue? Safari ### Additional Comments _No response_

Schermafbeelding 2025-08-27 om 21.45.25.png

214 KiB

Schermafbeelding 2025-08-27 om 21.45.16.png

274 KiB

Schermafbeelding 2025-08-27 om 21.45.06.png

360 KiB

Schermafbeelding 2025-08-27 om 21.45.25.png

Schermafbeelding 2025-08-27 om 21.45.16.png

Schermafbeelding 2025-08-27 om 21.45.06.png

Tom van der Laan added the

Bug

label 2025-08-27 19:49:46 +00:00

Llewellyn commented

2025-08-29 00:24:53 +00:00

Owner

I see, not sure why... I will give it a look, we did massively refactor this area, to get the new Modules native compiler to work, since it also uses the dynamic get feature. It could be that we fixed it already, you can test it with the pre-test package if you like. Else I will get to it sometime next week.

Tom van der Laan commented

2025-08-29 00:43:52 +00:00

Author

Member

Can confirm this bug is also in 5.1.2-alpha 1

Tom van der Laan commented

2025-08-29 00:44:59 +00:00

Author

Member

Also if i remove the b.published = 1.

c.published still does what it supposed to do.

Also if i remove the b.published = 1. c.published still does what it supposed to do.

Llewellyn commented

2025-08-29 00:45:51 +00:00

Owner

https://git.vdm.dev/joomla-pre-test/pkg-component-builder/archive/5.x.zip

Llewellyn commented

2025-08-29 00:46:54 +00:00

Owner

This is v5.1.2-alpha2 (in pre test)

Tom van der Laan commented

2025-08-29 01:07:09 +00:00

Author

Member

same problem still.

[BUG]: dynamic get where clauses for joins not distributed properly #1246

What Happened?

Steps to reproduce the Bug

Which Joomla version are you compiling in?

Which PHP version are you compiling in?

Which Joomla versions are you targeting?

Which PHP version are you targeting?

Which Web server is JCB running on?

Which Relational Database is JCB running on?

Which OS is JCB running on?

Which JCB version are you using?

Where in JCB did this issue occur?

On which browsers did you encounter the issue?

Additional Comments