[jifty-devel] Howto: Select Distinct in Jifty::DBI

[Ruslan Zakirov]

Hello, Jes.

I've written some tests and a new patch. That would be cool if you extend tests.

On Tue, Jun 1, 2010 at 9:59 PM, Jes <jjjesss at gmail.com> wrote:
> Hi Ruslan:
>
> It seems there is some bug in the patch or in my code.
>
> I have MYSQL table called "celda" and "Celda" model with this columns
> (in fact there are more columns but for this example...):
>
> "ine" "poblacion"
>
> So, for example I could have this records:
>
> ine     poblacion other_column
> 14000   a1              1
> 14000   a1              2
> 14001   b1              3
> 14001   b1              4
> 14002   c1              5
> 14002   c1              6
>
> And I coded something like in View.pm:
>
> 8<-------------------------------------------------------
>    my $celdas = new ImpactoWeb::Model::CeldaCollection;
>
>    my @listofine = $celdas->distinct_column_values(
>                                                        column   => 'ine'
>                                                        );
>
>
>    if ( @listofine ) {
>        table {
>            attr { class => "datatable" } row {
>                th { "INE" };
>                th { "Municipio" };
>            };
>
>                        foreach my $ine (@listofine)
>                        {
>                                #find cells with that INE
>                                $celdas->limit(
>                        column   => 'ine',
>                        value    => $ine
>                        );
>
>                my $celda = $celdas->next;
>
>                                row {
>                                                cell { $ine };
>                                                cell { $celda->poblacion };
>                                };
>
>                        }
>        };    #table
>    }
>
> 8<-------------------------------------------------------
>
> This should show (that's my intention) something like:
>
>
> ine     poblacion
> 14000   a1
> 14001   b1
> 14002   c1
>
> but I got this error:
>
> WARN - DBD::mysql::db selectcol_arrayref failed: Unknown table 'main' in field list at /usr/local/share/perl/5.10.1/Jifty/DBI/Collection.pm line 959, <DATA> line 16.
>
> It seems something regarding this piece of code in your patch:
>
>    my $column = 'main.'. $args{'column'};      <<<<<<<<<<<<<<<<<< 'main.'
>    $query_string = 'SELECT DISTINCT '. $column . $query_string;
>
> Any idea?
>
> Thanks in advance,
>
> Jes
>
> El Tue, 1 Jun 2010 17:47:53 +0400
> Ruslan Zakirov <ruz at bestpractical.com> escribió:
>
>> Hello, Jes.
>>
>> I believe it's usable operation and deserves a method in collection.
>> In the attachment you can find patch for Jifty::DBI that demonstrates
>> how method will probably look. I didn't test the patch even if it
>> compiles or not.
>>
>> If it works for you and other people have no objections then I can
>> push it into trunk.
>>
>> On Sat, May 29, 2010 at 3:09 PM, Jes <jjjesss at gmail.com> wrote:
>> > El Fri, 28 May 2010 12:28:36 -0400
>> > Jesse Vincent <jesse at bestpractical.com> escribió:
>> >
>> >>
>> >> Jes,
>> >>
>> >> What are you actually trying to do?
>> >>
>> >> Best,
>> >> Jesse
>> >>
>> >> On Fri 28.May'10 at 11:19:51 +0200, Jes wrote:
>> >> > Hi all:
>> >> >
>> >> > How can I do a query like:
>> >> >
>> >> > SELECT DISTINCT myfield FROM mytable
>> >> >
>> >> > ???
>> >> >
>> >> > I'm used to deal with records and collections but I can't figure
>> >> > out how this can be coded (first time I have to deal with).
>> >> >
>> >> > Thanks in advance,
>> >> >
>> >> > Jes
>> >> > _______________________________________________
>> >> > jifty-devel mailing list
>> >> > jifty-devel at lists.jifty.org
>> >> > http://lists.jifty.org/cgi-bin/mailman/listinfo/jifty-devel
>> >> _______________________________________________
>> >> jifty-devel mailing list
>> >> jifty-devel at lists.jifty.org
>> >> http://lists.jifty.org/cgi-bin/mailman/listinfo/jifty-devel
>> >
>> >
>> > Hi:
>> >
>> > Well, I have several models. One of them has a lot of columns. There
>> > are a lot of records with one column in common. This column is
>> > called "ine", and the model is called "Celda". So, for example, to
>> > find all records for a particular value of "ine" I'd do:
>> >
>> >    my $cells = ImpactoWeb::Model::CeldaCollection->new;
>> >    $cells->limit(
>> >                column   => 'ine',
>> >                value    => $ine
>> >                );
>> >    while ( my $cell = $cells->next )
>> >        {
>> >        .... rest of the code...
>> >        }
>> >
>> > As well I need to find all different values of column "ine". In SQL
>> > I'd do:
>> >
>> > SELECT DISTINCT ine FROM Celda;
>> >
>> > or something similar. I'm only want to get an array, for example,
>> > with all different values of "ine".
>> >
>> > Thanks, bye
>> >
>> > Jes
>>
>>
> _______________________________________________
> jifty-devel mailing list
> jifty-devel at lists.jifty.org
> http://lists.jifty.org/cgi-bin/mailman/listinfo/jifty-devel
>



-- 
Best regards, Ruslan.
-------------- next part --------------
A non-text attachment was scrubbed...
Name: Jifty-DBI-02searches_distinct_values.t
Type: application/x-troff
Size: 5597 bytes
Desc: not available
URL: <http://lists.jifty.org/pipermail/jifty-devel/attachments/20100601/bdc70080/attachment.t>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: Jifty-DBI-collection_distinct_column_values_method.patch
Type: application/octet-stream
Size: 1126 bytes
Desc: not available
URL: <http://lists.jifty.org/pipermail/jifty-devel/attachments/20100601/bdc70080/attachment.obj>