joomla/Component-Builder
42
28 Fork 13
Code Issues 56 Pull Requests Projects Wiki Activity

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

New Issue
Open
opened 2025-08-27 19:49:46 +00:00 by Tom van der Laan · 6 comments
Tom van der Laan commented 2025-08-27 19:49:46 +00:00
Member
Copy link
Copy source

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_
Scherm­afbeelding 2025-08-27 om 21.45.25.png
214 KiB
Scherm­afbeelding 2025-08-27 om 21.45.16.png
274 KiB
Scherm­afbeelding 2025-08-27 om 21.45.06.png
360 KiB
Scherm­afbeelding 2025-08-27 om 21.45.25.png Scherm­afbeelding 2025-08-27 om 21.45.16.png Scherm­afbeelding 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
Copy link
Copy source

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.

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
Copy link
Copy source

Can confirm this bug is also in 5.1.2-alpha 1

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
Copy link
Copy source

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
Copy link
Copy source

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

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
Copy link
Copy source

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

This is v5.1.2-alpha2 (in pre test)
Tom van der Laan commented 2025-08-29 01:07:09 +00:00
Author
Member
Copy link
Copy source

same problem still.

same problem still.
Sign in to join this conversation.
No Branch/Tag Specified
Labels
Clear labels
Beta
Issues related to the a beta release
 Bug
Documentation
URGENT
enhancement
help wanted
invalid
joomla
Joomla related changes
 question
wontfix
No labels Bug
Milestone
No items
No Milestone
Projects
Clear projects
No projects
Assignees
Clear assignees
aamigo Eunice Hannah Jeff Lemuel Llewellyn Malachi Roline vistamedia Martin (Martin Kopp) aB0t (Robot) ro-ot (root) TLWebdesign (Tom van der Laan)
No Assignees
2 Participants
Notifications
Due Date
No due date set.
Dependencies

No dependencies set.

Reference: joomla/Component-Builder#1246