3

I'm new in Mongo DB and I'm using jenssegers Library in my Laravel Project. I have a table called 'table1' which has '_id','c1','c2','c3','created_at' columns. I need to convert bellow SQL query to mongo db.

select *, count(c3) as total from
(select * from table1 
where c1 in ('1', '32', '6', ...) 
order by created_at desc) as temp 
group by c2 order by created_at desc

I need to select all the column including '_id'. I need only rows containing maximum created_at value by 'order by' when grouped by c2 column and order again result rows by that created_at column.

I found this answer but My question is little bit more complex I think: https://stackoverflow.com/a/24143255/3386509

And this could be useful and related to my problem: http://blog.chomperstomp.com/how-to-order-by-before-group-by-with-mysql/

Community
  • 1
  • 1
Mokhlesur Rahman
  • 751
  • 7
  • 25
  • Please make your code more transparent and start each expression in new row. – Liniel Dec 18 '14 at 08:29
  • 1
    Why do you do a GROUP BY when you have no aggregate functions (e.g. MAX, SUM, COUNT)? Also the general GROUP BY rule says: If a GROUP BY clause is specified, each column reference in the SELECT list must either identify a grouping column or be the argument of a set function! – jarlh Dec 18 '14 at 08:32
  • @jarlh You're right if hi select 'MAX(created_at)' and 'GROUP BY _ID' then will work, but I don't know MongoDB. – Liniel Dec 18 '14 at 08:48
  • I have edited my question and added count(c3) and formatted code. – Mokhlesur Rahman Dec 18 '14 at 09:06

1 Answers1

0

You must use aggregation function see more mongodb.aggregation

Orange_shadow
  • 199
  • 2
  • 9