I've been searching a long while to find a way to handle the query I need. I
still need to try the searchable pluggin but I already managed to find a way
that somehow works for what I need.
But it doesn't give exactly the rights results and I'm not shure is the
right way to do it...
any help appreciatted!
here the query:
*$options['joins']* = array(
array( 'table' =>
'registers_categories',
'alias' => 'RegistersCategory',
'type' => 'inner',
'conditions' =>
array('Register.id = RegistersCategory.register_id')
),
array( 'table' => 'categories',
'alias' => 'Category',
'type' => 'inner',
'conditions' =>
array('RegistersCategory.category_id = Category.id')
),
array( 'table' =>
'registers_subcategories',
'alias' =>
'RegistersSubcategory',
'type' => 'inner',
'conditions' =>
array('Register.id = RegistersSubcategory.register_id')
),
array( 'table' => 'subcategories',
'alias' => 'Subcategory',
'type' => 'inner',
'conditions' =>
array('RegistersSubcategory.Subcategory_id = Subcategory.id')
),
array( 'table' =>
'registers_subsubcategories',
'alias' =>
'RegistersSubsubcategory',
'type' => 'inner',
'conditions' =>
array('Register.id = RegistersSubsubcategory.register_id')
),
array( 'table' => 'subsubcategories',
'alias' => 'Subsubcategory',
'type' => 'inner',
'conditions' =>
array('RegistersSubsubcategory.Subsubcategory_id = Subsubcategory.id')
)
);
* $options['contain']* = array('Language.id');
* $options['fields'] *= array('Register.id', 'Register.title',
'Register.tags', 'Language.id', 'Category.name', 'Subcategory.name',
'Subsubcategory.name');
*$options['conditions'] *= array( * 'AND'* =>
array('Register.published_by >' => 0, 'Language.id =' => 3),
* 'OR' *=> array( 'Register.title
LIKE' => '%'.$params['search'].'%',
'Register.tags LIKE'
=> '%'.$params['search'].'%',
'Category.name LIKE'
=> '%'.$params['search'].'%',
'Subcategory.name
LIKE' => '%'.$params['search'].'%',
'Subsubcategory.name
LIKE' => '%'.$params['search'].'%')
);
*$options['group'] *= 'Register.id';
*$this->find('all', $options);
*
I explain... I'm searching the word typed in Registers, when they are
published and in english ('AND') and I want to show all the results matched
in either Register.title, Register.tags, Category.name, Subcategory.name,
Subsubcategory.name
Those three last tables have a relation HABTM with Register, Language has a
relation Belongs to
running this with te part in orange commented out works well, but it doesn't
search in subsubcategories table. But if I run all it works but it ommits
some posts that shouldn't be ommited. I've checked many times possible typo
mistakes and I don't find them... I wonder if 3 inner joins is too much (I
doubt that) but I don't understand what's on the way...
I also read somewhere that Containable doesn't work with joins... but as I
said commenting the orange part it works fine..
I also wonder if using a custom query with UNION is better...
Then actually I would like to check if user had entered more than one word
and check each word separately and together... is then this the best way or
should I consider different options...
I'm a bit lost, but I guess that what I'm pretending to do is somehow common
to many of you so please if you could help with directions, ideas?
thanks!
--
Our newest site for the community: CakePHP Video Tutorials
http://tv.cakephp.org
Check out the new CakePHP Questions site http://ask.cakephp.org and help others
with their CakePHP related questions.
To unsubscribe from this group, send email to
[email protected] For more options, visit this group at
http://groups.google.com/group/cake-php
