Re: Subquery in join clause in CakePHP 2.2

There is a problem, if set table prefix in database config, it will also add the prefix to subquery. 

在 2012年8月29日星期三UTC+8上午1时27分47秒，Mark Wratten写道：

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

--
Like Us on FaceBook https://www.facebook.com/CakePHP
Find us on Twitter http://twitter.com/CakePHP
 
---
You received this message because you are subscribed to the Google Groups "CakePHP" group.
To unsubscribe from this group and stop receiving emails from it, send an email to cake-php+unsubscribe@googlegroups.com.
To post to this group, send email to cake-php@googlegroups.com.
Visit this group at http://groups.google.com/group/cake-php?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.