Re: Getting distinct data from a psql function in a cake query

You can probably do it by using the fields option in the CakePHP find
method, see the book at:
http://book.cakephp.org/view/73/Retrieving-Your-Data

The first example show the fields option with DISTINCT. That should
help you a little on the way!

Have you considered denormalizing your table, so that you store not
only the date, but also two additional columns - year and month -
calculated from your date column.

That would make it easier for you to use CakePHP find to gather all
the years, and for each year, gather all the months.

See what you can with the fields option, then show us your resulting
code/problems :)
Enjoy,
John

On Feb 28, 5:21 pm, Bailey <baileys...@gmail.com> wrote:
> 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