Running SELECT Statements - differences between CakePHP2 and later (RESOLVED)

Need Help
1

Back in CakePHP 2 I had a rather complicated SELECT I would run that pulled in data from several tables on certain conditions - bit complex to explain the reason why but it worked.

The data returned would be in a nice multi-dimensional array like

array(
	(int) 0 => array(
		'Member' => array(
			'id' => (int) 2169,
			'membership_number' => 'A12345',
			'gender' => (int) 2,
			'date_of_joining' => '2014-08-13',
			'full_name' => 'DOE, JANE',
		),
		'CurrentMemberStatus' => array(
			'id' => (int) 2,
			'name' => 'Associate'
		),
		'CurrentMemberUnit' => array(
			'id' => (int) 33,
			'name' => 'Berwick',
			'lft' => (int) 118,
			'rght' => (int) 119
		)
	),

I’m trying to replicate in CakePHP4 but the same SELECT statement is just returning a flat array etc

(int) 0 => [
   'id' => (int) 2169,
   'membership_number' => 'A12345',
   'gender' => (int) 2,
   'date_of_joining' => '2014-08-13',
   'full_name' => 'DOE, JANE',

Is this just differences between the versions? Nothing I can do to return the same structure as CakePHP2?

Thanks in advance :slight_smile:

Regards,
Brian

2

You’ll need to show us your code if you want help changing this. The very fact that it’s coming back as an array means you’re doing something different from the standard.

3

Hi Zuluru

Thanks for your help. Due to the complexity of the SELECT - needing things like limits on CONTAIN data in many related records etc, in CakePHP2 it had been a SQL SELECT which wasn’t pretty but worked.

		$members = $db->fetchAll(
			'SELECT Member.id,
			Member.membership_number,
            Member.gender,
			Member.date_of_joining,
			Member.member_age_group_id,
			MemberAgeGroup.id,
			MemberAgeGroup.name,	
			CurrentMemberStatus.id,
			CurrentMemberStatus.name,		
etc

When I tried to replicate in CakePHP4, it flattened everything into a single array, and several fields in different tables with the same field name (id, name and so on) were missing.

In the end I’ve just modified the SELECT statement to give aliases to the fields as a work around and change the code that references the array. Gives the same result in the end

        $connection = ConnectionManager::get('default');

        $members = $connection
            ->execute('SELECT
            Member.id as Member__id,
            Member.membership_number as Member__membership_number,
            Member.gender as Member__gender,
            Member.date_of_birth as Member__date_of_birth,
            MemberAgeGroup.id as MemberAgeGroup__id,
            MemberAgeGroup.name as MemberAgeGroup__name,
            CurrentMemberStatus.id as CurrentMemberStatus__id,
            CurrentMemberStatus.name as CurrentMemberStatus__name,
etc
4

I’m confused, are you not using Model associations? In that case, it would be easy to retrieve associated data when those are setup.

For example, for the above, a basic index() action in the Controller could be something like…

        $this->paginate = [
            'contain' => ['MemberAgeGroup','CurrentMemberStatus'],
            'sortableFields' => ['full_name','membership_number','gender','date_of_birth','MemberAgeGroup.name','CurrentMemberStatus.name'],
            'order' => ['CurrentMemberStatus.name'=>'asc','full_name'=>'asc'],
        ];
        
        $query = $this->Member->find('all');
        $members = $this->paginate($query);

Granted, for more complex use cases, e.g. when passing conditions to contain, things can get tricky. But I’m not seeing that with the small snipped you’ve shared.

1 Like
5

Hi Seagrinch

I hadn’t included the full SELECT statement as it’s quite large - I trimmed it down for brevity and I’m sorry that wasn’t made clear.

The select is quite complex with multiple associations, limiting on numerous fields and doesn’t work in model associations.The site is a membership database for a youth group similar to Scouts or Guides, and we track when people join and leave, and which local group they belong to. Many members will change groups as they move around.

So a person might join 10 FEB 2019 in group “A”. They move to another town and move to group “B” on 1 JULY 2020. They leave 1 DEC 2020 when they move out of town. Then 4 MAR 2022 they move again and rejoin in group “C”.

We track all these changes against the member’s record and it’s easy to see the current member status and group they’re a part of - this is used throughout most of the site.

Where it becomes fun is we have some reporting needs on historic data which might be “show me all members of group “C” on a certain date, and by their age on that date”. The retrieval on this is quite complex with 5 joins and lots of conditions and after a lot of work we found the easiest and fastest way was via a SQL select, now moving to CakePHP 4 we needed to replicate the output.

Hope the reasoning there made sense.

6

Ah, that makes sense. I’ve been working on an app with a similar “People with multiple roles over time” requirement, so I can appreciate the struggle.

The CakePHP 4.x associations have come a long way, but I have found that using contain is not trivial, especially when you want to apply search/order/limit restrictions on them. That said, it might also be possible to break the query up into a few steps - but that all depends on the requirements of the report and the power of your server :wink:

1 Like
7

Yeah - I had looked at this plugin but there was a requirement for MySQL 8