Ok. Yeah, I have been trying many different ways of getting results. The
raw SQL that I'm trying to recreate in SQLA is this (for the restwave
column only), which works in postgresql. The limit was only there to do
the filter the results. You can ignore that limit.
manga=# select c.pk,c.mangaid,c.manga_target_pk, n.z, (select
(array_agg(unwave.restw))[0:5] as restwave from (select
(unnest(w.wavelength)/(1+n.z)) as restw from mangadatadb.wavelength as w)
as unwave)
from mangadatadb.cube as c
join mangasampledb.manga_target as m on m.pk=c.manga_target_pk
join mangasampledb.manga_target_to_nsa as t on t.manga_target_pk=m.pk
join mangasampledb.nsa as n on n.pk=t.nsa_pk
join mangadatadb.wavelength as w on w.pk=c.wavelength_pk;
pk | mangaid | manga_target_pk | z |
restwave
-------+---------+-----------------+--------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
11211 | 1-22286 | 30678 | 0.099954180419445 | {3292.49709827422,
3293.25529747001,3294.01371862107,3294.7723617274,3295.53100483373}
7729 | 1-22286 | 30678 | 0.099954180419445 | {3292.49709827422,
3293.25529747001,3294.01371862107,3294.7723617274,3295.53100483373}
11209 | 1-22298 | 15026 | 0.0614774264395237 | {3411.84452637247,
3412.63020900144,3413.41612163118,3414.2022642617,3414.98840689221}
7727 | 1-22298 | 15026 | 0.0614774264395237 | {3411.84452637247,
3412.63020900144,3413.41612163118,3414.2022642617,3414.98840689221}
11219 | 1-22301 | 35 | 0.105152934789658 | {3277.00884941768,
3277.76348196558,3278.51833542465,3279.27340979488,3280.02848416512}
(5 rows)
In SQLA, this code returns something but it is the wrong array for the
given Cube specified in the filter condition
In [20]: rwquery = session.query(datadb.Cube.restwave).filter(datadb.Cube.
mangaid=='1-113520')
In [21]: print rwquery
SELECT (SELECT array_agg(unwave.restw) AS array_agg_1
FROM (SELECT unnest(mangadatadb.wavelength.wavelength) / (%(z_1)s + nsaz.z)
AS restw
FROM mangadatadb.wavelength, (SELECT mangasampledb.nsa.z AS z
FROM mangasampledb.nsa JOIN mangasampledb.manga_target_to_nsa ON
mangasampledb.nsa.pk = mangasampledb.manga_target_to_nsa.nsa_pk JOIN
mangasampledb.manga_target ON mangasampledb.manga_target.pk = mangasampledb.
manga_target_to_nsa.manga_target_pk JOIN mangadatadb.cube ON mangasampledb.
manga_target.pk = mangadatadb.cube.manga_target_pk
WHERE mangadatadb.cube.mangaid = mangadatadb.cube.mangaid) AS nsaz) AS
unwave) AS anon_1
FROM mangadatadb.cube
WHERE mangadatadb.cube.mangaid = %(mangaid_1)s
In [22]: rwave = rwquery.first()
In [23]: rwave[0][0:10]
Out[23]:
[3292.49709827422,
3293.25529747001,
3294.01371862107,
3294.7723617274,
3295.53100483373,
3296.28986989532,
3297.04895691218,
3297.80826588431,
3298.56779681171,
3299.32732773911]
I do rwquery.first() here instead of .one() or .all() because in my
database, I actually have different versions of the same object, 6
versions, which I don't care about. But that's fine, I can add additional
filters later. In the code setup I sent up, there is only one version of
each cube object. The above results gets produced with the @expression
@restwave.expression
def restwave(cls):
session = db.Session()
nsaz = session.query(sampledb.NSA.z.label('z')).\
join(sampledb.MangaTargetToNSA, sampledb.MangaTarget, Cube).\
filter(Cube.mangaid == cls.mangaid).subquery('nsaz', with_labels
=True)
unwave = session.query((func.unnest(Wavelength.wavelength)/(1+nsaz.c
.z)).label('restw')).subquery('unwave', with_labels=True)
restwave = session.query(func.array_agg(unwave.c.restw)).as_scalar()
return restwave
I don't need any limits and I think I need 4 selects, the one in the query
I run explicitly s.query(Cube.restwave) and three inside the @expression.
When I grab the actual cube instance object and access restwave on the
instance side, it works just fine.
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
