CODEWITHSUNDEEP
phpmysqlcakephp
LinLin
LinLin

Reputation: 1

How to convert SQL query for CakePHP?

I already have the right SQL query but now I have problem to change the query for CakePHP. What should I do to get the same SQL ? Thanks in advance.

SELECT tests.id, tests.name,tests.tag_on, tests.status, tests.date_scheduled, tests.date_done, tests.remarks,
field_user.name AS fielduser, sales_user.name AS salesuser 
FROM tests 
INNER JOIN users field_user ON (field_user.id = tests.field_user_id) 
INNER JOIN users sales_user ON (sales_user.id = tests.sales_user_id);

This is my TestsController.php 

public function index()
{
        $this->paginate = ['limit' => 100, 'order' => ['date_scheduled' => 'asc'],
        'contain' => ['Users','FieldUsers'],
        'fields' => ['id', 'name', 'tag_on', 'status', 'date_scheduled', 'date_done', 'remarks', Users.id','Users.name','field_user_id','sales_user_id'],
        'sortWhitelist' => ['id', 'name','tag_on', 'status', 'date_scheduled', 'date_done', 'remarks','Users.id','Users.name','field_user_id','sales_user_id'],
        ];
    }

This is my TestsTable.php

class TestsTable extends Table
    {

        public function initialize(array $config)
        {
            parent::initialize($config);

            $this->table('tests');
            $this->displayField('name');
            $this->primaryKey('id');

            $this->belongsTo('Users', [
                'className' => 'Users',
                'foreignKey' => 'sales_user_id',
                'joinType' => 'INNER'
            ]);

            $this->belongsTo('FieldUsers', [
                'className' => 'Users',
                'foreignKey' => 'field_user_id',
                'joinType' => 'INNER'
            ]);

Upvotes: 0

Views: 93

Answers (1)

bikash.bilz
bikash.bilz

Reputation: 821

You can do this in two ways of cake ORM, I am writing one from those 2

$this->Tests->find('all')
        ->contain(['Users', 'FieldUsers'])
        ->select(['Tests.name', 'Tests.tag_on', 'Tests.status', 
             'FieldUsers.name'=>'fieldUser', 'Users.name'=>'userName'])
        ->limit('100')
        ->order(['date_scheduled' => 'asc']);

You can use additional 2 things to get the result as array.

->hydrate(false)->toArray();  //Must be at the end

Happy Coding :)

Upvotes: 1

Related Questions