Managed to solve it with something like this in some cases.
/**
* Find a list of all the regions and how many hotels are in each region
*
* @return array
*/
public function regionWithHotelCount() {
return $this->find('all', [
'link' => [
'CountriesRegion' => [
'fields' => ['id', 'country_id', 'region_id'],
'Country' => [
'fields' => ['id'],
'State' => [
'fields' => ['id'],
'City' => [
'fields' => ['id'],
'Hotel' => [
'fields' => ['id'],
]
]
]
]
]
],
'fields' => ['Region.slug', 'CountriesRegion.region_id',
'Region.name', 'COUNT(Hotel.id) as NumHotels'],
'group' => 'CountriesRegion.region_id'
]);
}
On Tuesday, 22 July 2014 11:57:59 UTC+1, David Yell wrote:
>
> I wasn't aware that Linkable took different types of joins.
>
> On Tuesday, 22 July 2014 11:50:38 UTC+1, José Lorenzo wrote:
>>
>> Not sure why linkable is not working for you. Did you make sure to set
>> 'type' => 'INNER' in the linkable definition for that model? That will
>> filter records from the parent model according to the conditions in the
>> association.
>>
>> On Tuesday, July 22, 2014 10:57:14 AM UTC+2, David Yell wrote:
>>>
>>> *Synopsis*
>>> Trying to select data from multiple related models explicitly is hard in
>>> the framework due to it's focus on always providing a left join. Even with
>>> Containable and Linkable it's not always possible, without a manual query,
>>> to select records where their related record has a condition. Such as
>>> selecting all Posts where a Tag is authored by User x. It seems odd to me
>>> that the framework should return empty related model records or, if using
>>> linkable not return all the related records. I am starting this thread to
>>> discuss solutions, tips, tricks and techniques for achieving this
>>> functionality as I do not feel that either Containable or Linkable
>>> accurately cover this use-case. It may well be that case that some more
>>> guidance is needed in the documentation for this specific type of query.
>>>
>>> *The problem*
>>> You would like to select from primary model but only if related models
>>> conditions are met. The primary model should only be included in the result
>>> set if the related model conditions are met. With a single related model
>>> this is quite easy using Linkable. However when you start trying to achieve
>>> this across HABTM, hasManyThrough or two or three nested models the problem
>>> grows in complexity.
>>>
>>> A few examples, might make things clearer.
>>>
>>> *Hotel hasMany HotelFeature belongsTo Feature*
>>> You need to select hotels which have n number of specific features. The
>>> hotel must have all of the features listed to be included in the dataset.
>>>
>>> *Post hasMany PostsTag belongsTo Tag belongsTo User*
>>> You need to find all the posts which have tags created by a certain user.
>>>
>>> *Hotel belongsTo City belongsTo Province belongsTo Country*
>>> You need to select all the cities in a country where there is a hotel.
>>>
>>> I'm sure there are some other use-cases, but these are the ones of the
>>> top of my head. As you can see it's usually selecting a primary record
>>> where a related record matches a certain condition, but where the related
>>> record is a multiple.
>>>
>>> *Possible solutions*
>>> This is where I am unsure. My SQL-fu is weak thanks to Cake's awesome
>>> ORM. The only real thing that I can think of is either more enhancements to
>>> Containable or Linkable really. However I am not sure how to approach such
>>> a problem. I was told once in an interview that any more than five joins
>>> will impact performance in MySQL (could be fiction), so obviously there is
>>> a performance concern with highly normalised databases. Concerns which are
>>> ignored by Containable with it's multiple queries across many models - a
>>> solution which I think was devised to solve this problem.
>>>
>>> *Discussion*
>>> The idea of the thread was to discuss potential solutions really. I
>>> wanted to air my thoughts on a problem which I have hit many times with my
>>> current project and have had to battle against. Usually using manual joins,
>>> but in most cases I end up using Containable and filtering the data in the
>>> front-end `foreach()` which isn't elegant, clever or clean.
>>>
>>> I'm sure this kind of issue will have been addressed with the new ORM in
>>> 3.x, but I need to find a decent solution to this problem which will be
>>> flexible enough to be beneficial to lots of different projects, and to
>>> other Cake users. We are all taught to normalise, and it makes sense to do
>>> so, but this issue keeps nagging at me and I'm finding it frustrating. I
>>> don't feel that the framework is helping me solve such a problem.
>>>
>>> If you read to here, thanks, have a biscuit and share your thoughts. How
>>> do you get around this issue?
>>>
>>
--
Like Us on FaceBook https://www.facebook.com/CakePHP
Find us on Twitter http://twitter.com/CakePHP
---
You received this message because you are subscribed to the Google Groups
"CakePHP" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to cake-php+unsubscr...@googlegroups.com.
To post to this group, send email to cake-php@googlegroups.com.
Visit this group at http://groups.google.com/group/cake-php.
For more options, visit https://groups.google.com/d/optout.
