CakePHP Custom Queries and Concatenate

Problem

I recently ran into an issue where I found it much more efficient to write a custom query to gather the data I needed to perform some processing than it was to utilize CakePHP’s model/controller normal data accessing functionality.

At first, I wanted to make sure that all of my related tables gathered all of the data from each other. This created numerous objects filled with data that I may or may not use. It also resulted in the need to loop through every array multiple times to gather the final data required.

So, I decided to make a custom query  in the model that would handle joining all the tables into one array. I ran into trouble where fields were being grouped into their respective models as expected by CakePHP’s model class. This caused a headache to access the data properly again.

Original Custom Query

SELECT
	environments.id,
	environments.name,
	deploy_files.name,
	web_services.name,
	CONCAT(web_services.name,\'.\',users.name,\'=\',users.password) line
FROM
	deploy_files,
	environments,
	users,
	web_services,
	users_web_services,
	users_environments,
	web_services_deploy_files
WHERE
users.id = users_web_services.user_id
AND web_services.id = users_web_services.web_service_id
AND users.id = users_environments.user_id
AND environments.id = users_environments.environment_id
AND web_services.id = web_services_deploy_files.web_service_id
AND deploy_files.id = web_services_deploy_files.deploy_file_id
AND users.active = 1
AND web_services.active = 1
AND deploy_files.active = 1
AND environments.active = 1
ORDER BY
	environments.name,
	deploy_files.name,
	web_services.name,
	users.hidden DESC,
	users.name

Original Output

array (
  0 =>
  array (
    'environments' =>
    array (
      'id' => '1',
      'name' => 'Production',
    ),
    'deploy_files' =>
    array (
      'name' => 'test.login',
    ),
    'web_services' =>
    array (
      'name' => 'WebService',
    ),
    0 =>
    array (
      'line' => 'WebService.security=on',
    ),
  )
)

This output is usable, but I would have preferred to have everything simplified a bit and not grouped into sub associative arrays

Solution

I noticed the results of the CONCAT in the original query were in their own sub-array (0, zero). So, I took every column and put them in their own CONCAT and gave them an alias.

New Custom Query

SELECT
	CONCAT(environments.id) env_id,
	CONCAT(environments.name) env_name,
	CONCAT(deploy_files.name) file_name,
	CONCAT(web_services.name) ws_name,
	CONCAT(web_services.name,\'.\',users.name,\'=\',users.password) line
FROM
	deploy_files,
	environments,
	users,
	web_services,
	users_web_services,
	users_environments,
	web_services_deploy_files
WHERE
users.id = users_web_services.user_id
AND web_services.id = users_web_services.web_service_id
AND users.id = users_environments.user_id
AND environments.id = users_environments.environment_id
AND web_services.id = web_services_deploy_files.web_service_id
AND deploy_files.id = web_services_deploy_files.deploy_file_id
AND users.active = 1
AND web_services.active = 1
AND deploy_files.active = 1
AND environments.active = 1
ORDER BY
	environments.name,
	deploy_files.name,
	web_services.name,
	users.hidden DESC,
	users.name

New Output

array (
  0 =>
  array (
    0 =>
    array (
      'env_id' => '1',
      'env_name' => 'Production',
      'file_name' => 'test.login',
      'ws_name' => 'WebService',
      'line' => 'WebService.security=on',
    ),
  )
)

Now everything is inside the single 0 sub-array. The biggest problem I see with this is that I have concocted a custom query that breaks any CakePHP naming conventions. My controller and other data consuming components now rely on KNOWING these specific columns from this query. However, it is a large query that generates a lot more data than has been shown in these examples. Therefore, I think it is more efficient and memory-saving than the CakePHP natural methods or even a basic custom query like the original custom query above.