Tag Archives: Postgresql

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