SQL 'SET SESSION group_concat_max_len' from joomla DB query? Topic is solved

General questions relating to Joomla! 3.x.

Moderator: General Support Moderators

Forum rules
Forum Rules
Absolute Beginner's Guide to Joomla! <-- please read before posting.
Forum Post Assistant - If you are serious about wanting help, you should use this tool to help you post.
Windows Defender SmartScreen Issues <-- please read this if using Windows 10
Post Reply
sazbaztaz
Joomla! Intern
Joomla! Intern
Posts: 60
Joined: Thu Mar 18, 2010 2:20 pm

SQL 'SET SESSION group_concat_max_len' from joomla DB query?

Post by sazbaztaz » Tue Jun 22, 2021 3:11 pm

Hi I'm wondering if anybody run into a similar problem.

I'm trying to fetch results from a database grouping several rows and applying a comma separated GROUP_CONCAT to a column which is a text field with JSON strings in them.

Single row json looks similar to...
{"10":{"p":"10","q":"4","t":"8.36","c":0},"46":{"p":"46","q":"2","t":"2.64","c":0}}

GROUPPED and with comma separated GROUP CONCAT two rows would look something like...
{"10":{"p":"10","q":"4","t":"8.36","c":0},"46":{"p":"46","q":"2","t":"2.64","c":0}},
{"36":{"p":"36","q":"2","t":"1.36","c":1},"54":{"p":"54","q":"6","t":"22.13","c":0}}

Now, the query and everything works fine except when a lot of rows as a result from the query get groupped together this string gets too large and the default GROUP_CONCAT length is 1024 characters and the string get's chopped off.

As I read the group_concat_max_len can be modified with an SQL query and I've tested it on the servers phpMyAdmin SQL command like...

SET SESSION group_concat_max_len=100000;
show variables like 'group_concat_max_len';

The variable is allowed to set as it returns 100000 so there is no problem with that.

My problem is I can't figure out how to pass two queries from Joomla and set the temporary group_concat_max_len.

It must be executed from the same query session butt it cant be run like $db->setQuery('SET SESSION group_concat_max_len=100000, SELECT * FROM...);

I would appreciate any help...

sazbaztaz
Joomla! Intern
Joomla! Intern
Posts: 60
Joined: Thu Mar 18, 2010 2:20 pm

Re: SQL 'SET SESSION group_concat_max_len' from joomla DB query?

Post by sazbaztaz » Tue Jun 22, 2021 4:10 pm

I got it...

If anyone interested here it is...

$db = JFactory::getDbo();
$db->setQuery('SET SESSION group_concat_max_len = 1000000');
$db->query();
$db->setQuery('Select * FROM #__table_name WHERE...');
$elements = $db->loadObjectList();

User avatar
toivo
Joomla! Master
Joomla! Master
Posts: 13570
Joined: Thu Feb 15, 2007 5:48 am
Location: Sydney, Australia

Re: SQL 'SET SESSION group_concat_max_len' from joomla DB query?

Post by toivo » Wed Jun 23, 2021 8:52 am

Thank you for posting the solution. It will will assist others facing a similar limitation in custom SQL queries.
Toivo Talikka, Global Moderator


Post Reply

Return to “General Questions/New to Joomla! 3.x”