Laravel 4流利数

I am using the Laravel 4 php framework and I need to use the fluent query builder to get some statistical type data. I have a database table with a structure like the follwing

id: int
group_id: int
sent_at: datetime
message_id: int

I need a fluent query that will get the count of all rows that have the same message id, and I want to also have that count organized by the group_id.

For example, I have a message id of 1, and each row with the message id of 1 has a group id that may or may not be the same. I need to know what group id's there are in the table for a given message id, and also how many total there is for each group id.

I hope that makes sense.

Thank you for any help.

And this is the Laravel 4 version:

$message_id = 1;

// You can change "amount" to anything that best suits you
// The `orderBy` line is not necessary, but I'm assuming you will may need it.
$groups = DB::table('YOUR_TABLE_NAME')
    ->select(array('group_id', DB::raw('COUNT(*) `amount`')))
    ->where('message_id', '=', $message_id)
    ->groupBy('group_id')
    ->orderBy('amount', 'DESC')
    ->get();

foreach ($groups as $g) {
    echo "$g->group_id has $g->amount messages with id $message_id";
}

Try that query for getting count of group_id for each message_id:

SELECT message_id, count(group_id) FROM table GROUP BY message_id

To get it for given message_id just use "WHERE":

SELECT message_id, count(group_id) FROM table WHERE message_id = XX GROUP BY message_id

To get all group_id for given message_id, just use simple query like that:

SELECT group_id FROM table WHERE message_id = XX

If that's not what you were looking for, let me know - we'll work on correct one :)

(Of course change "table" to your table name)

EDIT: Fluent version example to get count of group_id for message_id = 1:

$query = DB::table('table')
->select(array('message_id', DB::raw('COUNT(group_id) as group_count')))
->where('message_id', '=', 1)
->group_by('message_id')
->get();