Cake doesn't have an excellent way of doing this yet but grigri posted
this method the other day:
http://groups.google.com/group/cake-php/browse_thread/thread/863305f8a7267067
which is a lot better than writing out the SQL by hand.
quoted (grigri):
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:
On May 1, 2:08 pm, clrockwell <[EMAIL PROTECTED]> wrote:
> I have two associated models - Newproduct hasMany Newproductimage -
>
> Ideally, I need to do a find that only returns the Newproductimage
> where 'newproductimage.thumbnail' is set to 1
>
> Doing:
> $conditions = array('Newproductimage.thumbnail' => 1)
> $this->Newproduct->find('all', array('conditions' => $conditions))
> searches through Newproduct for thumbnail, which does not exist.
>
> I know that I can rebuild the array with the returned results, but it
> seems like cake would handle this for me.
>
> Thanks for any tips.
--~--~---------~--~----~------------~-------~--~----~
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
-~----------~----~----~----~------~----~------~--~---
