Glad to. After some reading I was able to take it a step further and turn
it into a custom find method, which nicely encapsulates the code in the
model. In our app we 'broadcast' articles to a number of different towns,
but because we also do a radius search that can result in duplicate rows
being returned. So I needed a function that does a SELECT DISTINCT on the
joining table to get unique ids, in the form of -
SELECT ... FROM articles Article JOIN (SELECT DISTINCT article_id FROM
articles_towns WHERE town_id IN (...)) ArticleTown ON
ArticleTown.article_id = Article.id
I decided to create a custom find method named broadcast.
In the Article model -
public $findMethods = array('broadcast' => true);
protected function _findBroadcast ($state, $query, $results = array()) {
if ($state == 'before') {
$query['joins'] = array(
array(
'table' => sprintf("(SELECT DISTINCT article_id FROM articles_towns WHERE
town_id IN (%s))", TownsComponent::neighbors()),
'alias' => 'ArticleTown',
'type' => "INNER",
'conditions' => array('ArticleTown.article_id = Article.id')));
return $query;
}
return $results;
}
In the Controller -
$this->Article->find('broadcast', $options);
or
$this->paginate = ('broadcast', $options);
$this->paginate('Article');
In my case the list of town is in a static component, but could easily be
passed in with the options. I think you will agree it can be done in a very
tidy way with Cake. With older versions of Cake I used to see example code
that was much longer than the SQL it generated and thought, what's the
point, why not just use SQL? But I'm growing to like the new abilities of
Cake and you can really minimize the amount of SQL you need to put in your
code.
Disclaimer - there may be better ways to do the same thing that I have not
thought of.
Mark
On Tuesday, August 28, 2012 12:08:38 PM UTC-4, ceap80 wrote:
>
> Hi Mark, could you post a code sample of how you solved?
>
> I'm always interested in querying the db the cakephpway.
>
> Thanks.
>
> On Monday, August 27, 2012 10:30:55 PM UTC-4:30, Mark Wratten wrote:
>>
>> Figured it out for myself, the subquery just goes in place of the table
>> name.
>>
>> On Monday, August 27, 2012 6:17:05 PM UTC-4, Mark Wratten wrote:
>>>
>>> Is there a 'Cake' way of including subqueries in join clauses of SQL
>>> select statements?
>>>
>>> I'm looking to generate something in the form of -
>>>
>>> SELECT ... FROM table1
>>> JOIN (SELECT DISTINCT key FROM table2 WHERE ...) table2 ON table2.key =
>>> table1.key
>>>
>>> I have been looking at joining tables in the docs, but it only seems to
>>> support joining tables rather than expressions. I could move the DISTINCT
>>> to the main query, but that does not perform as well, and also messes up
>>> pagination.
>>>
>>> Thanks
>>>
>>> Mark
>>>
>>
--
You received this message because you are subscribed to the Google Groups
"CakePHP" group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
Visit this group at http://groups.google.com/group/cake-php?hl=en-US.
