I know nothing about using MySQL views, but I've never found it a
problem to filter/sort on deep associations.
The trick is to use dynamic associations - a temporary `hasOne`
instead of a `hasMany`, so that cakephp performs a join and you can
then filter inline in the query.
For example, let's assume that :
Post hasOne Author [class: User]
User hasOne Profile
Post hasAndBelongsToMany Tag [with: PostsTag]
And you want to find all posts written by a user with a [EMAIL PROTECTED]
email [in Profile] which is associated to either 'Bacon' or 'Eggs'
tags. You also want to order the results first by whether the Tag is
Bacon or Eggs, then by the Author's surname
[I'll do this manually here -- so you can see what's going on; there
are a lot of behaviors and extra code around to make it easier and
less verbose]
First, set up the dynamic bindings:
$this->Post->bindModel(array(
'hasOne' => array(
'AuthorProfile' => array('className' => 'Profile', 'foreignKey' =>
false, 'conditions' => 'AuthorProfile.author_id=Author.id'),
'PostsTag1' => array('className' => 'PostsTag', 'foreignKey' =>
false, 'conditions' => 'PostsTag1.post_id=Post.id'),
'Tag1' => array('className' => 'Tag', 'foreignKey' => false,
'conditions' => 'PostsTag1.tag_id=Tag.id'),
)
), false);
Now call findAll() with the conditions based on your previous keys:
$data = $this->Post->find('all', array(
'fields' => 'Post.*, Author.*',
'recursive' => 0, // This can be higher to fetch associated stuff,
of course [but not lower!]
'conditions' => array('AuthorProfile.email' => 'LIKE [EMAIL PROTECTED]',
'Tag1.tag' => array('Bacon', 'Eggs'), '1=1 GROUP BY Post.id'),
'order' => array('(Tag1.tag = \'Bacon\') DESC', 'Author.surname
ASC')
));
And there ya go!
A few gotchas:
* You must either use the GROUP BY or a DISTINCT rowset to avoid
getting duplicate rows (e.g. if a Post was associated both with Bacon
and Eggs)
* If you're using paginate then don't forget the `false` parameter to
bindModel() or you'll be in trouble
* It's best to alias the dynamic joins distinctly from other
associations - hence the 'Tag1'.
* When performing multiple joins in a single query, make sure your
database tables are indexed properly. Use EXPLAIN SELECT with wild
abandon to get it fast and furious.
This is proof-of-concept only; in practice it's usually more efficient
to perform multiple queries than one giant one. Experiment away...
Anyway, hope this helps - and not too off-topic. Someone else can help
you out with using views, I'm sure.
On Apr 24, 6:49 pm, Sebastian Veggiani <[EMAIL PROTECTED]> wrote:
> Hi,
>
> somebody could clarify what is the level of support of Cake 1.2 for
> woriking with MySQL Views?
>
> For example, I've a chain o associations of three or more levels.
>
> If I want to filter the results by a field in the 3rd level or beyond
> there is no easy way to do it, unless I use a Custom SQL Query, but in
> this case I can't use the Model paginator in the view.
>
> The solution I found was to create a View in MySQL with the necessary
> data from joined tables and then filter by one of these fields. This
> works OK for me.
>
> The problem comes with pagination and sorting. When I try to sort by
> a view field which is not in the main table (specified by FROM) the
> application fails. Ordering by any field in the main table works OK.
>
> anybody knows something about MySQL Views in cake?
>
> 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 [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/cake-php?hl=en
-~--~~~~--~~--~--~---
