Hi grigri,
Thank you for your enlighenting answer. It helped my further understanding of
cake and I found what bugged it (see down below).
What I need is what your second select example does e.g.:
SELECT Hotel . * , Hotelmaster . *
FROM hotels AS Hotel
LEFT JOIN hotelmasters AS Hotelmaster ON ( Hotelmaster.id =
Hotel.hotelmaster_id )
WHERE Hotelmaster.name LIKE "%novomar%"
$this->paginate is at the moment this array:
Array
(
[limit] => 10
[order] => Array
(
[Hotel.name] => asc
[Saison.id] => desc
)
[contain] => Array
(
[Hotelmaster] => Array
(
[conditions] => Array
(
[Hotelmaster.deleted] => 0
)
[Praefix] => Array
(
[fields] => Array
(
[0] => Praefix.name
)
)
)
[0] => Saison
[User] => Array
(
[fields] => User.name
)
)
[url] => Array
(
[controller] => hotels
[action] => suche
)
[conditions] => Array
(
[Hotel.deleted] => 0
[Hotelmaster.name LIKE] => "%novo%"
)
)
The call of
$hotels = $this->paginate('Hotel');
Results in following select satement:
SELECT `Hotel`.`id`, `Hotel`.`buchungscode1`, `Hotel`.`buchungscode2`,
`Hotel`.`buchungscode3`, `Hotel`.`buchungscode4`, `Hotel`.`praefix_id`,
`Hotel`.`name`, `Hotel`.`laengengrad`, `Hotel`.`breitengrad`, `Hotel`.`ort_id`,
`Hotel`.`zielgebiet_id`, `Hotel`.`hotelmaster_id`, `Hotel`.`kategorie`,
`Hotel`.`deleted`, `Hotel`.`inuse`, `Hotel`.`user_id`, `Hotel`.`sprache_id`,
`Hotel`.`saison_id`, `Hotel`.`mandant_id`, `Hotel`.`zeit`, `Hotel`.`fertig`,
`Hotel`.`inuse_zeit`, `Hotelmaster`.`id`, `Hotelmaster`.`buchungscode1`,
`Hotelmaster`.`buchungscode2`, `Hotelmaster`.`buchungscode3`,
`Hotelmaster`.`buchungscode4`, `Hotelmaster`.`praefix_id`,
`Hotelmaster`.`name`, `Hotelmaster`.`laengengrad`, `Hotelmaster`.`breitengrad`,
`Hotelmaster`.`ortmaster_id`, `Hotelmaster`.`zielgebietmaster_id`,
`Hotelmaster`.`kategorie`, `Hotelmaster`.`exklusiv`, `Hotelmaster`.`deleted`,
`Hotelmaster`.`inuse`, `Hotelmaster`.`user_id`, `Hotelmaster`.`zeit`,
`Hotelmaster`.`inuse_zeit`, `User`.`name`, `Saison`.`id`, `Saison`.`name`
FROM `hotels` AS `Hotel`
LEFT JOIN `hotelmasters` AS `Hotelmaster` ON (`Hotel`.`hotelmaster_id` =
`Hotelmaster`.`id` AND `Hotelmaster`.`deleted` = 0)
LEFT JOIN `users` AS `User` ON (`Hotel`.`user_id` = `User`.`id`)
LEFT JOIN `saisons` AS `Saison` ON (`Hotel`.`saison_id` = `Saison`.`id`)
WHERE `Hotel`.`deleted` = 0 AND `Hotelmaster`.`name` LIKE '\"%novo%\"'
ORDER BY `Hotel`.`name` asc, `Saison`.`id` desc LIMIT 10
Which gives me an empty result although one of my linked Hotelmasters has the
name "Novomar".
"Hotelmaster`.`deleted` = 0" is true for all of them at the moment.
AND HERE COMES THE BUG:
So the problem is '\"%novo%\"' in the result. If I try it with
`Hotelmaster`.`name` LIKE "%novo%" it works.
Phew.....
So how do I get the proper search string to my Mysql server?
At the moment I create the condition like this:
$paramhotel['Hotelmaster.name LIKE'] =
'"%'.trim($this->data['Hotel']['name']).'%"';
It works when I change it to
$paramhotel['Hotelmaster.name LIKE'] =
'%'.trim($this->data['Hotel']['name']).'%';
Thank you a lot for your help grigri!
Anja
-----Ursprüngliche Nachricht-----
Von: cake-php@googlegroups.com [mailto:[EMAIL PROTECTED] Im Auftrag von grigri
Gesendet: Dienstag, 25. November 2008 15:10
An: CakePHP
Betreff: Re: Comtain, conditions and pagination: correct syntax for conditions
Have you tried just putting the conditions in the paginate call?
class HotelsController extends AppController {
var $paginate = array(
'Hotel' => array(
'limit' => 10,
'contain' => array(
'Hotelmaster', ...
)
)
);
function search() {
// Example conditions
$conditions = array(
'Hotelmaster.deleted' => 0,
'Hotelmaster.name LIKE ?' => array('%novo%')
);
$results = $this->paginate('Hotel', $conditions);
}
}
Always remember that the `conditions` key in a relation is the join condition
(the bit in the 'ON (...)' clause), it doesn't affect the result set.
Contrast this (conditions in the relation):
SELECT Hotel.*, Hotelmaster.* FROM hotels AS Hotel LEFT JOIN hotelmasters AS
Hotelmaster ON (Hotelmaster.id=Hotel.hotelmaster_id
AND Hotelmaster.deleted=0)
with this (conditions in the find):
SELECT Hotel.*, Hotelmaster.* FROM hotels AS Hotel LEFT JOIN hotelmasters AS
Hotelmaster ON (Hotelmaster.id=Hotel.hotelmaster_id)
WHERE Hotelmaster.deleted=0
See the difference? (Run the queries directly in phpMyAdmin to see)
If you're having trouble, first unbind everything you don't need.
Contain only the important models, make it work, then add the others back one
by one.
Also, please paste the SQL logs you're getting. It makes diagnosing the problem
a lot easier :)
hth
grigri
On Nov 25, 1:42 pm, "Liebermann, Anja Carolin"
<[EMAIL PROTECTED]> wrote:
> Hi everybody,
>
> I am trying to program a search with contain since nearly a week now
> and I still don't get it right.
>
> What I want to do:
>
> Hotel belongsto Hotelmaster (Hotel is a kind of blueprint of
> Hotelmaster)
> I search for Hotel and want only to find datasets where the related
> Hotelmaster fulfills certain conditions.
> To make it worse the result should be paginated.
>
> What I have now in my hotels_controller.php is:
>
> $this->paginate['Hotel'] = array(
> 'limit' => 10,
> 'order' => array ('Hotel.name' => 'asc',
> 'Saison.id' => 'desc'),
> 'url' => $paginator_params,
> 'condition' => $paramhotel,
> 'contain'=> array(
> 'Hotelmaster'=> array( 'conditions'=>
> $parammaster,
>
> 'Praefix'=>array('fields'=>array('Praefix.name'))),
> 'Town'=> array('fields'=>
> array('Town.name','Town.id')),
> 'Praefix', //and some more models of no interest
> 'User'=> array('fields'=> 'User.name'))
> );
>
> $parammaster is an array depending on my search criteria and can look
> like (simple example):
> Array
> (
> [Hotelmaster.deleted =] => 0
> [Hotelmaster.name LIKE] => "%Novo%"
> )
>
> I am not sure if the syntax of my conditions is correct. In some
> examples I find on the net the syntax of the conditions within the
> contain statement differ from the "normal" conditions. And to make
> things worse the whole thing is in a "paginate" and not a "find".
>
> At the moment any search critera have no effect on my search. When I
> change the search criteria to something like this (
> Hotelmaster.deleted = 0
> Hotelmaster.name LIKE "%Novo%"
> )
>
> I get 5 search results when having only 2 datasets in my database.
> Very weird.
>
> What would be the correct syntax for my $parammaster searchconditions?
>
> Thank you for any help
>
> Anja
--~--~---------~--~----~------------~-------~--~----~
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
-~----------~----~----~----~------~----~------~--~---
