Error when use 2 or more group_by inside subqueries

[agusneos]

show error queries when contain 2 or more group_by inside subqueries. example
$this->db->select('field1');
$sub = $this->subquery->start_subquery('from');
$sub->select('field1');
$sub->from($table_1);
$sub->where('field2', $con1)
->where('DATE(field3) >=', $con2)
->where('DATE(field3) <=', $tgcon3_to);
$sub->group_by('field4')
->group_by('field5');
$this->subquery->end_subquery('test');
$total = $this->db->count_all_results();

[NTICompass]

What error are you seeing here? Also, why do you need a FROM subquery here in the first place? Wouldn't this query work without a subquery?

[agusneos]

i attach my sample sql query
http://sqlfiddle.com/#!9/cd573/4
there is multiple group_by.
thx..

[agusneos]

Can you test in your subquery?

[NTICompass]

Thank you for providing your sample SQL query. I see now why you need to use a subquery.

I also see the issue and I am looking into fixing it.

The issue is that from is trying to put backticks around its argument to protect them and it's failing with multiple GROUP_BY statements.

What's happening is:

FROM ((SELECT ... GROUP BY `field4`, `field5`)` AS test)

I'll have to figure out how to stop from from adding in backticks.

[agusneos]

Thx for your reply,
I understand the above explanation.
I'm waiting for your update..

[agusneos]

Do you update this ?
Thx..

[NTICompass]

Apologies, but I have not fixed this issue yet.

I've been busy with other work and have not come up with a solution yet.

I'll see if I can at least find a workaround before a full fix.

I'll keep you posted.

[NTICompass]

I think I figured out a solution. I need to take the query being passed to ->from() and change it from GROUP BY a, b into GROUP BY (a), (b). Adding those parenthesis seems to fix this issue.

This is something I can do automatically in the library. I made a crappy fix, if you want to try out the develop branch. Let me know if that works or not.

In the next few days, I can work to clean up that fix and put it on the master branch.

[MrPetovan]

By the way, this issue with the backticks also happens with IN statements in the subquery, it isn't limited to GROUP BY statements. When I tried to upgrade CodeIgniter on a 5 years-old website, I got the same kind of error, and I must have had fixed in in my previous system folder, except I overwrote it with the upgraded files. headbang on desk

[NTICompass]

You mean IN statements inside a FROM clause? I can see if I can add that to the regex fix I did for the GROUP BY or maybe find a better solution.

[MrPetovan]

Thanks, I appreciate it, I haven't been able to find a replacement for Code Igniter 3+, so I stopped at the version 2.2.3 with a system folder hack. In the meantime, I added a flag to prevent from() from cutting the string between commas and protecting identifiers, and I only set the flag from Subquery.

In system/database/DB_active_record.php:

	/**
	 * From
	 *
	 * Generates the FROM portion of the query
	 *
	 * @param	mixed	can be a string or array
	 * @param	bool	whether to protect identifiers or not
	 * @return	object
	 */
	public function from($from, $protect = TRUE)
	{
		foreach ((array) $from as $val)
		{
			if ($protect && strpos($val, ',') !== FALSE)
			{
				foreach (explode(',', $val) as $v)
				{
					$v = trim($v);
					$this->_track_aliases($v);

					$this->ar_from[] = $this->_protect_identifiers($v, TRUE, NULL, FALSE);

					if ($this->ar_caching === TRUE)
					{
						$this->ar_cache_from[] = $this->_protect_identifiers($v, TRUE, NULL, FALSE);
						$this->ar_cache_exists[] = 'from';
					}
				}

			}
			else
			{
				$val = trim($val);

				if ($protect) {
					// Extract any aliases that might exist.  We use this information
					// in the _protect_identifiers to know whether to add a table prefix
					$this->_track_aliases($val);

					$this->ar_from[] = $this->_protect_identifiers($val, TRUE, NULL, FALSE);

					if ($this->ar_caching === TRUE)
					{
						$this->ar_cache_from[] = $this->_protect_identifiers($val, TRUE, NULL, FALSE);
						$this->ar_cache_exists[] = 'from';
					}
				} else {
					$this->ar_from[] = $val;

					if ($this->ar_caching === TRUE)
					{
						$this->ar_cache_from[] = $val;
						$this->ar_cache_exists[] = 'from';
					}
				}
			}
		}

		return $this;
	}

[cinder92]

any update on this?

[NTICompass]

@cinder92 I'm sorry, but I haven't touched this code in a while. I can take a look at it in the upcoming days if this is still an issue that needs to be sorted out.

There's a fix for the FROM clause in the develop branch: https://github.com/NTICompass/CodeIgniter-Subqueries/blob/develop/libraries/Subquery.php

What code/query are you trying to run?