笔记---mongodb && mongoid

1、mongoid执行查询语句

使用 **Model.collection.aggregate()**执行查询语句

Blog.collection.aggregate([{"$match": {object_type: 'Article'}}, { "$group": {_id: { object_id: "$object_id", user_id: "$user_id" },num: { "$sum": 1 } }}]).
1

参考链接： https://stackoverflow.com/questions/12014009/mongoid-group-by-or-mongodb-group-by-in-rails

2、按照时间分组查询统计

mongid

def self.group_by_date(field, format = 'day')
    key_op = [['year', '$year'], ['month', '$month'], ['day', '$dayOfMonth']]
    key_op = key_op.take(1 + key_op.find_index { |key, op| format == key })
    project_date_fields = Hash[*key_op.collect { |key, op| [key, {op => "$#{field}"}] }.flatten]
    group_id_fields = Hash[*key_op.collect { |key, op| [key, "$#{key}"] }.flatten]
    pipeline = [
        {"$project" => {"name" => 1, field => 1}.merge(project_date_fields)},
        {"$group" => {"_id" => group_id_fields, "count" => {"$sum" => 1}}},
        {"$sort" => {"count" => -1}}
    ]
    User.collection.aggregate(pipeline)
  end

# 使用
User.group_by_date("created_at", "day")
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

mongodb

db.collection.aggregate([
  {$match: {event_type: 'click', created_at: {
    $gte: ISODate('2015-01-01T00:00:00.000Z'),
    $lt: ISODate('2016-01-01T00:00:00.000Z')
  }}},
  {$group: {
      _id: {user_id: '$user_id', account_id: '$account_id', created_at: {
           day: { $dayOfMonth: '$created_at' },
           month: { $month: '$created_at' },
           year: { $year: '$created_at'}
       }},
      'count': {'$sum': 1}
      }
  },
  {$sort: {created_at: -1}},
])
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

参考链接：
https://stackoverflow.com/questions/19384437/best-way-to-group-by-date-with-mongoid
https://www.it1352.com/1634820.html

Article.where(:id.in=>Comment.any_of({:'created_by.user_id' => /#{current_user.id.to_s}/}).pluck(:commentable_id))
1

3、连接mongodb数据库进行操作

# 新建连接
client = Mongo::Client.new(['127.0.0.1:27017'],user:'root', password: '123456',database: 'mymongodb',:connect=>:direct)
# 查询数据
users = client[:users].find({"$and": [{"name": {"$ne": nil}}, {"name": {"$ne": ""}}]}, 
                                   {union_id:1, official_account_id: 1,nickname: 1, avatar_url: 1, name: 1, _id: 0})
# 关闭连接
 client.close()
1
2
3
4
5
6
7

4、mongodb/mongoid 多表关联查询

mongodb的多表关联查询需要使用$lookup操作，实用聚合函数aggregate查询

mongodb

# sql
db.getCollection("users").aggregate([
    {
        $lookup: {
            'from': 'branch_users',
            'localField': '_id',
            'foreignField': 'user_id',
            'as': 'branch_users'
        }

    },
		{
        $lookup: {
            'from': 'user_infos',
            'localField': '_id',
            'foreignField': 'user_id',
            'as': 'user_info'
        }
    },
    {
        $match: {
				    'is_vip': true,
					'user_info.occupation': 'occupation',
					'branch_users.branch': 'BRANCH'
				}
    }
])
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

monogoid

User.collection.aggregate([
{"$lookup": {'from': 'user_infos','localField': '_id','foreignField': 'user_id','as': 'user_info'}},
{"$lookup": {'from': 'branch_users','localField': '_id','foreignField': 'user_id','as': 'branch_users'}},
{"$match": {'is_vip': true, 'user_info.occupation': 'occupation','branch_users.branch': 'BRANCH'}}
])
1
2
3
4
5

…