CODEWITHSUNDEEP
mysqlmongodbyii2-advanced-app
Pathik Vejani
Pathik Vejani

Reputation: 4501

Create simple query using MongoDB with Yii2 advanced template

How can i build query like this in Yii2 and MongoDB:

this is with Yii2 and MySQL:

$connection = new Query;
$sql = "SELECT e.emp_id as emp_id, e.full_name as full_name, e.email as email, e.contact as contact, e.address as address, e.gender as gender, e.avatar as avatar, e.status as status, date_format(e.added_date_time,'%d/%m/%Y %H:%i:%s') as added_date_time, d.dept_name as dept_name FROM employee e, department d WHERE e.dept_id = d.dept_id";
$model = $connection->createCommand($sql);
$posts = $model->queryAll();
return json_encode($posts);

But how can i create same query in MongoDB with Yii2?
Is there any command like createCommand() and pass sql in that function?

Upvotes: 0

Views: 828

Answers (1)

arogachev
arogachev

Reputation: 33548

First of all you need to create model extended from yii\mongodb\ActiveRecord.

Why not to use framework features such as ActiveRecord and ActiveQuery instead of writing raw SQL?

You can write query as regular ActiveQuery:

use app\models\Employee;
use yii\db\Expression;

...

Employee::find()
    ->select([
        'emp_id',
        'full_name'
        'email',
        'contact'
        'address',
        'gender',
        'avatar',
        'status',
        'added_date_time' => new Expression("date_format(added_date_time,'%d/%m/%Y %H:%i:%s')"),
        'dept_name',
    ])
    ->all();

Some notes about the query:

1) It's better to create another model for Department and relation if it's needed. Current selection from two tables and where condition doesn't make sense because all employees will be returned.

2) Because of 1), aliases are redundant.

3) Most of the times with ActiveRecord you don't need to explicitly specify selected attributes.

4) Probably it's better to format date on server side.

Upvotes: 1

Related Questions