Sorting with persistence service

[Gabriel Roffman]

When using the persistence service to get arrays of related objects,
is there any way to set a default sorting on returned objects? Or is
the best way to convert the array into a query and then do a query of
query?

--
Gabe Roffman
eTesters

[ike]

Well my problems with Email and this google group in particular seem
to be unfortunately damned persistent. I've tried just about
everything I can think of to get email to send from the datafaucet
account to this list, all of which should work, 'cause it's the same
smtp server as the tapogee domain, but fails every time, despite
having all the same settings in my mail client. <sigh>

Anyway, here's the response I've been trying to send all day.

No they don't sort currently. I've considered adding a sort order
column
to the cross-reference table it creates, but haven't done it yet. My
feeling generally speaking though is that if you need the information
sorted, it's better to use a gateway to gather the data from that
table
as a query, rather than relying on the object links.

[GabeRoffman]

Following the use of cfproperty metadata to drive the persistence
service, I like the idea of adding an optional "sort" attribute for
each property. If the property has a sort attribute (with value asc
or desc obviously), then it adds it to the sort statement associated
with the object as a whole. This would even allow multiple sorts on a
single query by adding the sorts to the object sort statement as they
appear in the property list. I understand not wanting to cloud the
persistence objects but I think a default sort for a particular table
is pretty common that for convenience, I wouldn't want to have to
switch over to a gateway object.

I'm trying to see what kind of sql statements that the persistence
service writes but I can't see any query information in the debugging
information and it's freaking me out!

[ike]

> service, I like the idea of adding an optional "sort" attribute for
> each property. If the property has a sort attribute (with value asc
> or desc obviously), then it adds it to the sort statement associated
> with the object as a whole. This would even allow multiple sorts on a
> single query by adding the sorts to the object sort statement as they
> appear in the property list. I understand not wanting to cloud the
> persistence objects but I think a default sort for a particular table
> is pretty common that for convenience, I wouldn't want to have to
> switch over to a gateway object.

Certainly something I'm willing to consider adding. When I mentioned
considering adding a sort order to the cross-reference table I was
actually thinking more in terms of use-cases where the relationship
actually should be ordered. As an example, if you were creating a
newsletter app and you could have multiple newsletters and each
newsletter could have multiple columns in it (maybe some of them are
syndicated across several newsletters), you'd want to be able to
manually assign the order that those columns are included in the
newsletter, rather than relying on either the default order or on an
alphabetical sort of the column names.

> I'm trying to see what kind of sql statements that the persistence
> service writes but I can't see any query information in the debugging
> information and it's freaking me out!

Ha! Sorry about that. Check out the debugging page of the
documentation.

http://www.datafaucet.com/sqldebug.cfm

All fo the queries run by DF have the same name (there's only one
pair
of cfquery tags), and so if we left the debugging on, you'd see a
bunch
of queries all with the same name. So we made the debugging a little
more
controlled. It's off by default. You can enable debugging for an
individual
query via the statement object, and you can enable debugging for an
arbitrary duration by turning it on or off for the request, using
request.datafaucet.tron() ("trace on") and request.datafaucet.troff()
("trace off"). If you want them all debug-on by default, just add
request.datafaucet.tron() to your onRequestStart() event. :)

ike