[macphisto]

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

[midnigther]

You can use where () function to implement it.

$where = 'id = '.$id.' GROUP_BY '.$username;

$crud->where ($where);

[macphisto]

thanks

[zrmba]

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]

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]

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]

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]

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]

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]

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?

Once more thanks a lot

[victor]

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

[carlosT]

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]

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]

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

[Ahmed Adebiyi Oladapo]

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