CODEWITHSUNDEEP
phpmysqlcakephp
Alisa
Alisa

Reputation: 553

cakephp 3 fetch all by using table.*

I am using cakePHP3 query builder to fetch records from two tables using following query, where I want all columns from table1 and selected columns from table2:

$this->loadModel('Table1');
$Table1 = $this->Table1->find('all', array('fields' => array('Table1.*'),'conditions'=>$conditions,'order'=>array('Table1.id'=>'DESC')))->contain(['Table2']);

But I am getting the following error

Error: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'AS `Table1__*` FROM Table1 Table1 LEFT JOIN Table2 Table2 ON ' at line 1

I am new to CakePHP3.

Upvotes: 3

Views: 4516

Answers (2)

arilia
arilia

Reputation: 9398

why not use query builder?

there are many ways to do that

$query= $this->Table1->find('all') 
    ->where($conditions)
    ->order(['Table1.id'=>'DESC'])
    ->contain(['Table2' => [
            'fields' => ['field1', 'field2']
        ]
    ]);

or

$query= $this->Table1->find('all') 
    ->select($this->Table1) // selects all the fields from Table1
    ->select(['Table2.field1', 'Table2.field2']) // selects some fields from Table2
    ->where($conditions)
    ->order(['Table1.id'=>'DESC'])
    ->contain(['Table2']);

or

$query= $this->Table1->find('all') 
    ->where($conditions)
    ->order(['Table1.id'=>'DESC'])
    ->contain(['Table2' => function($q) {
             return $q->select(['field1', 'field2']);
        }
    ]);

Upvotes: 4

Jacek B Budzynski
Jacek B Budzynski

Reputation: 1413

Assuming the correct relationship between tables

Example conditions array 

$conditions = ['Table2.id' > 1];

Selecting Rows From A Table

 $table1 = $this->Table1->find()->select(['Table1.*'])->where($conditions)->contain(['Table2'])->order(['Table1.id'=>'DESC'])->toArray();

OR

$table1 => $this->Table1->find('all', array(
    'contain' => ['Table2'],
    'conditions' => $conditions,
    'fields' => ['Table1.*'],
    'order' => ['Table1.id'=>'DESC']
));

here you have more info about queryBuilder

Upvotes: -2

Related Questions