CakePHP

Adding an extra column (sub query) to a model using associations

Classic

List

Threaded

5 messages Options

Maff^

Adding an extra column (sub query) to a model using associations

Hi There,

First of all I want to state that I'm not a very advanced CakePHP
user. I tried to find answers to my question here and with Google, but
I'm probably not using there right keywords.

Okay, here is my situation: I have an Diner model which has 2 hasMany
associations (to Dinersubscription and Dinerpayment). The
Dinersubscriprion model has a field 'count' telling how many servings
a Resident wants. (0 meaning that person won't join diner). The
Dinerpayment model tels who paid for the groceries and adding up all
records for a particular diner gives you the costs for that diner.

Here is a image illustration my situation:
http://bl-tyrone.student.utwente.nl/~Mafioso/cakephp/Diner_structure.gif

diner.php:
<?php
class Diner extends AppModel {

var $name = 'Diner';
var $displayField = 'date';

var $hasMany = array(
'Dinersubscription' => array('className' => 'Dinersubscription',
'foreignKey' => 'diner_id',
'Dinerpayment' => array('className' => 'Dinerpayment', 'foreignKey'
=> 'diner_id'),
);

var $belongsTo = array ('Bill');

}
?>

Using sub queries I can lookup the costs an total count for a diner:
SELECT Diner.*, (SELECT SUM(count) FROM dinersubscriptions WHERE
diner_id = 1717) as totalcount, (SELECT SUM(amount) FROM dinerpayments
WHERE diner_id = 1717) as totalamount FROM diners as Diner WHERE id =
1717;

My question is the following: Is it possible to add these subqueries
to the associations of the Diner model, so every time I look up a
diner (or more diners at once) I retrieve the total count of
Dinersubscriptions and the total amount of Dinerpayments?

I've tried to add hasOne-associations but that didn't work :(

Btw, I'm using CakePHP version 1.2.0.5427alpha. And I've removed the
Bill model association because it's inrelevant.

Greetings, Ruud Bijnen

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups "Cake PHP" group.
To post to this group, send email to [hidden email]
To unsubscribe from this group, send email to [hidden email]
For more options, visit this group at http://groups.google.com/group/cake-php?hl=en
-~----------~----~----~----~------~----~------~--~---

b logica

Re: Adding an extra column (sub query) to a model using associations

Pancakes, yum!

If you haven't already, check out Mariano's article on his Bindable
behavior. I found a good way to play around with it was to put
debug($my_whatever) in the view and to then play around with
restrictions in the controller.

http://bakery.cakephp.org/articles/view/bindable-behavior-control-your-model-bindings

On Sat, Mar 15, 2008 at 3:26 PM, Maff^ <[hidden email]> wrote:

>
> Hi There,
>
> First of all I want to state that I'm not a very advanced CakePHP
> user. I tried to find answers to my question here and with Google, but
> I'm probably not using there right keywords.
>
> Okay, here is my situation: I have an Diner model which has 2 hasMany
> associations (to Dinersubscription and Dinerpayment). The
> Dinersubscriprion model has a field 'count' telling how many servings
> a Resident wants. (0 meaning that person won't join diner). The
> Dinerpayment model tels who paid for the groceries and adding up all
> records for a particular diner gives you the costs for that diner.
>
> Here is a image illustration my situation:
> http://bl-tyrone.student.utwente.nl/~Mafioso/cakephp/Diner_structure.gif
>
> diner.php:
> <?php
> class Diner extends AppModel {
>
> var $name = 'Diner';
> var $displayField = 'date';
>
> var $hasMany = array(
> 'Dinersubscription' => array('className' => 'Dinersubscription',
> 'foreignKey' => 'diner_id',
> 'Dinerpayment' => array('className' => 'Dinerpayment', 'foreignKey'
> => 'diner_id'),
> );
>
> var $belongsTo = array ('Bill');
>
> }
> ?>
>
> Using sub queries I can lookup the costs an total count for a diner:
> SELECT Diner.*, (SELECT SUM(count) FROM dinersubscriptions WHERE
> diner_id = 1717) as totalcount, (SELECT SUM(amount) FROM dinerpayments
> WHERE diner_id = 1717) as totalamount FROM diners as Diner WHERE id =
> 1717;
>
> My question is the following: Is it possible to add these subqueries
> to the associations of the Diner model, so every time I look up a
> diner (or more diners at once) I retrieve the total count of
> Dinersubscriptions and the total amount of Dinerpayments?
>
> I've tried to add hasOne-associations but that didn't work :(
>
>
> Btw, I'm using CakePHP version 1.2.0.5427alpha. And I've removed the
> Bill model association because it's inrelevant.
>
> Greetings, Ruud Bijnen
>
> >
>

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups "Cake PHP" group.
To post to this group, send email to [hidden email]
To unsubscribe from this group, send email to [hidden email]
For more options, visit this group at http://groups.google.com/group/cake-php?hl=en
-~----------~----~----~----~------~----~------~--~---

Maff^

Re: Adding an extra column (sub query) to a model using associations

First of all, thanks for your comment, the Bindable behavior is very
useful! Also, sorry for my very late response, I've been busy with
school and work.

Unfortunately the Bindable behavior doesn't help me with my problem :
(, although it made me think.
I thoughtI found a solution by using a hasOne-association in my Diner
model:

var $hasOne = array (
'Dinertotal' => array (
'className' => 'Diner',
'fields' => array (
'(SELECT SUM(count) FROM `dinersubscriptions` WHERE diner_id =
`Diner`.`id`) as `totalcount`',
'(SELECT SUM(amount) FROM `dinerpayments` WHERE diner_id =
`Diner`.`id`) as `totalamount`'
),
'foreignKey' => 'id',
),
);
This creates a JOIN query which retrieves the fields i want:

"SELECT `Diner`.`id`, `Diner`.`date`, `Diner`.`title`,
`Diner`.`description`, `Diner`.`stats`, `Diner`.`locked`,
`Diner`.`bill_id`, `Diner`.`created`, `Diner`.`modified`, `Bill`.`id`,
`Bill`.`date`, `Bill`.`created`, `Bill`.`modified`, (SELECT SUM(count)
FROM `dinersubscriptions` WHERE diner_id = `Diner`.`id`) as
`totalcount`, (SELECT SUM(amount) FROM `dinerpayments` WHERE diner_id
= `Diner`.`id`) as `totalamount` FROM `diners` AS `Diner` LEFT JOIN
`bills` AS `Bill` ON (`Diner`.`bill_id` = `Bill`.`id`) LEFT JOIN
`diners` AS `Dinertotal` ON (`Dinertotal`.`id` = `Diner`.`id`) WHERE
`Diner`.`id` = 499 LIMIT 1"

Array
(
[Diner] => Array
(
[id] => 1851
[date] => 2008-02-29
[title] =>
[description] =>
[stats] => 1
[locked] => 1
[bill_id] => 11
[created] => 2008-01-18 16:17:24
[modified] => 2008-03-01 23:04:18
)

[Bill] => Array
(
[id] => 11
[date] => 2008-03-20
[created] => 2008-03-16 16:29:18
[modified] => 2008-03-16 16:29:18
)

[0] => Array
(
[totalcount] => 5
[totalamount] => 8.6000003814697
)
)

I was happy to find this working for me, until I did a Bill-
>find('first') (where id = 11)!
It didn't work because cakePHP makes a lot of queries in order to
retrieve the hasOne assoc. (instead of a join):

SELECT `Diner`.`id`, `Diner`.`date`, `Diner`.`title`,
`Diner`.`description`, `Diner`.`stats`, `Diner`.`locked`,
`Diner`.`bill_id`, `Diner`.`created`, `Diner`.`modified` FROM `diners`
AS `Diner` WHERE `Diner`.`bill_id` IN (11)
SELECT (SELECT SUM(count) FROM `dinersubscriptions` WHERE diner_id =
`Diner`.`id`) as `totalcount`, (SELECT SUM(amount) FROM
`dinerpayments` WHERE diner_id = `Diner`.`id`) as `totalamount` FROM
`diners` AS `Dinertotal` WHERE `Dinertotal`.`id` = 1839
SELECT (SELECT SUM(count) FROM `dinersubscriptions` WHERE diner_id =
`Diner`.`id`) as `totalcount`, (SELECT SUM(amount) FROM
`dinerpayments` WHERE diner_id = `Diner`.`id`) as `totalamount` FROM
`diners` AS `Dinertotal` WHERE `Dinertotal`.`id` = 1840
etc...

This of course doesn't work because `Diner`.`id` is unknown.

So my question is: is there a way to force cakePHP to do a join query
instead of all those separate queries (it would also be a lot faster),
or can I some how make cakePHP query some extra fields? (Maybe by
making a behavior so you could define these fields in your Model).

I hope my story is still a little bit understandable, my English is
quite poor :(.

Yours sincerely,
Ruud Bijnen

On Mar 15, 11:34 pm, "b logica" <[hidden email]> wrote:

> Pancakes, yum!
>
> If you haven't already, check out Mariano's article on his Bindable
> behavior. I found a good way to play around with it was to put
> debug($my_whatever) in the view and to then play around with
> restrictions in the controller.
>
> http://bakery.cakephp.org/articles/view/bindable-behavior-control-you...
>
>
>
> On Sat, Mar 15, 2008 at 3:26 PM, Maff^ <[hidden email]> wrote:
>
> > Hi There,
>
> > First of all I want to state that I'm not a very advanced CakePHP
> > user. I tried to find answers to my question here and with Google, but
> > I'm probably not using there right keywords.
>
> > Okay, here is my situation: I have an Diner model which has 2 hasMany
> > associations (to Dinersubscription and Dinerpayment). The
> > Dinersubscriprion model has a field 'count' telling how many servings
> > a Resident wants. (0 meaning that person won't join diner). The
> > Dinerpayment model tels who paid for the groceries and adding up all
> > records for a particular diner gives you the costs for that diner.
>
> > Here is a image illustration my situation:
> > http://bl-tyrone.student.utwente.nl/~Mafioso/cakephp/Diner_structure.gif
>
> > diner.php:
> > <?php
> > class Diner extends AppModel {
>
> > var $name = 'Diner';
> > var $displayField = 'date';
>
> > var $hasMany = array(
> > 'Dinersubscription' => array('className' => 'Dinersubscription',
> > 'foreignKey' => 'diner_id',
> > 'Dinerpayment' => array('className' => 'Dinerpayment', 'foreignKey'
> > => 'diner_id'),
> > );
>
> > var $belongsTo = array ('Bill');
>
> > }
> > ?>
>
> > Using sub queries I can lookup the costs an total count for a diner:
> > SELECT Diner.*, (SELECT SUM(count) FROM dinersubscriptions WHERE
> > diner_id = 1717) as totalcount, (SELECT SUM(amount) FROM dinerpayments
> > WHERE diner_id = 1717) as totalamount FROM diners as Diner WHERE id =
> > 1717;
>
> > My question is the following: Is it possible to add these subqueries
> > to the associations of the Diner model, so every time I look up a
> > diner (or more diners at once) I retrieve the total count of
> > Dinersubscriptions and the total amount of Dinerpayments?
>
> > I've tried to add hasOne-associations but that didn't work :(
>
> > Btw, I'm using CakePHP version 1.2.0.5427alpha. And I've removed the
> > Bill model association because it's inrelevant.
>
> > Greetings, Ruud Bijnen- Hide quoted text -
>
> - Show quoted text -

Chris Hartjes

Re: Adding an extra column (sub query) to a model using associations

In reply to this post by Maff^

On Sat, Mar 15, 2008 at 3:26 PM, Maff^ <[hidden email]> wrote:
>
> Hi There,
>
> First of all I want to state that I'm not a very advanced CakePHP
> user. I tried to find answers to my question here and with Google, but
> I'm probably not using there right keywords.
>

See, if only more people asking questions did their homework like this
person, I would be a lot less grumpy.

--
Chris Hartjes
Internet Loudmouth
Motto for 2008: "Moving from herding elephants to handling snakes..."
@TheKeyBoard: http://www.littlehart.net/atthekeyboard

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups "Cake PHP" group.
To post to this group, send email to [hidden email]
To unsubscribe from this group, send email to [hidden email]
For more options, visit this group at http://groups.google.com/group/cake-php?hl=en
-~----------~----~----~----~------~----~------~--~---

b logica

Re: Adding an extra column (sub query) to a model using associations

In reply to this post by Maff^

Your English is far better than my Dutch (Oranje Boven!)

I wonder if this thread will be helpful to your sutuation as well:

http://groups.google.com/group/cake-php/browse_frm/thread/f7508c11b6f785ff#

On Tue, Apr 1, 2008 at 5:07 PM, Maff^ <[hidden email]> wrote:

>
> First of all, thanks for your comment, the Bindable behavior is very
> useful! Also, sorry for my very late response, I've been busy with
> school and work.
>
> Unfortunately the Bindable behavior doesn't help me with my problem :
> (, although it made me think.
> I thoughtI found a solution by using a hasOne-association in my Diner
> model:
>
> var $hasOne = array (
> 'Dinertotal' => array (
> 'className' => 'Diner',
> 'fields' => array (
> '(SELECT SUM(count) FROM `dinersubscriptions` WHERE diner_id =
> `Diner`.`id`) as `totalcount`',
>
> '(SELECT SUM(amount) FROM `dinerpayments` WHERE diner_id =
> `Diner`.`id`) as `totalamount`'
> ),
> 'foreignKey' => 'id',
> ),
> );
> This creates a JOIN query which retrieves the fields i want:
>
> "SELECT `Diner`.`id`, `Diner`.`date`, `Diner`.`title`,
> `Diner`.`description`, `Diner`.`stats`, `Diner`.`locked`,
> `Diner`.`bill_id`, `Diner`.`created`, `Diner`.`modified`, `Bill`.`id`,
> `Bill`.`date`, `Bill`.`created`, `Bill`.`modified`, (SELECT SUM(count)
> FROM `dinersubscriptions` WHERE diner_id = `Diner`.`id`) as
>
> `totalcount`, (SELECT SUM(amount) FROM `dinerpayments` WHERE diner_id
> = `Diner`.`id`) as `totalamount` FROM `diners` AS `Diner` LEFT JOIN
> `bills` AS `Bill` ON (`Diner`.`bill_id` = `Bill`.`id`) LEFT JOIN
> `diners` AS `Dinertotal` ON (`Dinertotal`.`id` = `Diner`.`id`) WHERE
> `Diner`.`id` = 499 LIMIT 1"
>
> Array
> (
> [Diner] => Array
> (
> [id] => 1851
> [date] => 2008-02-29
> [title] =>
> [description] =>
> [stats] => 1
> [locked] => 1
> [bill_id] => 11
> [created] => 2008-01-18 16:17:24
> [modified] => 2008-03-01 23:04:18
> )
>
> [Bill] => Array
> (
> [id] => 11
> [date] => 2008-03-20
> [created] => 2008-03-16 16:29:18
> [modified] => 2008-03-16 16:29:18
> )
>
> [0] => Array
> (
> [totalcount] => 5
> [totalamount] => 8.6000003814697
> )
> )
>
>
> I was happy to find this working for me, until I did a Bill-
> >find('first') (where id = 11)!
> It didn't work because cakePHP makes a lot of queries in order to
> retrieve the hasOne assoc. (instead of a join):
>
> SELECT `Diner`.`id`, `Diner`.`date`, `Diner`.`title`,
> `Diner`.`description`, `Diner`.`stats`, `Diner`.`locked`,
> `Diner`.`bill_id`, `Diner`.`created`, `Diner`.`modified` FROM `diners`
> AS `Diner` WHERE `Diner`.`bill_id` IN (11)
> SELECT (SELECT SUM(count) FROM `dinersubscriptions` WHERE diner_id =
> `Diner`.`id`) as `totalcount`, (SELECT SUM(amount) FROM
> `dinerpayments` WHERE diner_id = `Diner`.`id`) as `totalamount` FROM
> `diners` AS `Dinertotal` WHERE `Dinertotal`.`id` = 1839
> SELECT (SELECT SUM(count) FROM `dinersubscriptions` WHERE diner_id =
> `Diner`.`id`) as `totalcount`, (SELECT SUM(amount) FROM
> `dinerpayments` WHERE diner_id = `Diner`.`id`) as `totalamount` FROM
> `diners` AS `Dinertotal` WHERE `Dinertotal`.`id` = 1840
> etc...
>
> This of course doesn't work because `Diner`.`id` is unknown.
>
>
> So my question is: is there a way to force cakePHP to do a join query
> instead of all those separate queries (it would also be a lot faster),
> or can I some how make cakePHP query some extra fields? (Maybe by
> making a behavior so you could define these fields in your Model).
>
> I hope my story is still a little bit understandable, my English is
> quite poor :(.
>
> Yours sincerely,
> Ruud Bijnen
>
>
> On Mar 15, 11:34 pm, "b logica" <[hidden email]> wrote:
> > Pancakes, yum!
> >
> > If you haven't already, check out Mariano's article on his Bindable
> > behavior. I found a good way to play around with it was to put
> > debug($my_whatever) in the view and to then play around with
> > restrictions in the controller.
> >
> > http://bakery.cakephp.org/articles/view/bindable-behavior-control-you...
>
>
> >
> >
> >
> > On Sat, Mar 15, 2008 at 3:26 PM, Maff^ <[hidden email]> wrote:
> >
> > > Hi There,
> >
> > > First of all I want to state that I'm not a very advanced CakePHP
> > > user. I tried to find answers to my question here and with Google, but
> > > I'm probably not using there right keywords.
> >
> > > Okay, here is my situation: I have an Diner model which has 2 hasMany
> > > associations (to Dinersubscription and Dinerpayment). The
> > > Dinersubscriprion model has a field 'count' telling how many servings
> > > a Resident wants. (0 meaning that person won't join diner). The
> > > Dinerpayment model tels who paid for the groceries and adding up all
> > > records for a particular diner gives you the costs for that diner.
> >
> > > Here is a image illustration my situation:
> > > http://bl-tyrone.student.utwente.nl/~Mafioso/cakephp/Diner_structure.gif
> >
> > > diner.php:
> > > <?php
> > > class Diner extends AppModel {
> >
> > > var $name = 'Diner';
> > > var $displayField = 'date';
> >
> > > var $hasMany = array(
> > > 'Dinersubscription' => array('className' => 'Dinersubscription',
> > > 'foreignKey' => 'diner_id',
> > > 'Dinerpayment' => array('className' => 'Dinerpayment', 'foreignKey'
> > > => 'diner_id'),
> > > );
> >
> > > var $belongsTo = array ('Bill');
> >
> > > }
> > > ?>
> >
> > > Using sub queries I can lookup the costs an total count for a diner:
> > > SELECT Diner.*, (SELECT SUM(count) FROM dinersubscriptions WHERE
> > > diner_id = 1717) as totalcount, (SELECT SUM(amount) FROM dinerpayments
> > > WHERE diner_id = 1717) as totalamount FROM diners as Diner WHERE id =
> > > 1717;
> >
> > > My question is the following: Is it possible to add these subqueries
> > > to the associations of the Diner model, so every time I look up a
> > > diner (or more diners at once) I retrieve the total count of
> > > Dinersubscriptions and the total amount of Dinerpayments?
> >
> > > I've tried to add hasOne-associations but that didn't work :(
> >
> > > Btw, I'm using CakePHP version 1.2.0.5427alpha. And I've removed the
> > > Bill model association because it's inrelevant.
> >
> > > Greetings, Ruud Bijnen- Hide quoted text -
> >
> > - Show quoted text -
>
>
> >
>