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

[Jes]

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
> 
>