[Dbix-class] can't use order by on a column created with "select as"

[Gabrielle]

Hi,

I am just learning to use DBIx.  I am trying to select a group of volunteers
and order them by their geographic distance from a certain point.  I
calculate their distance from the given point and select it as a new column
called DISTANCE.  When I run the query without the "order_by" clause, I get
the results I am expecting including the calculated DISTANCE column.  But
when I try to order the results by DISTANCE, I get an error that says
DISTANCE is an unknown column.  I have also tried ordering by userLastName,
which works fine.  I am assuming the problem is that the 'DISTANCE' column
is created with the "select as" clause.  Please let me know if anyone has
run into this before.

 

Here is the query:

 

        $resultSet = $schema->resultset('Volunteer')->search(

            {

                %$params

            },

            {

                select => ['volAge', 'volDistanceWillTravel',
'volFirstTime', 'volHoursPerWeek',

                    'volNotes', 'volFosterDogs', 'volFosterCats',
'volFosterOther', 'volMailings',

                    'volTraining', 'volDogSoc', 'volCatSoc', 'volTelephone',
'volAdmin', 

                    'volFundraise', 'volTransport', 'volClean', 'volWrite',
'volEvents',

                    'volGrantWrite', 'volHomeVisit', 'volPetfinder',
'volUpdateDate',

                    'pfUser.userFirstName', 'pfUser.userLastName',
'pfUser.userCity', 'pfUser.userState', 

                    'pfUser.userEmail1', 'pfUser.userPhone1', 

                    {IFNULL =>
["3956*acos(sin(RADIANS($L1))*sin(RADIANS(pfUser.userLat))+cos(RADIANS($L1))
*cos(RADIANS(pfUser.userLat))*cos(RADIANS(pfUser.userLon-$G1)))", '0']},],

                as => [qw/volAge volDistanceWillTravel volFirstTime
volHoursPerWeek

                    volNotes volFosterDogs volFosterCats volFosterOther
volMailings

                    volTraining volDogSoc volCatSoc volTelephone volAdmin 

                    volFundraise volTransport volClean volWrite volEvents

                    volGrantWrite volHomeVisit volPetfinder volUpdateDate

                    userFirstName userLastName userCity userState userEmail1
userPhone1 DISTANCE/],

                join=>'pfUser',

                order_by=>'DISTANCE',

                page=>1,

                rows=>10

            }

        );

 

 

Thanks,

 

Gabrielle DeWitt

Programmer/Developer

www.petfinder.com

Adopt a homeless pet!

 

-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.rawmode.org/pipermail/dbix-class/attachments/20060510/4ee8e5dd/attachment-0001.htm