Forum OpenACS Development: order by query command

Collapse
Posted by yo ha on
I was wondering if you can pass in a parameter to the order by command of a postgresql query in a .xql file because i am not having success with it.

This is my query:

SELECT citation_types.name as l_name, citation_references.citation_reference_id as l_id,
citation_references.title as l_title, citation_references.author_surname as l_surname,
citation_references.author_firstname as l_firstname, citation_references.date as l_date,
citation_references.description as l_description
FROM citation_types, citation_references, acs_objects, citation_libraries
WHERE citation_references.type_id = citation_types.citation_type_id
AND citation_references.deleted = false
AND citation_references.citation_reference_id = acs_objects.object_id
AND acs_objects.creation_user = :user_id
AND citation_references.library_id = citation_libraries.library_id
AND citation_references.library_id = :library_id
ORDER BY :sortOne, :sortTwo, :sortThree

where i am passing in three values to the order by command (sortOne, sortTwo and sortThree).

The error I get is:
ERROR: non-integer constant in ORDER BY

I think the problem is to do with the fact that when the query gets run, single quotes gets put around sortOne, sortTwo and sortThree, so that the order by command becomes:

ORDER BY 'l_name', 'l_date', 'l_surname'

instead of ORDER BY l_name, l_date, l_surname

if sortOne, sortTwo and sortThree are l_name, l_date and l_surname respectively.

Does anyone know how to fix this problem?

Thanks.

Collapse
2: Re: order by query command (response to 1)
Posted by Gustaf Neumann on
use attribute names in the order by clause, not the colon names. you find many examples if you search in packages with "grep -R orderby *"
Collapse
3: Re: order by query command (response to 1)
Posted by Brian Fenton on
Try dollar signs ($) instead of colons (:)
Collapse
4: Re: order by query command (response to 1)
Posted by yo ha on
Brian,

Thanks a lot that saved me a lot of trouble.