Find all distinct field values for a Model that occur N times in CakePHP

Question

So I have a model FeaturedListing that has a field date which is a mysql date field. There will be multiple FeaturedListings that have the same date value.

I want to find all dates that have N or more FeaturedListings on it. I think I'd have to group by date and then find which ones have N or more in there group and get the value that was grouped on. Could any one give me any pointers to accomplish that. A raw sql query may be required.

Edit: Thanks to the answers below it got me going on the right track and I finally have a solution I like. This has some extra conditions specific to my application but I think its pretty clear. This snippet finds all dates after today that have at least N featured listings on them.

$dates = $this->find('list', array(
        'fields' => array('FeaturedListing.date'),
        'conditions' => array('FeaturedListing.date >' => date('Y-m-d') ),
        'group' => array('FeaturedListing.date HAVING COUNT(*) >= $N')
        )
    );

I then make a call to array_values() to remove the index from the returned list and flatten it to an array of date strings.

$dates = array_values($dates);

score 1 · Answer 1 · answered Jul 14 '13 at 17:39

In "raw" SQL you would use group by and having:

select `date`
from FeaturedListings fl
group by `date`
having count(*) >= N;

If you want the listings on these dates, you need to join this back to the original data. Here is one method:

select fl.*
from FeaturedListings fl join
     (select `date`
      from FeaturedListings fl
      group by `date`
      having count(*) >= N
     ) fld
     on fl.`date` = fld.`date`

user221931 · Accepted Answer · 2013-07-14T21:19:12.017

1

No need to go to raw SQL, you can achieve this easily in cake ($n is the variable that holds N):

$featuredListings = $this->FeaturedListing->find('all', array(
   'fields' => array('FeaturedListing.date'),
   'group' => array('FeaturedListing.date HAVING COUNT(*)' => $n),
));

edited Jul 14 '13 at 21:19

answered Jul 14 '13 at 18:36

user221931

1,852
1
13
16

1

I got it to work using 'group' => array('FeaturedListing.date HAVING COUNT(*) >= $n') I saw other examples that put the having statement in the group statement. – Mike Jul 14 '13 at 19:41

Find all distinct field values for a Model that occur N times in CakePHP

2 Answers2