I am working on a project where in each table there is a deleted
column.
For example, I have User.deleted, Group.deleted.
The below is written by cake incorrectly (Unless I modeled something
wrong, but I must say I used the bake script to make the models,
controllers, and views then added the conditions myself). What that
statement is saying if Group.deleted <> Y AND User.deleted <> Y (in
this case Group.deleted is set to Y) then don't show the user, this is
an incorrect assumption.
Is there a way to correct this problem? If so, how to go about it?
Below is my model, and User/index produces the following SQL (core.php
debug is 2)statement:
SELECT `User`.`id`, `User`.`lastname`, `User`.`firstname`,
`User`.`username`, `User`.`password`, `User`.`group_id`,
`User`.`deleted`, `User`.`emailAddress`, `Group`.`id`, `Group`.`name`,
`Group`.`deleted` FROM `users` AS `User` LEFT JOIN `groups` AS `Group`
ON `User`.`group_id` = `Group`.`id` WHERE (
ucase(ifnull(User.deleted,"N"))<>"Y") AND (
ucase(ifnull(Group.deleted,"N"))<>"Y")
IMO the statement should have been written as (This way User still
shows up, but has Group information as nulls since their group has been
marked as deleted):
SELECT `User`.`id`, `User`.`lastname`, `User`.`firstname`,
`User`.`username`, `User`.`password`, `User`.`group_id`,
`User`.`deleted`, `User`.`emailAddress`, `Group`.`id`, `Group`.`name`,
`Group`.`deleted` FROM `users` AS `User` LEFT JOIN `groups` AS `Group`
ON `User`.`group_id` = `Group`.`id` AND (
ucase(ifnull(Group.deleted,"N"))<>"Y") WHERE (
ucase(ifnull(User.deleted,"N"))<>"Y")
<?php
class User extends AppModel
{
var $name = 'User';
//The Associations below have been created with all possible keys,
those that are not needed can be removed
var $belongsTo = array(
'Group' =>
array('className' => 'Group',
'conditions' =>
'ucase(ifnull(Group.deleted,"N"))<>"Y"',
'order' => '',
'foreignKey' => 'group_id',
'counterCache' => ''),
);
var $hasMany = array(
'MileageClaim' =>
array('className' => 'MileageClaim',
'conditions' =>
'ucase(ifnull(MileageClaim.deleted,"N"))<>"Y"',
'order' => '',
'foreignKey' => 'user_id',
'dependent' => '',
'exclusive' => '',
'finderSql' => '',
'counterSql' => ''),
);
var $hasAndBelongsToMany = array(
'Department' =>
array('className' => 'Department',
'conditions' =>
'ucase(ifnull(deleted,"N"))<>"Y"',
'order' => '',
'foreignKey' => 'user_id',
'joinTable' => 'users_departments',
'associationForeignKey' =>
'department_id',
'uniq' => '',
'finderQuery' => '',
'deleteQuery' => '',
'insertQuery' => ''),
);
}
?>
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups "Cake
PHP" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at http://groups.google.com/group/cake-php
-~----------~----~----~----~------~----~------~--~---
