This is the statement I would like to use convert using CakePHP
SELECT appearances.member_id, members.name, appearances.created
FROM appearances
LEFT JOIN members ON members.mbrid = appearances.member_id
In the list of appearances (templates/Appearances/index.php) I can display the name of the members instead of just the member_id. And of course other fields in the members table if I need to.
What does your index function look like? If the table associations are all set up correctly, it should be a simple matter of containing the Members association, and then your template will have something like $appearance->member->name instead of $appearance->member_id.
This is what my table class looks like. Please note, the foreign key is not members table’s primary key.
class AppearancesTable extends Table
{
public function initialize(array $config): void
{
$this->belongsTo('Members', [
'foreignKey' => 'mbrid'
]);
$this->addBehavior('Timestamp');
}
}
This is what my table association looks like. My index.php resulted in:
Trying to get property 'mbr_name' of non-object mbr_name is the name in the members table.
This is the code I used in the index.php
<?= $appearance->member->mbr_name ?>
Did you add the containment to where you’re reading the list of appearances?
That’s it. done and it works. Great. I added it on the controller file
public function index()
{
$this->loadComponent('Paginator');
$appearances = $this->Paginator->paginate($this->Appearances->find('all', [
'order' => ['Appearances.created' => 'DESC'],
'contain' => ['Members']
]));
$this->set(compact('appearances'));
}That looks exactly right.
SELECT appearances.member_id, members.name, appearances.created, batches.grade_id
FROM appearances
LEFT JOIN members ON members.mbrid = appearances.member_id
LEFT JOIN batches ON members.mbrid = batches.member_id
WHERE batches.no = 1 AND (batch.subno = 0 OR batch.subno = 1)
How do I manage this kind of SQL?
For MembersTable: $this->hasMany('Batches')->setForeignKey('member_id');
For AppearancesController::index()
$visits = $this->Paginator->paginate($this->Visits->find('all', [
'order' => ['Visits.created' => 'DESC'],
'contain' => ['Members', 'Members.Batches']
]));
For index.php: <?= $visit->member->batch->grade_id ?>
But I get this error message: Notice (8): Trying to get property 'grade_id' of non-object in the display page.
And I still haven’t add a WHERE clause in the code – not sure where to put this…
Since members hasMany batches, it’s not $visit->member->batch, it’s $visit->member->batches, and it’s an array.
To add the where clause, I think you may need to convert your query to use the functional mode instead of array parameters, something like this (untested):
$query = $this->Visits->find()
->contain([
'Members' => ['Batches' => [
'queryBuilder' => function (Query $q) {
return $q->where(['batches.no' => 1, 'OR' => ['batch.subno' => 0, 'batch.subno' => 1]]);
},
]],
])
->order(['Visits.created' => 'DESC']);
$visits = $this->Paginator->paginate($query);
Quite possible that this can work with the array method too, I just never do it that way; I find the functional approach more flexible and expressive. Here’s the documentation on passing conditions to contain.
Hi,
I tried the code above. I’m not sure if it’s right but in the debug, I get two (2) SQL statements instead of only one which is this:
SELECT visits.member_id, members.mbr_name, visits.created, batches.grade_id
FROM visits
LEFT JOIN members ON members.mbrid = visits.member_id
LEFT JOIN batches ON batches.member_id = members.mbrid
WHERE batches.batch = '2020' AND (batches.sub_batch = '0' OR batches.sub_batch = '1')
ORDER BY visits.created DESC
DEBUG SQL RESULTS
SELECT
Visits.id AS Visits__id,
Visits.member_id AS Visits__member_id,
Visits.vst_datetime AS Visits__vst_datetime,
Visits.location_id AS Visits__location_id,
Visits.created AS Visits__created,
Visits.modified AS Visits__modified,
Members.id AS Members__id,
Members.mbrid AS Members__mbrid,
Members.mbr_name AS Members__mbr_name,
Members.mbr_type AS Members__mbr_type,
Members.created AS Members__created,
Members.modified AS Members__modified
FROM
visits Visits
LEFT JOIN members Members ON Members.mbrid = (Visits.member_id)
ORDER BY
Visits.created DESC
LIMIT
20 OFFSET 0
SECOND STATEMENT
SELECT
Batches.member_id AS Batches__member_id
FROM
batches Batches
WHERE
(
Batches.member_id in (
187, 1471, 1470, 1463, 250, 350, 1000,
501, 300, 255, 254, 253, 3, 303, 215,
305, 202, 201
)
AND Members.Batches.batch = '0'
AND Members.Batches.sub_batch = '0'
)
This is as expected for a hasMany relationship. Each visit will have a member, each member will have an array of batches.
I know, but in this case, I just want the result to be:
visits.member_id, members.name, visits.created,batches.grade_id
ID | NAME | DATE OF VISIT | GRADE
For the grade_id, I got this error message: Notice (8): Trying to get property 'grade_id' of non-object
template->index.php
code to display grade: <?= $visit->member->batch->grade_id ?>
Controller-> find() code used
$this->Visits->find()
->contain([
'Members' => ['Batches' => [
'queryBuilder' => function (Query $q) {
return $q->where(['Batches.batch' => 1, 'OR' => ['Batches.sub_batch' => '0', 'Batches.sub_batch' => '1']]);
},
]],
])
->order(['Visits.created' => 'DESC']));
Again, as written, there’s no batch property on member, it’s batches. If you absolutely cannot deal with an array in there, and are instead wanting to get 5 copies of the visit and member data if there’s 5 batches that go with it, then follow the suggestion you got on StackOverflow.
This code seem to get me the right SQL I’m looking for:
$visits = $this->Paginator->paginate($this->Visits->find()
->select(['Members.mbrid', 'Members.mbr_name', 'Visits.created', 'Batches.grade_id'])
->leftJoinWith('Members')
->leftJoinWith('Members.Batches')
->where(['Batches.batch' => '2020', 'Batches.sub_batch' => '0'])
->order(['Visits.created' => 'DESC'])
);
What’s the code to use in index.php to access the data:
<?= $visit->member_id ?> doesn't seem to work...Notice (8): Trying to get property 'mbrid' of non-object [ROOT/templates/Visits/index.php, line 20] is the error message.
In Debug mode, there is one variable visits and below is the content of one visits row
1 (App\Model\Entity\Visit)
created (Cake\I18n\FrozenTime)
_matchingData (array)
Members (array)
mbrid 250
mbr_name John Doe
Batches (array)
grade_id gr7
I thought _matchingData was only present when you use the ->matching() call in your query. Couldn’t tell you why it’s showing up here.
The SQL generated is exactly what I needed:
SELECT
Members.mbrid AS Members__mbrid,
Members.mbr_name AS Members__mbr_name,
Visits.created AS Visits__created,
Batches.grade_id AS Batches__grade_id
FROM
visits Visits
LEFT JOIN members Members ON Members.mbrid = (Visits.member_id)
LEFT JOIN batches Batches ON Members.id = (Batches.member_id)
WHERE
(
Batches.batch = '2020'
AND Batches.sub_batch = '0'
)
ORDER BY
Visits.created DESC
I run this query in phpmyadmin and I got the result I need. The problem is I don’t know how to access them…
<?= $visit->created->format(DATE_RFC850) ?> this code is the only code that displays the correct data. The others gave me the error message.
Trying to get property 'xx' of non-object—
Looking at the structure of the $visit variable you showed, it would seem that $visit->_matchingData['Members']['mbrid'] would work. I have no idea why it’s coming in that structure.