There is still much ambiguity here and inaccuracy (JOINs on the outside
or JOINs on the inside, the mappings have mistakes like foreign key to
"pk" but no "pk", mappings without primary keys, "autoload" makes no
sense as I don't have your tables, etc.), so I can only guess but
perhaps give you enough clues. It is highly unusual to have a string
of four JOINs inside of a column-based subquery, but when you say
s.query(Cube.restwave), that's what that means here.
import sqlalchemy
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relationship, deferred, sessionmaker
from sqlalchemy.schema import Column, ForeignKey
from sqlalchemy import func
from sqlalchemy.dialects.postgresql import *
from sqlalchemy.types import Float, Integer, String
from sqlalchemy.ext.hybrid import hybrid_property
Base = declarative_base()
Session = sessionmaker()
class Target(Base):
__tablename__ = 'target'
pk = Column(Integer, primary_key=True)
mangaid = Column(String)
class Wavelength(Base):
__tablename__ = 'wavelength'
pk = Column(Integer, primary_key=True)
wavelength = deferred(Column(ARRAY(Float, zero_indexes=True)))
class NSA(Base):
__tablename__ = 'nsa'
pk = Column(Integer, primary_key=True)
z = Column(Float)
class TargetToNSA(Base):
__tablename__ = 'target_to_nsa'
id = Column(Integer, primary_key=True)
target_pk = Column(Integer, ForeignKey('target.pk'))
nsa_pk = Column(Integer, ForeignKey('nsa.pk'))
class Cube(Base):
__tablename__ = 'cube'
pk = Column(Integer, primary_key=True)
mangaid = Column(String)
target_pk = Column(Integer, ForeignKey('target.pk'))
target = relationship(Target, backref='cubes')
wavelength_pk = Column(Integer, ForeignKey('wavelength.pk'))
wavelength = relationship(Wavelength, backref='cubes')
@hybrid_property
def restwave(self):
raise NotImplementedError()
@restwave.expression
def restwave(cls):
s = Session()
restw = (func.unnest(Wavelength.wavelength) / (1 +
NSA.z)).label("restw")
unwave =
s.query(restw).select_from(Wavelength).correlate(NSA).subquery("unwave")
agg_unwave =
s.query(func.array_agg(unwave.c.restw)).label("restwarr")
joined = s.query(agg_unwave).select_from(
Cube
).join(Target, TargetToNSA, NSA, Wavelength)
return joined.as_scalar()
session = Session()
q = session.query(Cube.restwave).filter(Cube.mangaid == '1-113520')
print q
On 08/03/2016 02:00 PM, Brian Cherinka wrote:
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)
frommangadatadb.cube asc
join mangasampledb.manga_target asm on m.pk=c.manga_target_pk
join mangasampledb.manga_target_to_nsa ast on t.manga_target_pk=m.pk
join mangasampledb.nsa asn on n.pk=t.nsa_pk
join mangadatadb.wavelength asw 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}
(5rows)
|
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]:printrwquery
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
defrestwave(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()
returnrestwave
|
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
<mailto:sqlalchemy+unsubscr...@googlegroups.com>.
To post to this group, send email to sqlalchemy@googlegroups.com
<mailto:sqlalchemy@googlegroups.com>.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
--
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.
