With echo=True, the following sql statements are reported:
INFO:sqlalchemy.engine.base.Engine.0x...9890:select version()
INFO:sqlalchemy.engine.base.Engine.0x...9890:{}
2011-12-25 21:08:58,796 INFO sqlalchemy.engine.base.Engine.0x...9890
select version()
2011-12-25 21:08:58,797 INFO sqlalchemy.engine.base.Engine.0x...9890
{}
INFO:sqlalchemy.engine.base.Engine.0x...9890:select current_schema()
INFO:sqlalchemy.engine.base.Engine.0x...9890:{}
2011-12-25 21:08:58,819 INFO sqlalchemy.engine.base.Engine.0x...9890
select current_schema()
2011-12-25 21:08:58,819 INFO sqlalchemy.engine.base.Engine.0x...9890
{}
2011-12-25 21:08:58,822 INFO sqlalchemy.engine.base.Engine.0x...9890
SELECT count(1) AS count_1
INFO:sqlalchemy.engine.base.Engine.0x...9890:SELECT count(1) AS
count_1
FROM (SELECT "Leden".id AS id, "Leden".achternaam AS achternaam,
"Leden".tussenvoegsel AS tussenvoegsel, "Leden".voorletters AS
voorletters, "Leden".adres AS adres, "Leden".postcode AS postcode,
"Leden".woonplaats AS woonplaats, "Tarieven".omschrijving AS
omschrijving, "Tarieven".tariefbedrag AS tariefbedrag,
"Leden".adresenpc AS adresenpc
FROM "Leden", "Tarieven"
FROM (SELECT "Leden".id AS id, "Leden".achternaam AS achternaam,
"Leden".tussenvoegsel AS tussenvoegsel, "Leden".voorletters AS
voorletters, "Leden".adres AS adres, "Leden".postcode AS postcode,
"Leden".woonplaats AS woonplaats, "Tarieven".omschrijving AS
omschrijving, "Tarieven".tariefbedrag AS tariefbedrag,
"Leden".adresenpc AS adresenpc
WHERE "Leden".lidsoort_id IN (%(lidsoort_id_1)s, %(lidsoort_id_2)s)
AND "Tarieven".id = "Leden".lidsoort_id ORDER BY "Leden".achternaam)
AS contributie_alle_leden
FROM "Leden", "Tarieven"
2011-12-25 21:08:58,822 INFO sqlalchemy.engine.base.Engine.0x...9890
{'lidsoort_id_2': 2, 'lidsoort_id_1': 1}
WHERE "Leden".lidsoort_id IN (%(lidsoort_id_1)s, %(lidsoort_id_2)s)
AND "Tarieven".id = "Leden".lidsoort_id ORDER BY "Leden".achternaam)
AS contributie_alle_leden
INFO:sqlalchemy.engine.base.Engine.0x...9890:{'lidsoort_id_2': 2,
'lidsoort_id_1': 1}
INFO:sqlalchemy.engine.base.Engine.0x...9890:SELECT
contributie_alle_leden.id AS contributie_alle_leden_id,
contributie_alle_leden.achternaam AS
contributie_alle_leden_achternaam,
contributie_alle_leden.tussenvoegsel AS
contributie_alle_leden_tussenvoegsel,
contributie_alle_leden.voorletters AS
contributie_alle_leden_voorletters, contributie_alle_leden.adres AS
contributie_alle_leden_adres, contributie_alle_leden.postcode AS
contributie_alle_leden_postcode, contributie_alle_leden.woonplaats AS
contributie_alle_leden_woonplaats, contributie_alle_leden.omschrijving
AS contributie_alle_leden_omschrijving,
contributie_alle_leden.tariefbedrag AS
contributie_alle_leden_tariefbedrag, contributie_alle_leden.adresenpc
AS contributie_alle_leden_adresenpc
2011-12-25 21:08:58,925 INFO sqlalchemy.engine.base.Engine.0x...9890
SELECT contributie_alle_leden.id AS contributie_alle_leden_id,
contributie_alle_leden.achternaam AS
contributie_alle_leden_achternaam,
contributie_alle_leden.tussenvoegsel AS
contributie_alle_leden_tussenvoegsel,
contributie_alle_leden.voorletters AS
contributie_alle_leden_voorletters, contributie_alle_leden.adres AS
contributie_alle_leden_adres, contributie_alle_leden.postcode AS
contributie_alle_leden_postcode, contributie_alle_leden.woonplaats AS
contributie_alle_leden_woonplaats, contributie_alle_leden.omschrijving
AS contributie_alle_leden_omschrijving,
contributie_alle_leden.tariefbedrag AS
contributie_alle_leden_tariefbedrag, contributie_alle_leden.adresenpc
AS contributie_alle_leden_adresenpc
FROM (SELECT "Leden".id AS id, "Leden".achternaam AS achternaam,
"Leden".tussenvoegsel AS tussenvoegsel, "Leden".voorletters AS
voorletters, "Leden".adres AS adres, "Leden".postcode AS postcode,
"Leden".woonplaats AS woonplaats, "Tarieven".omschrijving AS
omschrijving, "Tarieven".tariefbedrag AS tariefbedrag,
"Leden".adresenpc AS adresenpc
FROM "Leden", "Tarieven"
WHERE "Leden".lidsoort_id IN (%(lidsoort_id_1)s, %(lidsoort_id_2)s)
AND "Tarieven".id = "Leden".lidsoort_id ORDER BY "Leden".achternaam)
AS contributie_alle_leden ORDER BY contributie_alle_leden.id
LIMIT 24 OFFSET 0
2011-12-25 21:08:58,925 INFO sqlalchemy.engine.base.Engine.0x...9890
{'lidsoort_id_2': 2, 'lidsoort_id_1': 1}
FROM (SELECT "Leden".id AS id, "Leden".achternaam AS achternaam,
"Leden".tussenvoegsel AS tussenvoegsel, "Leden".voorletters AS
voorletters, "Leden".adres AS adres, "Leden".postcode AS postcode,
"Leden".woonplaats AS woonplaats, "Tarieven".omschrijving AS
omschrijving, "Tarieven".tariefbedrag AS tariefbedrag,
"Leden".adresenpc AS adresenpc
FROM "Leden", "Tarieven"
WHERE "Leden".lidsoort_id IN (%(lidsoort_id_1)s, %(lidsoort_id_2)s)
AND "Tarieven".id = "Leden".lidsoort_id ORDER BY "Leden".achternaam)
AS contributie_alle_leden ORDER BY contributie_alle_leden.id
LIMIT 24 OFFSET 0
INFO:sqlalchemy.engine.base.Engine.0x...9890:{'lidsoort_id_2': 2,
'lidsoort_id_1': 1}
The problem is in the last few sentences where you can see '.... ORDER
BY "Leden".achternaam) AS .... ORDER BY contributie_alle_leden.id'. So
the last ORDER BY is consistent with what is visible in the GUI:
ordered by primary key. So the question should be now: how to
construct a select that omits the last (and unwanted) ORDER BY clause?
Peter
On Dec 25, 5:26 pm, Michael Bayer <mike...@zzzcomputing.com> wrote:
> On Dec 25, 2011, at 7:36 AM, VDK wrote:
>
>
>
>
>
>
>
>
>
> > I have the following piece of code:
>
> > class ContributieNotas(object):
>
> > class Admin(EntityAdmin):
> > verbose_name = 'Contributie notas'
> > verbose_name_plural = 'Contributienota\'s'
> > list_display = ['id', 'achternaam', 'tussenvoegsel',
> > 'voorletters', 'adres',
> > 'postcode', 'woonplaats','omschrijving',
> > 'tariefbedrag', 'adresenpc']
>
> > def setup_views():
>
> > q = select([model.Leden.id, model.Leden.achternaam,
> > model.Leden.tussenvoegsel, model.Leden.voorletters,
> > model.Leden.adres, model.Leden.postcode,
> > model.Leden.woonplaats, model.Tarieven.omschrijving,
> > model.Tarieven.tariefbedrag,
> > model.Leden.adresenpc],
> > whereclause =
> > and_(model.Leden.lidsoort_id.in_([1,2]),
> > model.Tarieven.id == model.Leden.lidsoort_id),
> > order_by = (model.Leden.achternaam))
>
> > q = q.alias('contributie_alle_leden')
>
> > mapper(ContributieNotas, q)
>
> First off is definitely analyze the issue in terms of SQL using echo=True.
> There's no default ORDER BY in modern SQLAlchemy so the primary key ordering
> is likely just the insert ordering of the table. This is not directly
> linked to the primary key except that surrogate integer primary keys are
> typically assigned sequentially as rows are inserted. Second, the ORDER
> BY here will be rendered inside an aliased select, "SELECT * FROM (SELECT *
> FROM ... ORDER BY ...) as contribute_alle_leden WHERE <criterion>".
> Generally this will maintain that ordering but not necessarily, if the
> selectable is part of a bigger context that again changes how the database
> merges rows into the final result set. So for the ordering of the final
> result, the ORDER BY should be on the outside.
>
> mapper() offers an option called order_by which will instruct the Query
> object to place this as the default ORDER BY in the absence of other
> orderings. It must be in terms of the mapped selectable, here the q.alias():
>
> mapper(ContNot, q.c.achternaam)
>
>
>
> > one of them is putting the order by clause in the mapper but without
> > result.
>
> so if you've tried this then something else is off, and watching the
> generated SQL will begin to describe what's actually happening.
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
