Select all columns with `join` function

Need Help
1

I’m using a join in a query like the following:

// In CustomersTable.
$this->find()
    ->select([/* what do I put here? */])
    ->join([
        'Receipts' => [
            'table' => 'receipts',
            'type' => 'INNER',
            'conditions' => [
                'customers.id = receipts.customer_id'
            ]
        ]
    ]);

Note that this is a made-up example, but it illustrates my problem.

I can get all columns from customers by leaving off the select query, or I can specify specific columns to include from both the customers and the receipts tables, but I need to get all columns from both tables. How can I do that?

2

Hi Andrew
So what you want is something like

  • Customer 1
    • Receipt 1
    • Receipt 2
  • Customer 2 ( no receipts)
  • Customer 3
    • Receipt 3

etc?

You should be able to do all of this with the “Has / Belongs” functionality built into CakePHP. Have you gone through the CMS tutorial on the website?

In the Customers table you’d have

        $this->hasMany('Receipts', [
            'foreignKey' => 'customer_id',
        ]);

and in the Receipts table file you’d have

       $this->belongsTo('Customers', [
            'foreignKey' => 'customer_id',
            'joinType' => 'INNER'
        ]);

Then in your Controller you’d have something like

        $customers = $this->Customers->find('All', [
            'contain' => ['Receipts']],
        );

Hope this helps!

3

I’m asking how to do this with joins, not contains.

4

Hi,

You will need to use the selectQuery and the connection Manager:

use Cake\Datasource\ConnectionManager;

$connection = ConnectionManager::get('default');
$query = $connection->selectQuery();
$query = $this->select('*')->from('customers');
$query = $this->innerJoin(
     ['Receipts' => 'receipts'],
     ['Customers.id = Receipts.customer_id']
);
$query = $query->execute();
$data = $query->fetchAll('assoc');

Hope this is what you are looking for.