Work with same mysql object

For Joomla! 4.x Coding related discussions, you could also use: http://groups.google.com/group/joomla-dev-general

Moderators: ooffick, General Support Moderators

Forum rules
Forum Rules
Absolute Beginner's Guide to Joomla! <-- please read before posting, this means YOU.
Forum Post Assistant - If you are serious about wanting help, you will use this tool to help you post.
Windows Defender SmartScreen Issues <-- please read this if using Windows 10.
Locked
User avatar
Dangerous Boy
Joomla! Explorer
Joomla! Explorer
Posts: 302
Joined: Mon Dec 26, 2005 6:25 pm
Location: Home

Work with same mysql object

Post by Dangerous Boy » Mon Oct 03, 2022 11:56 pm

Code: Select all

$db = Factory::getContainer()->get('DatabaseDriver');
$ob = $db->getQuery(true);
$ob->select('*')
->from($db->quoteName('#__content'))
->where("`catid` = '15'")
// More Wheres/filters....
->order($db->quoteName('title') . 'ASC');
$db->setQuery($ob);
$obList = $db->loadObjectList();
so, now that we have the information we need to make a few groups without making new queries such as:
From the $obList we need to group it by year and a few others columns.
In laravel we can work with a single call and manipulate the result.

Code: Select all

$years = $obList->load()->group('YEAR('. $db->quouteName('created') . ')');
Is there a way to do something like that?, the idea is to make a single query and work with that without the need to make another query or many others.
That way I can do a foreach($onList) for the whole object and I can do another foreach($years) for the grouped section...etc
What I try to avoid is the need of another dbQuery to have the same filters but grouped by any possible "key".
Nothing for the moment....

carcam
Joomla! Hero
Joomla! Hero
Posts: 2176
Joined: Sat Dec 29, 2007 1:53 am
Location: Spain
Contact:

Re: Work with same mysql object

Post by carcam » Tue Oct 04, 2022 1:07 pm

I'm not sure if I understand your problem completely.

I think you want to be able to group your elements in the SQL query. You can use the ->group() method of the Query Class to do that. I do not remember the syntax exactly but it should be something like:

$ob->select('*')
->from($db->quoteName('#__content'))
->where("`catid` = '15'")
->group(FIELD)

I'm not sure if you are allowed to group a MySQL query with a calculated field, but if you are, that will work.

Another possibility is to use the ArrrayHelper class on the results to group them by the specific key.
La web es Mejor Con Joomla ¡envíanos tu sitio en Joomla 4!: https://mejorconjoomla.com/showcase
Twitter: @carcam

User avatar
Dangerous Boy
Joomla! Explorer
Joomla! Explorer
Posts: 302
Joined: Mon Dec 26, 2005 6:25 pm
Location: Home

Re: Work with same mysql object

Post by Dangerous Boy » Tue Oct 04, 2022 2:46 pm

carcam wrote:
Tue Oct 04, 2022 1:07 pm
I'm not sure if I understand your problem completely.

I think you want to be able to group your elements in the SQL query. You can use the ->group() method of the Query Class to do that. I do not remember the syntax exactly but it should be something like:

$ob->select('*')
->from($db->quoteName('#__content'))
->where("`catid` = '15'")
->group(FIELD)

I'm not sure if you are allowed to group a MySQL query with a calculated field, but if you are, that will work.

Another possibility is to use the ArrrayHelper class on the results to group them by the specific key.
Thank you but nop...
Your first query has to have all that you need with all the information and filters except for grouping, why not grouping, well, you can't foreach() the results and grouping on the first query means that you have to make another query for details...

Code: Select all

$query = [
 [
  'id'=>12,
  'title'=>'Article 1'
  'created'=>'2001-03-05'
 ],

 [
  'id'=>12,
  'title'=>'Article 1'
  'created'=>'2001-05-03'
 ],

 [
  'id'=>12,
  'title'=>'Article 1'
  'created'=>'2004-03-03'
 ],

 [
  'id'=>12,
  'title'=>'Article 1'
  'created'=>'2005-03-05'
 ]
]
Say that the first query give you an array like that, then you need a "filter" by year in order to build your list.

Code: Select all

<ul>
 <li><a href="#" data-filter-year="2001">2001</a></li>
 <li><a href="#" data-filter-year="2004">2004</a></li>
 <li><a href="#" data-filter-year="2005">2005</a></li>
</ul>
To build "filter" based on the first query you need a foreach() and a temporary array so you don't repeat the information, then you need a count() in order to build a pagination.
that is just one filter of a few, you have to foreach() the same query for as many filters.
The idea is that the first query be able to be use as a "second" query or third or as many....

Code: Select all

$rawData = $db->loadObjectList();
$years = $rawData->load()->group('created');
$special = $rawData->load()->group('special');
$cats = $rawData->load()->group('catid');
In laravel we use something similar, single query then we just work with that, reorder, grouping without making more calls.
Nothing for the moment....

carcam
Joomla! Hero
Joomla! Hero
Posts: 2176
Joined: Sat Dec 29, 2007 1:53 am
Location: Spain
Contact:

Re: Work with same mysql object

Post by carcam » Wed Oct 12, 2022 3:52 pm

I'm afraid we do not have such a feature in Joomla Query class. You need to get all your fields and then do the grouping with PHP.

You might find useful the ArrayHelper class https://api.joomla.org/cms-3/classes/Jo ... elper.html which allows you to filter arrays or sort them by object properties.
La web es Mejor Con Joomla ¡envíanos tu sitio en Joomla 4!: https://mejorconjoomla.com/showcase
Twitter: @carcam


Locked

Return to “Joomla! 4.x Coding”