Re: Selecting specific data from the database through multiple relations

I have managed to solve some issues, like counting hotels using things similar to the following model method.

/**
 * 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+unsubscribe@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.