CODEWITHSUNDEEP
cakephpdatabase-designcakephp-2.0
wordy
wordy

Reputation: 173

Stuck on how to do model data in the right "Cake way"?

I'm trying to build a tool to help my team plan events. A partial diagram to explain my question:

Diagram

Larger Image

Actions hasMany Teams with different roles (i.e. Lead, Secondary) via a hasManyThrough (actions_teams)

Actions are linked to other actions in a parent/child relationship via a hasManyThrough (linkages)

REQUIRED: I'd like to be able to know, for a given Action.id that has an attached (actions_team) Team(s) in a given role, do those Team(s) have a linkage back to Action.id (i.e. lists Action.id as the parent in linkages)?

I've figured out how to do this in both controllers and even in views, by using loops and/or Containable to be able to highly control the data coming back. However, I will ask this question so frequently, that I'd prefer to somehow implement it in the database itself.

The relation I need would seem to be a join table of 2 join tables? (actions_teams and linkages). Or is the solution to use a counterCache? I'm stuck.

Any suggestions or tips would be greatly appreciated. TIA!

Upvotes: 1

Views: 109

Answers (1)

AgRizzo
AgRizzo

Reputation: 5271

The assumption had to be made that the linkage was only 1 level (i.e., we cannot from a child check beyond its parent. If your given action_id is the grandparent or great-grandparent of the team's action, this will NOT work.) Instead of creating 1 giant SQL statement with (potentially) derived tables, sub-selects, etc., start with a view to establish the "list of potential teams."

Without seeing actual data, create this view to make sure there is a distinct list (you may be able to avoid this view if these three fields comprise an unique index.) 

CREATE VIEW vw_team_listing AS
SELECT DISTINCT team_id, action_id, action_role_id
FROM actions_teams;

{The case statement is because you asked, "do teams have a linkage back?" You could just create a select statement for those teams that DO or DO NOT have a linkage (not both)}

SELECT vw_team_listing.team_id AS team_id,
CASE
WHEN EXISTS(
SELECT *
FROM linkages
JOIN actions_teams
ON linkages.child_action_id = action_teams.action_id
WHERE linkages.parent_id = vw_team_listing.action_id
AND actions_teams.team_id = vw_team_listing.team_id
)
THEN 'Linked'
ELSE 'Not Linked'
END AS answer
FROM vw_team_listing
WHERE vw_team_listing.action_id = 'GIVEN_ACTION_ID'
AND vw_team_listing.action_role_id = 'GIVEN_ACTION_ROLE_ID';

Upvotes: 1

Related Questions