laravel-mongodb – complex query example

Complex queries can be pretty hard to translate. I tried to convert a PostgreSQL query for grouping by a boolean and a date at the same time. Here is my original Eloquent query:

ModelName::selectRaw('COUNT(*) AS "count", boolean_one, date_trunc(\'day\', created_at) as date')
    ->where('created_at', '>=', Carbon::now()->subMonth())
    ->where('boolean_two', '=', $booleanTwo)
    ->where('string_value', 'LIKE', $searchForString . '%')
    ->groupBy('boolean_one')->groupBy('date')
    ->orderBy('date')->get();

And here’s the same query translated, using Moloquent:

ModelName::raw(function ($collection) {
    return $collection->aggregate([
        [
            '$match' => [
                'created_at'   => ['$gt' => new MongoDate(Carbon::now()->subMonths(1)->timestamp)],
                'boolean_two'  => $booleanTwo,
                'string_value' => ['$regex' => new MongoRegex('/.*' . $searchForString . '.*/')]
            ],
        ],
        [

            '$group' => [
                '_id'   => [
                    'month'       => ['$month' => '$created_at'],
                    'day'         => ['$dayOfMonth' => '$created_at'],
                    'year'        => ['$year' => '$created_at'],
                    'boolean_one' => '$boolean_one'
                ],
                'count' => [
                    '$sum' => 1
                ]
            ]
        ]
    ]);
});;

Importing Postgresql dump

sudo -u postgres pg_restore --verbose --clean --no-acl --no-owner -h localhost -U postgres -d dbname ./dumpfilename

Connecting to Heroku Postgre from a remote host

It was kind of tricky to figure out, but it turned out that Heroku Postgre requires some SSL tricks to connect from outside. Here are my PHPStorm connection settings, Advanced tab:

2015-12-21_11-46-58

MySQL replication

This is mostly a note for myself, so I don’t have to do research again. Bunch of usefull links:

Servers for hackers – replication

Exporting / Importing data in a UTF8 safe way

 

Replication was easy in my case, the real issue was with handling huge backup files. Here are the commands I used for export, transfer and import of data.

Also, remember to check master status (log position) and to lock the database before you start.

mysqldump -u root -p --routines --triggers --all-databases -r database.dump

sudo rsync -vP --inplace utf8.dump user@server.com:/home/user/

mysql -uroot -p --default-character-set=utf8
mysql> SOURCE database.dump;

In case you’re getting binary logging errors (“Statement based binlogging does not work in isolation level READ UNCOMMITTED and READ COMMITTED since the necessary locks cannot be taken.” or so), make sure this line is present in your MySQL config:

binlog_format=row

I hate WordPress editor.

An update!

So, I updated the website witb a nice theme and I’m updating the content right now. Looks like I’ll keep the blog alive too. I plan to publish new articles about programming every time I feel I do something new (not only for me!). My work at Unknown Worlds got a bit more challenging since few weeks ago – I started working on the interface of our upcoming game, Subnautica. Well, let’s get rolling!