Hello!
Thanks for the answer, the query works great.
I just have one problem, when I try to put it in paginate it doesn't work.
Is this line: "$this->paginate = ('broadcast', $options);" correct?
I also tried "findType" but it doesn't do anything.
Thanx!
On Tuesday, August 28, 2012 7:27:47 PM UTC+2, Mark Wratten wrote:
>
> 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 [email protected].
To post to this group, send email to [email protected].
Visit this group at http://groups.google.com/group/cake-php.
For more options, visit https://groups.google.com/groups/opt_out.
