For some reason, I don't see the responses to this in the group. They are in my email, but not the group. For the benefit all everyone I'm gonna paste these in. Please note I'm not the author of everything:
grigri to Cake The easiest way to simulate an INNER join from a LEFT is to impose a not-null condition on the PK of the joined field. Assuming you're using the bindable behavior to add a left join, your SQL will look like this: SELECT [...] FROM `requests` AS `Request` LEFT JOIN `dates` AS `Date` ON (...) WHERE (...) you need to add an external condition (not inside the ON ()), so the sql is like this SELECT [...] FROM `requests` AS `Request` LEFT JOIN `dates` AS `Date` ON (...) WHERE (`Date`.`id` IS NOT NULL AND (...)) You can add that through the $conditions parameter of the find method, should be simple enough. On Feb 7, 6:03 am, Baz L <[EMAIL PROTECTED]> wrote: > Any ideas anyone? > -- > Baz L > Web Development 2.0: Web Design, CakePHP, > Javascripthttp://www.WebDevelopment2.com/ Dardo Sordi Bogado to cake-php http://groups.google.com/group/cake-php/browse_thread/thread/9f092441185a5b72/f32a7b86bc9ec5d1?lnk=gst&q=%22inner+join%22#f32a7b86bc9ec5d1[[BR - Hide quoted text - AD7six to Cake On Feb 7, 2:44 pm, Baz <[EMAIL PROTECTED]> wrote: > Hey AD7six, > > This sounds promising, but I'm unsure how to implement in my case. > > 1. What would I use as the 'join table' since I have a Has Many and a > Belongs To? > > ThanX in advance. $this->bindModel(array('hasOne' => 'Dummy' => array('className' => 'Date'))); $conditions = array('Dummy.date_start' => '> ' . $start); $recursive = 1; $results = $this->find('all', compact('conditions', 'recursive')); // find all requests with at least one Date that starts in the future. Or similar. JP Mortiboys to me Sure thing. The main difference between a left join and an inner join is that an inner join requires both records to exist whereas a left join only requires that the left (first specified) record exists. You can simulate an inner join by performing a left join and requiring that the right record exists. Any conditions you set inside the association variable will be set in the ON(...) part of the select query. This will not influence whether or not a record is joined, only how it is joined, so it's useless for this purpose. If you impose a condition on the outer part of the findAll, it is obeyed for every record fetched. So if you specify that the PK (primary key) of the joined table must exist, then it will effectively be an inner join. $this->Request->bindModel(array('hasOne' => array( 'Date' => array('conditions' => array('Date.date' => '> 2008-02-07')) )); $this->Request->findAll(); This will generate the following SQL: SELECT [...] FROM `requests` AS `Request` LEFT JOIN `dates` AS `Date` ON(`Date`.`request_id`=`Request`.`id` AND `Date`.`date` > '2008-02-07') As you can see, the date condition you put in the binding is inside the ON() clause, so the left join really is a left join, and the SQL will return all requests, with a joined date that is in the future, if it exists, and null otherwise. To force the inner join, add a condition to the actual find call: $this->Request->findAll('Date.id IS NOT NULL'); Of course, you could just put the date condition in the findAll directly: $this->Request->bindModel(array('hasOne' => array('Date'))); $this->Request->findAll(array('Date.date' => '> 2008-02-07')); Which will effectively do the same thing. Personally though, I prefer the first method - it's more clear cut. Hope this clears things up, if not, let me know and I'll see if I can explain it a bit better. grigri Baz wrote: Baz to JP show details Feb 7 Reply JP Mortiboys: YOU'RE A GENIUS!!!!! Although I discovered how to do custom pagination yesterday, it seemed like overkill for this problem. ThanX. more minor caveat with my specific example: I use the bindable behavior, so I needed to restrict to model to not include the hasMany date, then do a manual bind for the hasOne, THEN pass reset as false, since I was using paginate. ThanX all. -- Baz L --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Cake PHP" 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 -~----------~----~----~----~------~----~------~--~---
