[ic] sort by a field in relational table

[interchange-users@interchange.redhat.com (Jonathan Clark)]

> I know this question may be too basic but I could not find in doc and
> mail-list.
>
> How to sort search results by a field of another table ?
>
> In my case, I would like to sort the search results like below by sort field
> in state table.
>
> The state table have fields like this: code contry sort name.
>
> [loop search="
>    st=db
>    fi=userdb
>    co=yes
>    sf=membership
>    se=A
>    tf=state
>    su=yes
>    ml=99999
>   "]
>   [loop-change 1][condition][loop-data userdb
> state][/condition]<br><b>[loop-data userdb state]</b>[/loop-change 1]
>  [loop-data userdb username]&nbsp;
> [/loop]

I think you will need to use the [query] tag and some SQL:

[query type=list list=1 ml=99999 sql=|	SELECT userdb.username AS username,
					 userdb.state AS state
					FROM userdb, state
					WHERE userdb.state=state.state
					 AND userdb.membership='A'
					ORDER BY state.sort
					|]
	[list]
		[sql-param username]
	[/list]

[/query]

(not tested, and not sure exactly what your data relationship is)

Jonathan
Webmaint.