Autogenerated query for sorting in datagrid widgets

[Jose San Leandro]

Hi,

I have a minimal db model with two tables: FILE and TICKET. A FILE is
a collection of TICKETs, and a TICKET can only belong to one FILE. I'm
pasting the relationship below, hoping you can see it using a fixed
font.


+------------------+ +-------------------+
| | X | |
| |1 / \
N| |
| FILE +<--------------X
X=========>| TICKET |
| | \ / | |
| | X | |
+------------------+ +-------------------+

The FILE table doesn't contain an explicit column for the number of
associated tickets. I don't want to denormalize the model if I can
avoid it.
I present the list of files in a datagrid, defining a new column
'tickets', with a custom :reader function, to display that information
for each ticket. However, the built-in sort feature fails since it
uses a generic way consisting of autogenerated sql queries, and that
information is not explicit in the FILE table:

While accessing database #<MYSQL-DATABASE localhost/prj/prj OPEN
{B8FF6D9}>
with expression "SELECT FILE.NAME,FILE.ID,FILE.TICKETS FROM FILE
ORDER BY FILE.TICKETS ASC LIMIT 6 OFFSET 0":
Error 1054 / Unknown column 'FILE.TICKETS' in 'field list'
has occurred.

I just need a way to customize the generated query, in order to do the
join myself and count the number of TICKETs pointing to each FILE. How
can I override that query?

Thank you in advance.

[Saikat Chakrabarti]

To do this, should try adding a tickets slot to your file view that
doesn't map to your file model. The way to get this to work is to
have a :reader for your ticket slot. Thus, if your model is:

(clsql:def-view-class file ()
((id
:initarg :id
:accessor file-id
:type integer)
(name
:initarg :name
:accessor file-name
:type string)))

you can have a view as follows:

(defview file-view (:inherit-from '(:scaffold file)
:type table)
(tickets :reader (lambda (obj) (some-code-for-getting-total-
tickets)))

Here, "obj" passed into the :reader for tickets is a reference to your
file object, so you should be able to use that to construct some query
to get the total number of tickets for that file and have your lambda
return that.

Hope that helps.


On Mar 22, 5:54 am, Jose San Leandro <jose.sanlean...@ventura24.es>
wrote:

[Saikat Chakrabarti]

Another thing I forgot to mention - if you are using clsql, you can
have slots on your model that are of type join that reference all the
associated objects. This just makes the code a little cleaner (and
you don't have to write as much SQL). So, your model for file could
be:

(clsql:def-view-class file ()
((id
:initarg :id
:accessor file-id
:type integer)
(name
:initarg :name
:accessor file-name
:type string)

(tickets
:accessor file-tickets
:db-kind :join
:db-info (:join-class ticket
:home-key id
:foreign-key file-id
:set t)))

Where your other model is called "ticket" and has slots for id (the
ticket's id) and file-id (referencing the file the ticket is a part
of). Then, in your :reader lambda for your view, you can just sum the
values in the list that you get from calling (file-tickets obj) and
you don't need to write any clsql syntax stuff.

[Jose San Leandro]

Thanks for your response.

I already had the 'tickets' attribute in the file class, defining it
as a join as you suggest.
I also use a custom reader in the view for listing the files, counting
all associated tickets.
Everything is working fine: i get the list of files, and each one with
the correct number of tickets. But when I click on the built-in 'sort'
feature for the 'tickets' column, I get the error.

I looked at the source code of the widget, but I don't understand most
of it right now.

Another thing I'm trying to do is generating a link for each cell in
the table with the ticket count, so that it takes the user to the list
of associated tickets for that file. I thought it should be done
with :present-as, but I have no idea how.

Anyway, I greatly appreciate any help.

[Jose San Leandro]

Oops, I actually didn't had the tickets attribute. I defined the join
from ticket to file, not the other way round. I'll try your suggestion
now.

Thanks!

On Mar 23, 10:17 am, Jose San Leandro <jose.sanlean...@ventura24.es>

[Jose San Leandro]

I declared the join in the 'file' side as well, and the instances are
related correctly. But it doesn't fix the sort issue.
In fact, whenever I customize the :reader function, the sort fails.
And it should, but I don't know how to implement the sort myself for
such columns.


On Mar 23, 10:19 am, Jose San Leandro <jose.sanlean...@ventura24.es>

[Robin Lee Powell]

On Sun, Mar 22, 2009 at 02:54:01AM -0700, Jose San Leandro wrote:
>
>
> While accessing database #<MYSQL-DATABASE localhost/prj/prj OPEN
> {B8FF6D9}>
> with expression "SELECT FILE.NAME,FILE.ID,FILE.TICKETS FROM FILE
> ORDER BY FILE.TICKETS ASC LIMIT 6 OFFSET 0":
> Error 1054 / Unknown column 'FILE.TICKETS' in 'field list'
> has occurred.
>
> I just need a way to customize the generated query, in order to do
> the join myself and count the number of TICKETs pointing to each
> FILE. How can I override that query?

Use the on-query slot:

on-query

A function designator that accepts the widget instance, as well as
sorting and pagination parameters. The function is expected to
return a properly sorted and paged sequence. The function should
also accept a :countp keyword argument. If true, the function should
return only the number of items with the given parameters, not the
actual items themselves. If this slot is NIL (the default), the
dataseq will operate on all persistent classes specified in
'data-class'. Alternatively, 'on-query' may be a list of
store-dependent keywords that will be passed to the datastore. This
may be a SQL 'where' clause, etc.

Initargs::on-query; Accessors:dataseq-on-query.

-Robin

--
They say: "The first AIs will be built by the military as weapons."
And I'm thinking: "Does it even occur to you to try for something
other than the default outcome?" -- http://shorl.com/tydruhedufogre
http://www.digitalkingdom.org/~rlpowell/ *** http://www.lojban.org/