Thx for concern ..here are the details.
1. User >hasMany >Balance(it's a view)
2. balance view sql : select `transactions`.`user_id` AS
`user_id`,sum(`transactions`.`amount`) AS `total` from `transactions`
group by `transactions`.`user_id`
3. Relations
user.php
~~~~~~~~~~~~~~~~~~
<?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',
'foreignKey' =>
'group_id',
'conditions' =>
'',
'fields' => '',
'order' => '',
'counterCache'
=> ''),
);
var $hasOne = array(
'Balance' => array('className' => 'Balance',
'foreignKey' =>
'user_id',
'conditions' =>
'',
'fields' => '',
'order' => '',
'dependent' =>
''),
);
var $hasMany = array(
'Account' => array('className' => 'Account',
'foreignKey' =>
'user_id',
'conditions' =>
'',
'fields' => '',
'order' => '',
'limit' => '',
'offset' => '',
'dependent' =>
'',
'exclusive' =>
'',
'finderQuery'
=> '',
'counterQuery'
=> ''),
'Transaction' => array('className' => 'Transaction',
'foreignKey' =>
'user_id',
'conditions' =>
'',
'fields' => '',
'order' =>
'date desc',
'limit' => '10',
'offset' => '',
'dependent' =>
'',
'exclusive' =>
'',
'finderQuery'
=> '',
'counterQuery'
=> ''),
);
var $hasAndBelongsToMany = array(
'Jointaccount' => array('className' => 'Jointaccount',
'joinTable' =>
'jointaccounts_users',
'foreignKey' => 'user_id',
'associationForeignKey' =>
'jointaccount_id',
'conditions' => '',
'fields' => '',
'order' => '',
'limit' => '',
'offset' => '',
'unique' => '',
'finderQuery' => '',
'deleteQuery' => '',
'insertQuery' => ''),
);
}
?>
Balance.php
~~~~~~~~~~~~~~~~~~~~~~~~
<?php
class Balance extends AppModel {
var $name = 'Balance';
}
?>
transaction.php
~~~~~~~~~~~~~~~~~~~
<?php
class Transaction extends AppModel {
var $name = 'Transaction';
//The Associations below have been created with all possible keys,
those that are not needed can be removed
var $belongsTo = array(
'User' => array('className' => 'User',
'foreignKey' =>
'user_id',
'conditions' =>
'',
'fields' => '',
'order' => '',
'counterCache'
=> ''),
'Account' => array('className' => 'Account',
'foreignKey' =>
'account_id',
'conditions' =>
'',
'fields' => '',
'order' => '',
'counterCache'
=> ''),
'Type' => array('className' => 'Type',
'foreignKey' =>
'type_id',
'conditions' =>
'',
'fields' => '',
'order' => '',
'counterCache'
=> ''),
'Jointaccount' => array('className' => 'Jointaccount',
'foreignKey' =>
'jointaccount_id',
'conditions' =>
'',
'fields' => '',
'order' => '',
'counterCache'
=> ''),
);
}
?>
4. sql executed : (recursion set 2, so it will have lot more queries)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
60 queries took 18 ms Nr Query Error Affected Num. rows
Took (ms)
1 DESC `users` 8 8 2
2 DESC `groups` 4 4 3
3 DESC `balances` 2 2 3
4 DESC `accounts` 6 6 2
5 DESC `transactions` 10 10 2
6 DESC `types` 4 4 2
7 DESC `jointaccounts` 4 4 2
8 SELECT `User`.`id`, `User`.`created`, `User`.`modified`,
`User`.`username`, `User`.`password`, `User`.`name`,
`User`.`group_id`, `User`.`active`, `Group`.`id`, `Group`.`name`,
`Group`.`created`, `Group`.`modified`, `Balance`.`user_id`,
`Balance`.`total` FROM `users` AS `User` LEFT JOIN `groups` AS `Group`
ON (`User`.`group_id` = `Group`.`id`) LEFT JOIN `balances` AS
`Balance` ON (`Balance`.`user_id` = `User`.`id` AND `User`.`id` = 1)
WHERE `User`.`id` = 1 LIMIT 1 1 1 1
9 SELECT `Group`.`id`, `Group`.`name`, `Group`.`created`,
`Group`.`modified` FROM `groups` AS `Group` WHERE `Group`.`id` = 1
1
1 0
10 SELECT `User`.`id`, `User`.`created`, `User`.`modified`,
`User`.`username`, `User`.`password`, `User`.`name`,
`User`.`group_id`, `User`.`active` FROM `users` AS `User` WHERE
`User`.`group_id` = 1 2 2 0
11 SELECT `Balance`.`user_id`, `Balance`.`total` FROM `balances` AS
`Balance` WHERE `Balance`.`user_id` = 1 1 1 1
12 SELECT `Account`.`id`, `Account`.`created`, `Account`.`modified`,
`Account`.`user_id`, `Account`.`name`, `Account`.`opening_balance`
FROM `accounts` AS `Account` WHERE `Account`.`user_id` = 1 2
2 0
13 SELECT `User`.`id`, `User`.`created`, `User`.`modified`,
`User`.`username`, `User`.`password`, `User`.`name`,
`User`.`group_id`, `User`.`active` FROM `users` AS `User` WHERE
`User`.`id` = 1 1 1 0
14 SELECT `User`.`id`, `User`.`created`, `User`.`modified`,
`User`.`username`, `User`.`password`, `User`.`name`,
`User`.`group_id`, `User`.`active` FROM `users` AS `User` WHERE
`User`.`id` = 1 1 1 0
15 SELECT `Transaction`.`id`, `Transaction`.`created`,
`Transaction`.`modified`, `Transaction`.`date`, `Transaction`.`name`,
`Transaction`.`amount`, `Transaction`.`user_id`,
`Transaction`.`account_id`, `Transaction`.`type_id`,
`Transaction`.`jointaccount_id` FROM `transactions` AS `Transaction`
WHERE `Transaction`.`account_id` = 1 28 28 0
16 SELECT `Transaction`.`id`, `Transaction`.`created`,
`Transaction`.`modified`, `Transaction`.`date`, `Transaction`.`name`,
`Transaction`.`amount`, `Transaction`.`user_id`,
`Transaction`.`account_id`, `Transaction`.`type_id`,
`Transaction`.`jointaccount_id` FROM `transactions` AS `Transaction`
WHERE `Transaction`.`account_id` = 5 4 4 0
17 SELECT `Transaction`.`id`, `Transaction`.`created`,
`Transaction`.`modified`, `Transaction`.`date`, `Transaction`.`name`,
`Transaction`.`amount`, `Transaction`.`user_id`,
`Transaction`.`account_id`, `Transaction`.`type_id`,
`Transaction`.`jointaccount_id` FROM `transactions` AS `Transaction`
WHERE `Transaction`.`user_id` = 1 ORDER BY date desc LIMIT 10 10
10 0
18 SELECT `User`.`id`, `User`.`created`, `User`.`modified`,
`User`.`username`, `User`.`password`, `User`.`name`,
`User`.`group_id`, `User`.`active` FROM `users` AS `User` WHERE
`User`.`id` = 1 1 1 0
19 SELECT `User`.`id`, `User`.`created`, `User`.`modified`,
`User`.`username`, `User`.`password`, `User`.`name`,
`User`.`group_id`, `User`.`active` FROM `users` AS `User` WHERE
`User`.`id` = 1 1 1 0
20 SELECT `User`.`id`, `User`.`created`, `User`.`modified`,
`User`.`username`, `User`.`password`, `User`.`name`,
`User`.`group_id`, `User`.`active` FROM `users` AS `User` WHERE
`User`.`id` = 1 1 1 0
21 SELECT `User`.`id`, `User`.`created`, `User`.`modified`,
`User`.`username`, `User`.`password`, `User`.`name`,
`User`.`group_id`, `User`.`active` FROM `users` AS `User` WHERE
`User`.`id` = 1 1 1 0
22 SELECT `User`.`id`, `User`.`created`, `User`.`modified`,
`User`.`username`, `User`.`password`, `User`.`name`,
`User`.`group_id`, `User`.`active` FROM `users` AS `User` WHERE
`User`.`id` = 1 1 1 0
23 SELECT `User`.`id`, `User`.`created`, `User`.`modified`,
`User`.`username`, `User`.`password`, `User`.`name`,
`User`.`group_id`, `User`.`active` FROM `users` AS `User` WHERE
`User`.`id` = 1 1 1 0
24 SELECT `User`.`id`, `User`.`created`, `User`.`modified`,
`User`.`username`, `User`.`password`, `User`.`name`,
`User`.`group_id`, `User`.`active` FROM `users` AS `User` WHERE
`User`.`id` = 1 1 1 0
25 SELECT `User`.`id`, `User`.`created`, `User`.`modified`,
`User`.`username`, `User`.`password`, `User`.`name`,
`User`.`group_id`, `User`.`active` FROM `users` AS `User` WHERE
`User`.`id` = 1 1 1 0
26 SELECT `User`.`id`, `User`.`created`, `User`.`modified`,
`User`.`username`, `User`.`password`, `User`.`name`,
`User`.`group_id`, `User`.`active` FROM `users` AS `User` WHERE
`User`.`id` = 1 1 1 0
27 SELECT `User`.`id`, `User`.`created`, `User`.`modified`,
`User`.`username`, `User`.`password`, `User`.`name`,
`User`.`group_id`, `User`.`active` FROM `users` AS `User` WHERE
`User`.`id` = 1 1 1 0
28 SELECT `Account`.`id`, `Account`.`created`, `Account`.`modified`,
`Account`.`user_id`, `Account`.`name`, `Account`.`opening_balance`
FROM `accounts` AS `Account` WHERE `Account`.`id` = 5 1 1 0
29 SELECT `Account`.`id`, `Account`.`created`, `Account`.`modified`,
`Account`.`user_id`, `Account`.`name`, `Account`.`opening_balance`
FROM `accounts` AS `Account` WHERE `Account`.`id` = 1 1 1 0
30 SELECT `Account`.`id`, `Account`.`created`, `Account`.`modified`,
`Account`.`user_id`, `Account`.`name`, `Account`.`opening_balance`
FROM `accounts` AS `Account` WHERE `Account`.`id` = 1 1 1 0
31 SELECT `Account`.`id`, `Account`.`created`, `Account`.`modified`,
`Account`.`user_id`, `Account`.`name`, `Account`.`opening_balance`
FROM `accounts` AS `Account` WHERE `Account`.`id` = 1 1 1 0
32 SELECT `Account`.`id`, `Account`.`created`, `Account`.`modified`,
`Account`.`user_id`, `Account`.`name`, `Account`.`opening_balance`
FROM `accounts` AS `Account` WHERE `Account`.`id` = 1 1 1 0
33 SELECT `Account`.`id`, `Account`.`created`, `Account`.`modified`,
`Account`.`user_id`, `Account`.`name`, `Account`.`opening_balance`
FROM `accounts` AS `Account` WHERE `Account`.`id` = 1 1 1 0
34 SELECT `Account`.`id`, `Account`.`created`, `Account`.`modified`,
`Account`.`user_id`, `Account`.`name`, `Account`.`opening_balance`
FROM `accounts` AS `Account` WHERE `Account`.`id` = 1 1 1 0
35 SELECT `Account`.`id`, `Account`.`created`, `Account`.`modified`,
`Account`.`user_id`, `Account`.`name`, `Account`.`opening_balance`
FROM `accounts` AS `Account` WHERE `Account`.`id` = 5 1 1 0
36 SELECT `Account`.`id`, `Account`.`created`, `Account`.`modified`,
`Account`.`user_id`, `Account`.`name`, `Account`.`opening_balance`
FROM `accounts` AS `Account` WHERE `Account`.`id` = 5 1 1 0
37 SELECT `Account`.`id`, `Account`.`created`, `Account`.`modified`,
`Account`.`user_id`, `Account`.`name`, `Account`.`opening_balance`
FROM `accounts` AS `Account` WHERE `Account`.`id` = 1 1 1 0
38 SELECT `Type`.`id`, `Type`.`created`, `Type`.`modified`,
`Type`.`name` FROM `types` AS `Type` WHERE `Type`.`id` = 1 1
1 0
39 SELECT `Type`.`id`, `Type`.`created`, `Type`.`modified`,
`Type`.`name` FROM `types` AS `Type` WHERE `Type`.`id` = 1 1
1 0
40 SELECT `Type`.`id`, `Type`.`created`, `Type`.`modified`,
`Type`.`name` FROM `types` AS `Type` WHERE `Type`.`id` = 1 1
1 0
41 SELECT `Type`.`id`, `Type`.`created`, `Type`.`modified`,
`Type`.`name` FROM `types` AS `Type` WHERE `Type`.`id` = 1 1
1 0
42 SELECT `Type`.`id`, `Type`.`created`, `Type`.`modified`,
`Type`.`name` FROM `types` AS `Type` WHERE `Type`.`id` = 3 1
1 0
43 SELECT `Type`.`id`, `Type`.`created`, `Type`.`modified`,
`Type`.`name` FROM `types` AS `Type` WHERE `Type`.`id` = 1 1
1 0
44 SELECT `Type`.`id`, `Type`.`created`, `Type`.`modified`,
`Type`.`name` FROM `types` AS `Type` WHERE `Type`.`id` = 1 1
1 0
45 SELECT `Type`.`id`, `Type`.`created`, `Type`.`modified`,
`Type`.`name` FROM `types` AS `Type` WHERE `Type`.`id` = 1 1
1 0
46 SELECT `Type`.`id`, `Type`.`created`, `Type`.`modified`,
`Type`.`name` FROM `types` AS `Type` WHERE `Type`.`id` = 1 1
1 0
47 SELECT `Type`.`id`, `Type`.`created`, `Type`.`modified`,
`Type`.`name` FROM `types` AS `Type` WHERE `Type`.`id` = 1 1
1 0
48 SELECT `Jointaccount`.`id`, `Jointaccount`.`created`,
`Jointaccount`.`modified`, `Jointaccount`.`name` FROM `jointaccounts`
AS `Jointaccount` WHERE `Jointaccount`.`id` = 1 1 1 0
49 SELECT `Jointaccount`.`id`, `Jointaccount`.`created`,
`Jointaccount`.`modified`, `Jointaccount`.`name` FROM `jointaccounts`
AS `Jointaccount` WHERE `Jointaccount`.`id` = 0 1 1 0
50 SELECT `Jointaccount`.`id`, `Jointaccount`.`created`,
`Jointaccount`.`modified`, `Jointaccount`.`name` FROM `jointaccounts`
AS `Jointaccount` WHERE `Jointaccount`.`id` = 0 1 1 0
51 SELECT `Jointaccount`.`id`, `Jointaccount`.`created`,
`Jointaccount`.`modified`, `Jointaccount`.`name` FROM `jointaccounts`
AS `Jointaccount` WHERE `Jointaccount`.`id` = 1 1 1 0
52 SELECT `Jointaccount`.`id`, `Jointaccount`.`created`,
`Jointaccount`.`modified`, `Jointaccount`.`name` FROM `jointaccounts`
AS `Jointaccount` WHERE `Jointaccount`.`id` = 0 1 1 0
53 SELECT `Jointaccount`.`id`, `Jointaccount`.`created`,
`Jointaccount`.`modified`, `Jointaccount`.`name` FROM `jointaccounts`
AS `Jointaccount` WHERE `Jointaccount`.`id` = 1 1 1 0
54 SELECT `Jointaccount`.`id`, `Jointaccount`.`created`,
`Jointaccount`.`modified`, `Jointaccount`.`name` FROM `jointaccounts`
AS `Jointaccount` WHERE `Jointaccount`.`id` = 1 1 1 0
55 SELECT `Jointaccount`.`id`, `Jointaccount`.`created`,
`Jointaccount`.`modified`, `Jointaccount`.`name` FROM `jointaccounts`
AS `Jointaccount` WHERE `Jointaccount`.`id` = 1 1 1 0
56 SELECT `Jointaccount`.`id`, `Jointaccount`.`created`,
`Jointaccount`.`modified`, `Jointaccount`.`name` FROM `jointaccounts`
AS `Jointaccount` WHERE `Jointaccount`.`id` = 1 1 1 0
57 SELECT `Jointaccount`.`id`, `Jointaccount`.`created`,
`Jointaccount`.`modified`, `Jointaccount`.`name` FROM `jointaccounts`
AS `Jointaccount` WHERE `Jointaccount`.`id` = 1 1 1 0
58 SELECT `Jointaccount`.`id`, `Jointaccount`.`created`,
`Jointaccount`.`modified`, `Jointaccount`.`name` FROM `jointaccounts`
AS `Jointaccount` JOIN `jointaccounts_users` ON
(`jointaccounts_users`.`user_id` = 1 AND
`jointaccounts_users`.`jointaccount_id` = `Jointaccount`.`id`) WHERE 1
= 1 1 1 0
59 SELECT `Transaction`.`id`, `Transaction`.`created`,
`Transaction`.`modified`, `Transaction`.`date`, `Transaction`.`name`,
`Transaction`.`amount`, `Transaction`.`user_id`,
`Transaction`.`account_id`, `Transaction`.`type_id`,
`Transaction`.`jointaccount_id` FROM `transactions` AS `Transaction`
WHERE `Transaction`.`jointaccount_id` = 1 34 34 0
60 SELECT `User`.`id`, `User`.`created`, `User`.`modified`,
`User`.`username`, `User`.`password`, `User`.`name`,
`User`.`group_id`, `User`.`active` FROM `users` AS `User` JOIN
`jointaccounts_users` ON (`jointaccounts_users`.`jointaccount_id` = 1
AND `jointaccounts_users`.`user_id` = `User`.`id`) WHERE 1 = 1 2
2 0
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
--~--~---------~--~----~------------~-------~--~----~
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?hl=en
-~----------~----~----~----~------~----~------~--~---
