Re: Recursive Count ?
OK, I finally made it with this 'join' parameter, that works... but that is not much better than using -query ;-) A little bit better at least... I still think there is a bug because I went to the IRC Cake group yesterday evening and discussed the problem, and someone told me that I should set up the backwards relations with HasMany to ensure Model linking... I did so, and it did not change a thing, Cake still thought it would not have to JOIN the deeper models because I was not calling any of their fields. So, Cake is ignoring the fields used in 'conditions' for determing if a Model is needed or not. Thats the bug. When I was doing a normal 'find' without 'fields' I got the correct JOINs and the whole data... but using that COUNT thing messed it up. In the end its probably so that most people will say: If you want to do such a complicated query, just use query, thats what its for... ;-) I just don't like to capitulate ;-) Thanks all. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups CakePHP group. To post to this group, send email to cake-php@googlegroups.com To unsubscribe from this group, send email to cake-php+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/cake-php?hl=en -~--~~~~--~~--~--~---
Re: Recursive Count ?
Found out the reason for my problems by now: When I am telling cake in the 'fields' parameter that I want only something from ModelA or ModelB, then cake only JOINs as far as necessary in his eyes. It does not care about the conditions which would need the deeper JOINs. So, how do I tell cake that it should do the full joins even if I do not request any of the fields of ModelC or ModelD? Is there some nice forceJoin parameter or something? ;-) Thanks --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups CakePHP group. To post to this group, send email to cake-php@googlegroups.com To unsubscribe from this group, send email to cake-php+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/cake-php?hl=en -~--~~~~--~~--~--~---
Re: Recursive Count ?
So, how do I tell cake that it should do the full joins even if I do not request any of the fields of ModelC or ModelD? Is there some nice forceJoin parameter or something? ;-) Thanks there is a join-key you can use for find()s (and to put into your paginate-array if you want to use pagination).. it works like: 'joins' = array( array( 'table' = 'tableName', 'alias' = 'ModelAlias', 'type' = 'type of join (inner, outer, left, right)', 'conditions' = array(conditions-array for the conditions in JOIN ON(.)) ), array( // next joined model ) ) --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups CakePHP group. To post to this group, send email to cake-php@googlegroups.com To unsubscribe from this group, send email to cake-php+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/cake-php?hl=en -~--~~~~--~~--~--~---
Re: Recursive Count ?
OK, here they are: class ModelA extends AppModel { var $name = 'ModelA'; var $useTable = 'ModelA'; var $primaryKey = 'id'; var $belongsTo = array( 'ModelB' = array('className' = 'ModelB', 'foreignKey' = 'modelb_id') ); } class ModelB extends AppModel { var $name = 'ModelB'; var $useTable = 'ModelB'; var $primaryKey = 'id'; var $belongsTo = array( 'ModelC' = array('className' = 'ModelC', 'foreignKey' = 'modelc_id') ); } class ModelC extends AppModel { var $name = 'ModelC'; var $useTable = 'ModelC'; var $primaryKey = 'id'; var $belongsTo = array( 'ModelD' = array('className' = 'ModelD', 'foreignKey' = 'modeld_id') ); } class ModelD extends AppModel { var $name = 'ModelD'; var $useTable = 'ModelD'; var $primaryKey = 'id'; } --- Of course I replaced our real names by the example names 'ModelX...' but thats just string replacement ;-) - The query I was trying now looked like this: $testcount = $this-ModelA-find( 'first', array( 'fields'='COUNT(DISTINCT ModelA.modelb_id)', 'recursive'=3, 'conditions'=array( 'ModelA.thinga'=41, 'ModelA.thingb'='Y', 'ModelA.thingc'='33', 'ModelC.thingd'='8880'))); And I got back the db error that the column ModelC.thingd would not exist which came from the missing JOIN after ModelB... only ModelB was joined, nothing of ModelC was there in the query which I got in debug mode by Cake. Hope that helps ;-) --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups CakePHP group. To post to this group, send email to cake-php@googlegroups.com To unsubscribe from this group, send email to cake-php+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/cake-php?hl=en -~--~~~~--~~--~--~---
Recursive Count ?
Hi together, I have problems to create a count query with recursive conditions... To clear up things, I ll try to make an example: We have 4 tables: ModelA, ModelB, ModelC, ModelD They are connected like this: - ModelA contains a FK to ModelB. - ModelB contains a FK to ModelC. - ModelC contains a FK to ModelD. And I want to make a distinct count of the PK of ModelB, combined with some conditions spreaded over the tables. This would look like the following SQL: SELECT COUNT (DISTINCT ModelA.modelb_id) FROM ModelA INNER JOIN ModelB ON ModelB.id = ModelA.modelb_id INNER JOIN ModelC ON ModelC.id = ModelB.modelc_id INNER JOIN ModelD ON ModelD.id = ModelC.modeld_id WHERE ModelD.something = 'somevalue' AND ModelA.someother = 'thatvalue' AND ModelB.somethird = 'thosevalues'; So, how can I translate that into Cake syntax? ;-) How would I need to link the models together and how do I need to form the count? I've tried a lot of things in quiet a lot of hours and googled around for days but was not able to find a working solution. So this is my last hope.. *crossing fingers* Thanks in advance! --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups CakePHP group. To post to this group, send email to cake-php@googlegroups.com To unsubscribe from this group, send email to cake-php+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/cake-php?hl=en -~--~~~~--~~--~--~---
Re: Recursive Count ?
On Mar 6, 5:13 pm, weckmann weckm...@bluewin.ch wrote: How would I need to link the models together and how do I need to form the count? I would suggest taking a look here about associating models: http://book.cakephp.org/view/78/Associations-Linking-Models-Together The count can be specified in the 'fields' array of a $this-Model- find call. However, you may run into the issue where the calculated value is stored in results as the 0th index of the model you're counting on (no pun intended). I've tried a lot of things in quiet a lot of hours and googled around for days but was not able to find a working solution. So this is my last hope.. *crossing fingers* Post here what you've tried. Have you already read through the Cake manual (http://book.cakephp.org) thoroughly? --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups CakePHP group. To post to this group, send email to cake-php@googlegroups.com To unsubscribe from this group, send email to cake-php+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/cake-php?hl=en -~--~~~~--~~--~--~---
Re: Recursive Count ?
I am already working a lot with linking models together, but not in that recursive way, mostly only with one belongsTo relation. But your hint specifing the count in the 'fields' array was a good step in the right direction, I finally got the correct SELECT statement created by cake ;-) The other thing is the JOINS... they do not work correctly yet... Now here is what I was doing fist: Since I am calling the ModelA, I thought it would be enough to define the following model linkings: On ModelA: belongsTo ModelB On ModelB: belongsTo ModelC On ModelC: belongsTo ModelD Thats it. Unfortunately it stops joining tables after ModelB... so ModelB gets correctly joined, but ModelC is ignored, even if I set the 'recursive' parameter to 3... as far as I understood the whole thing, this should find the link in ModelB to ModelC... but it does not. Do I have to use the backwards links with hasMany from ModelD to ModelC, ModelC to ModelB, ModelB to ModelA to get this working? Or do I have to set up some kind of link in ModelA directly to ModelC and ModelD? Thanks. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups CakePHP group. To post to this group, send email to cake-php@googlegroups.com To unsubscribe from this group, send email to cake-php+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/cake-php?hl=en -~--~~~~--~~--~--~---
Re: Recursive Count ?
On Mar 7, 2:13 am, weckmann weckm...@bluewin.ch wrote: Unfortunately it stops joining tables after ModelB... so ModelB gets correctly joined, but ModelC is ignored, even if I set the 'recursive' parameter to 3... as far as I understood the whole thing, this should find the link in ModelB to ModelC... but it does not. Post your models. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups CakePHP group. To post to this group, send email to cake-php@googlegroups.com To unsubscribe from this group, send email to cake-php+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/cake-php?hl=en -~--~~~~--~~--~--~---