On 08/04/2016 12:03 PM, Brian Cherinka wrote:
Yeah, sorry about that. Like I said, I don't normally build my classes
this way, defining all the columns in the Base Class. I define all my
columns, primary and foreign keys, etc manually first, so my SQLA
classes are minimally defined. And I cobbled together pieces from my
real code and your example to build that test code I sent you. So I
forgot some things. I did say it may not be completely right, and you
might have to hack it some. I apologize. Here is an attempt at
cleaning it up.
When I try to implement your @expression into my real code, it doesn't
work. I am getting an error about multiple results returned within the
subquery.
|
@restwave.expression
defrestwave(cls):
s =db.Session()
restw
=(func.unnest(Wavelength.wavelength)/(1+sampledb.NSA.z)).label("restw")
unwave
=s.query(restw).select_from(Wavelength).correlate(sampledb.NSA).subquery("unwave")
agg_unwave
=s.query(func.array_agg(unwave.c.restw)).label("restwarr")
joined =s.query(agg_unwave).select_from(
Cube
).join(sampledb.MangaTarget,sampledb.MangaTargetToNSA,sampledb.NSA,Wavelength)
returnjoined.as_scalar()
|
|
session.query(datadb.Cube.restwave).filter(datadb.Cube.mangaid=='1-113520').all()
|
|
ProgrammingError:(psycopg2.ProgrammingError)more than one row returned
bya subquery used asan expression
[SQL:'SELECT (SELECT (SELECT array_agg(unwave.restw) AS array_agg_1
\nFROM (SELECT unnest(mangadatadb.wavelength.wavelength) / (%(z_1)s +
mangasampledb.nsa.z) AS restw \nFROM mangadatadb.wavelength) AS unwave)
AS restwarr \nFROM mangadatadb.cube JOIN mangasampledb.manga_target ON
mangasampledb.manga_target.pk = mangadatadb.cube.manga_target_pk JOIN
mangasampledb.manga_target_to_nsa ON mangasampledb.manga_target.pk =
mangasampledb.manga_target_to_nsa.manga_target_pk JOIN mangasampledb.nsa
ON mangasampledb.nsa.pk = mangasampledb.manga_target_to_nsa.nsa_pk JOIN
mangadatadb.wavelength ON mangadatadb.wavelength.pk =
mangadatadb.cube.wavelength_pk) AS anon_1 \nFROM mangadatadb.cube
\nWHERE mangadatadb.cube.mangaid =
%(mangaid_1)s'][parameters:{'z_1':1,'mangaid_1':'1-113520'}]
|
I don't know if this is because I have multiple versions of the same
object or not. I tried adding in a selection on version, but the joins
did not work out properly. This NSA table does not need to be joined
with the versions. What I expect the above to return is exactly what
the raw SQL returns.
I've yet to see an unambiguous statement of what "the raw SQL" is. If
it is this:
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;
then that does not, and cannot, correspond to the Python code you are
sending. the JOINs would not be inside of the @expression, you'd have
to write them out on the outside.
I've provided a complete example of how to render SQL extremely similar
to what you want, featuring techniques such as correlate() as well as
how to nest the queries appropriately. If at this point you aren't able
to manipulate the code to get what you want, then I'd advise not using a
hybrid for this query at all. It is extremely complicated in this
context, and unless you are deeply familiar with SQLAlchemy APIs, you
would just have a bunch of code that you can't effectively maintain.
A list of the restwave array for each version of
the object with id = '1-113520'. I thought SQLA was designed precisely
to let users do what I'm trying, i.e. construct complex columns
involving multiple selects, without having to physically add a new
column into the db, or write a new SQL function in the db to call?
|
session.query(datadb.Cube.restwave).join(datadb.Cube,datadb.PipelineInfo,datadb.PipelineVersion).filter(datadb.PipelineVersion=='v1_5_1',datadb.Cube.mangaid=='1-113520').all()
InvalidRequestError: Could not find a FROM clause to join from. Tried
joining to <class
'sdss.internal.database.utah.mangadb.DataModelClasses.Cube'>, but got:
Can't find any foreign key relationships between 'nsa' and 'cube'.
|
On Wednesday, August 3, 2016 at 2:22:43 PM UTC-4, Mike Bayer wrote:
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.
--
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.
