On 13 December 2012 23:08, Drew <[email protected]> wrote:
> Greetings,
>
> I'd like to use the sql 'distinct' in a SQLFORM.grid 'query', but it is
> only valid as parameter in the 'select' method.
> I guess the 'group' parameter to SQLFORM.grid would work, but then I have
> to build the columns to group by, which seems excessive.
>
> Is it possible to create or pass raw sql to create a 'query' object and
> pass as parameter to SQLFORM.grid?
>
> Many cool features of 'select' are not available in SQLFORM.grid, unless
> I'm missing something.
>
>
SQLFORM.grid is limited as far as complex queries is concerned.
You can try bypassing 'distinct' with a clever use of 'groupby'. I rarely
use 'distinct' since I found that postgresql's 'group by' is a faster
option.
What I do in case of complex queries (also because it takes a lot of time
to execute) is to use db.executesql() and put the result in temporary
tables which then can be viewed quickly using SQLFORM.grid.
I use this technique for a series of standard queries. The user then has
the option in the menu to update the query and wait for the result to
become available or view the last result immediately.
Here is an example of such a query which is not that complex but it takes
quite a while to execute:
query = """
SET SEARCH_PATH TO isi;
DROP TABLE IF EXISTS isi.nr_arts_per_journal;
CREATE TABLE isi.nr_arts_per_journal AS (SELECT 1 as id, C.so,
A.py, COUNT(B.ut) AS aantal
FROM
isi.rjoern_uitgawe A,
isi.rjoernaal C,
isi.rarticle B
WHERE
A.rj_id = C.uuid
AND
B.ui = A.ui
GROUP BY C.so, A.py
ORDER BY count(B.ut) desc);
ALTER TABLE isi.nr_arts_per_journal
OWNER TO crest; """
the table db.nr_arts_per_journal has the following definition as model:
db.executesql("set search_path to isi;")
db.define_table('nr_arts_per_journal',
Field('so'),
Field('py'),
Field('aantal', "integer"),
migrate=False,
fake_migrate=True)
db.executesql("set search_path to isi, public;")
Regards
Johann
--
Because experiencing your loyal love is better than life itself,
my lips will praise you. (Psalm 63:3)
--
