Getting distinct data from a psql function in a cake query

Hi all,

Have a question surrounding cakephp database queries.

I'm querying a psql database and the idea is I have a set of records
with a date. I'm trying to provide a search option that allows you to
select from a drop down lists on months and then a drop down list of
years.

In the drop down lists I only want to display a list of where a year
is present in a record, and the same applies for months.

So i have a record:
ID=1,date="2010-02-01", data="blah blah blah";
ID=2,date="2010-03-01", data="blah blah blah";
ID=3,date="2017-04-01", data="blah blah blah";
ID=1,date="2018-02-01", data="blah blah blah";

So I want to produce two drop downlists that show:

Years: 2010, 2017, 2018

And when a year is selected: 2010=>(February, March), 2017=>(April)
and 2018 =>(February).

My query SQL is:
SELECT DISTINCT extract(year from record_date) from table where
user_id = 1;

This works and brings up the exact list of years but it when I'd call
the value from the drop downlist of years i would get the associated
id and not the value... so i re-worked it like:

$years = $this->Model->query('SELECT DISTINCT
extract(year from record_date) AS "year" from table where user_id =
'.$user1.';');

$rYears = array();

foreach ($dYears AS $dYear) {
$year= $dYear[0]['year'];
array_push($rYears ,array($dYear[0]['year']=>$year));
}
$this->set('years',$dutyYears);

I can't quite model the 'DISTINCT extract(year from record_date)'
which I think is causing me the problem when i try to use:

$this->Model->findByuser_id($userID, 'DISTINCT extract(year from
record_date'));

Does anyknow know:

a) how to switch from a query statement to a cake findBy statement? as
i cant see if its possible to select a Distinct on the extract
function... cake keeps trying to put: 'Duty.extract(year.....)';

b) How i could build an options list for a form that works on the year
with taking the months of each year... i've figured out the sql so far
as:

SELECT DISTINCT extract(month from record_date) from tablewhere
extract(year from record_date) = YEAR AND user_id = 1;

Many thanks for any help :-)

Check out the new CakePHP Questions site http://cakeqs.org and help others with their CakePHP related questions.

You received this message because you are subscribed to the Google Groups "CakePHP" group.
To post to this group, send email to cake-php@googlegroups.com
To unsubscribe from this group, send email to
cake-php+unsubscribe@googlegroups.com For more options, visit this group at http://groups.google.com/group/cake-php?hl=en