⚠ In case you've missed it, we have migrated to our new website, with a brand new forum. For more details about the migration you can read our blog post for website migration. This is an archived forum. ⚠

  •     

profile picture

group by



macphisto

macphisto
  • profile picture
  • Member

Posted 14 September 2012 - 09:50 AM

I want use group by, but how????

midnigther

midnigther
  • profile picture
  • Member

Posted 14 September 2012 - 11:10 AM

You can use where () function to implement it.


$where = 'id = '.$id.' GROUP_BY '.$username;
$crud->where ($where);

macphisto

macphisto
  • profile picture
  • Member

Posted 14 September 2012 - 16:30 PM

thanks :)

zrmba

zrmba
  • profile picture
  • Member

Posted 14 January 2013 - 16:50 PM

Hi midnigther I just wanted to act like you said upper
[quote name='midnigther' timestamp='1347621053' post='3422']
You can use where () function to implement it.


$where = 'id = '.$id.' GROUP_BY '.$username;
$crud->where ($where);

[/quote]

But in fact my problem is that i wanted to add a GROUP BY clause so i tried something like this:

$where = 'idpayement = * GROUP BY nofacture';
$crud->where ($where);


But I'm getting in trouble with the SQL engine. So how to use such things i mean

select sum(field1), field2 from tale_name group by field2

for example?

o_Odin

o_Odin
  • profile picture
  • Member

Posted 14 January 2013 - 18:00 PM

There's a solution. I would be glad if someone offers a better option.

CREATE TABLE IF NOT EXISTS `help` (
`help_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`type` varchar(255) NOT NULL,
`cost` int(10) unsigned NOT NULL,
PRIMARY KEY (`help_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=6 ;

INSERT INTO `help` (`help_id`, `type`, `cost`) VALUES
(1, 'type_1', 123),
(2, 'type_1', 456),
(3, 'type_2', 789),
(4, 'type_2', 101112),
(5, 'type_3', 0);


function help()
{
$crud = new grocery_CRUD();
$crud
->set_table('help')
->columns('type', 'E')
->where('1=1 group by `type`', NULL, FALSE)
->callback_column('E', array($this, 'cbc'))
;
$output = $crud->render();
$this->load->view('WriteHereYourView.php', $output);
}

function cbc($value, $row)
{
$E = @$this->db->query('select sum(cost) as result from help where type = ?', array($row->type))->row()->result;
return ($E) ? (string) $E : '0'; // or: return ($E) ? (string) $E : '~empty~';
}

zrmba

zrmba
  • profile picture
  • Member

Posted 15 January 2013 - 12:47 PM

Hi o_Odin,
i think that your solution is too complicated.
I don't know but grocery aim is to make the easier and shorter code as possible. So first i have tried
$where = '1=1 GROUP BY nofacture';
$crud->where ($where);

but i got in trouble.
Once it will be possible to GROUP BY, then maybe we can manage the sum() and such functions in the columns() grocery function.
Right?

o_Odin

o_Odin
  • profile picture
  • Member

Posted 15 January 2013 - 15:06 PM

Unfortunately, I do not know a more simple solution.
In a log of queries:

SHOW TABLES FROM `my_database`
SHOW COLUMNS FROM `help`
DESCRIBE `help`
SELECT `help`.* FROM (`help`) WHERE 1=1 GROUP BY `type` LIMIT 50

As you can see, GC does not send any column names in query. He selects them all, and then processed according to its own algorithm.

zrmba

zrmba
  • profile picture
  • Member

Posted 15 January 2013 - 15:55 PM

I guess!
but as using the following syntax,
$crud->set_relation('customerNumber','customers','{contactLastName} {contactFirstName}');

i hoped that we could simply say this:
 ->columns('customerName','contactLastName','phone','city','country','max(creditLimit)')

or
 ->columns('customerName','contactLastName','phone','city','country','sum(creditLimit)')

o_Odin

o_Odin
  • profile picture
  • Member

Posted 15 January 2013 - 16:19 PM

zrmba, you absolutely right! We can use Active Record before calling GC. Voila:

function help()
{
$crud = new grocery_CRUD();
$this->db
->select('sum(cost) as our_sum')
->group_by('type')
->order_by('our_sum', 'desc');
$crud
->set_table('help')
->columns('type', 'our_sum');
$output = $crud->render();
$this->load->view('WriteHereYourView.php', $output);
}

If we want to see a column like this, which is not in the table, we must specify it in

$crud->columns('type', 'our_sum');

I think it opens a LOT of opportunities :)


zrmba

zrmba
  • profile picture
  • Member

Posted 16 January 2013 - 09:44 AM

Hi o_Odin,
you are cool man to have get this for us, with this I am UNSTOPPABLE with GC.

But how did you found this? :D

Once more thanks a lot

victor

victor
  • profile picture
  • Member

Posted 16 January 2013 - 17:04 PM

Hi guys . That's a good solution!
Regards!

carlosT

carlosT
  • profile picture
  • Member

Posted 24 October 2013 - 11:55 AM

Can anyone help me make a query that only retrieves the latest record for a particular group (using group by user_id). I am lost :(


carlosT

carlosT
  • profile picture
  • Member

Posted 26 October 2013 - 09:25 AM

Just in case anyone is wondering. I had a table with multiple assessments per user_id but I only wanted to get the latest assessment per user, it was achieved by this...

$crud->where('assessment_number IN (SELECT MAX(assessment_number) FROM criticals GROUP BY user_id)');


Ian Zamora

Ian Zamora
  • profile picture
  • Member

Posted 23 April 2016 - 00:06 AM

thanks, i was really suffer with sum query but whit this topic finally i found the solution.  cheers


Ahmed Adebiyi Oladapo

Ahmed Adebiyi Oladapo
  • profile picture
  • Member

Posted 27 August 2016 - 17:08 PM

Hi o_Odin,
you are cool man to have get this for us, with this I am UNSTOPPABLE with GC.

But how did you found this? :D

Once more thanks a lot

 

 

zrmba, you absolutely right! We can use Active Record before calling GC. Voila:

function help()
{
$crud = new grocery_CRUD();
$this->db
->select('sum(cost) as our_sum')
->group_by('type')
->order_by('our_sum', 'desc');
$crud
->set_table('help')
->columns('type', 'our_sum');
$output = $crud->render();
$this->load->view('WriteHereYourView.php', $output);
}

If we want to see a column like this, which is not in the table, we must specify it in

$crud->columns('type', 'our_sum');

I think it opens a LOT of opportunities :)

 

 

you guys have really helped me as i am totally new to PHP and grocery crud. 

Presently I am having a problem using the having clause. I will be glad if anyone in here can help me

 

 

SELECT Current_Country, count(Training_Status) as Total, Training_Status from Staff_data
GROUP by Current_Country, Training_Status, Service_Status
HAVING Training_Status in ('support staff', 'trained staff') 
AND Service_Status = 'serving'
 
My only problem is the having clause