Re: [sqlalchemy] Re: order by child object's field

2016-03-22 Thread Simon King 
For what it's worth, SQLAlchemy usually does add the join condition for
you, based on your relationship definitions. But the second parameter to
query.join() is an optional expression that *replaces* the join condition
that would normally be generated.

Simon

On Mon, Mar 21, 2016 at 5:42 PM,  wrote:

> Dear Simon,
>
> thanks. This works and look great. I should stick this code on my
> sleeping pillow. :D
>
> From viewpoint of SQL or the database this makes totally sense to me.
> But my fault (in thinking) was expect that the relationship()
> definitions I did in the classes would be enough for SQLA to know what
> I want. ;)
> I should never keep SQL out of my head.
>
> This will help me a lot in the future!
>
> --
> 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.
>

-- 
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.

Re: [sqlalchemy] Re: order by child object's field

2016-03-21 Thread c.buhtz 
Dear Simon,

thanks. This works and look great. I should stick this code on my
sleeping pillow. :D

>From viewpoint of SQL or the database this makes totally sense to me.
But my fault (in thinking) was expect that the relationship()
definitions I did in the classes would be enough for SQLA to know what
I want. ;)
I should never keep SQL out of my head.

This will help me a lot in the future!

-- 
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.

Re: [sqlalchemy] Re: order by child object's field

2016-03-21 Thread Simon King 
On Mon, Mar 21, 2016 at 5:21 PM,  wrote:

> Hi Michal
>
> > q.join(ReferenceAuthor, ReferenceAuthor.c.Index == 0)
>
> Ah, of course! Thank you very much!
>
> But... ;)
>
> This Python code
>
>  return self.session.query(Reference) \
>  .filter_by(_mark = False) \
>  .join(Periodical) \
>  .join(ReferenceAuthor,
>ReferenceAuthor.c.Index==0) \
>  .join(Person) \
>  .order_by(Periodical._name) \
>  .order_by(Person._lastname) \
>  .all()
>
>
>
> The SQL echoed looks ok:
>
> SELECT "Reference"."ID" AS "Reference_ID",
>"Reference"."HasLabel" AS "Reference_HasLabel",
>"Reference"."PeriodicalID" AS "Reference_PeriodicalID"
> FROM "Reference"
> JOIN "Periodical"
> ON "Periodical"."ID" = "Reference"."PeriodicalID"
> JOIN "ReferenceAuthor"
> ON "ReferenceAuthor"."Index" = ?
> JOIN "Person"
> ON "Person"."ID" = "ReferenceAuthor"."PersonID"
> WHERE "Reference"."HasLabel" = 0
> ORDER BY "Periodical"."Name",
>  "Person"."LastName"
>
> But result doesn't looks like that it is secondary sorted by first
> persons lastname. See as an example one "Periodical._name" (Bmc
> Geriatrics) and the first persons lastname of 8 References.
>
> Bmc Geriatrics
>   Dapp
>   Tuntland
>   Hsu
>   van der Elst
>   Khatib
>   Hermans
>   van Buul
>   Lichtner
>

You are missing the join condition between ReferenceAuthor and Reference.
You probably want something like this:

join(ReferenceAuthor,
 and_(ReferenceAuthor.c.ReferenceId == Reference.ID),
  ReferenceAuthor.c.Index == 0))

Simon

-- 
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.

Re: [sqlalchemy] Re: order by child object's field

2016-03-21 Thread c.buhtz 
Hi Michal

> q.join(ReferenceAuthor, ReferenceAuthor.c.Index == 0)

Ah, of course! Thank you very much!

But... ;)

This Python code

 return self.session.query(Reference) \
 .filter_by(_mark = False) \
 .join(Periodical) \
 .join(ReferenceAuthor,
   ReferenceAuthor.c.Index==0) \
 .join(Person) \
 .order_by(Periodical._name) \
 .order_by(Person._lastname) \
 .all()



The SQL echoed looks ok:

SELECT "Reference"."ID" AS "Reference_ID",
   "Reference"."HasLabel" AS "Reference_HasLabel",
   "Reference"."PeriodicalID" AS "Reference_PeriodicalID"
FROM "Reference"
JOIN "Periodical"
ON "Periodical"."ID" = "Reference"."PeriodicalID"
JOIN "ReferenceAuthor"
ON "ReferenceAuthor"."Index" = ?
JOIN "Person"
ON "Person"."ID" = "ReferenceAuthor"."PersonID"
WHERE "Reference"."HasLabel" = 0
ORDER BY "Periodical"."Name",
 "Person"."LastName"

But result doesn't looks like that it is secondary sorted by first
persons lastname. See as an example one "Periodical._name" (Bmc
Geriatrics) and the first persons lastname of 8 References.

Bmc Geriatrics
  Dapp
  Tuntland
  Hsu
  van der Elst
  Khatib
  Hermans
  van Buul
  Lichtner

-- 
Verfassungsbeschwerden gegen Vorratsdatenspeicherung

Dein Recht zu unterzeichnen!

-- 
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.

Re: [sqlalchemy] Re: order by child object's field

2016-03-21 Thread Michal Petrucha 
On Mon, Mar 21, 2016 at 05:21:21PM +0100, c.bu...@posteo.jp wrote:
> > ReferenceAuthor is an instance of sqlalchemy.Table, so you can refer
> > to its columns as ReferenceAuthor.c.Index.
> 
> Ah nice. But something is still wrong.
> Part of the query:
> 
>   .join(ReferenceAuthor,
>   ReferenceAuthor.c.Index=0) \
> 
> result in
> ReferenceAuthor.c.Index=0) \
> ^
> 
> SyntaxError: keyword can't be an expression
> 
> Not sure what is wrong here. But I think c.Index doesn't exist?

That's not it, try:

q.join(ReferenceAuthor, ReferenceAuthor.c.Index == 0)

If you use a single equals sign, Python understands that as an attempt
to use a keyword argument; keyword arguments need to be valid Python
identifiers, not arbitrary expressions. That's why it gave you a
SyntaxError.

In this case, you do not want to use a keyword argument, but an
ordinary expression that gets translated into SQL by SQLAlchemy.

Good luck,

Michal

-- 
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.


signature.asc
Description: Digital signature

Re: [sqlalchemy] Re: order by child object's field

2016-03-21 Thread c.buhtz 
> ReferenceAuthor is an instance of sqlalchemy.Table, so you can refer
> to its columns as ReferenceAuthor.c.Index.

Ah nice. But something is still wrong.
Part of the query:

.join(ReferenceAuthor,
  ReferenceAuthor.c.Index=0) \

result in
ReferenceAuthor.c.Index=0) \
^

SyntaxError: keyword can't be an expression

Not sure what is wrong here. But I think c.Index doesn't exist?

-- 
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.

Re: [sqlalchemy] Re: order by child object's field

2016-03-21 Thread Simon King 
On Mon, Mar 21, 2016 at 3:05 PM,  wrote:

> On 2016-03-20 13:12 Jonathan Vanasco  wrote:
> >  .contains_eager('_periodical')\   # this lets sqlalchemy
>
> This eager-loading thing is a nice hint. I will use this in the future.
> Thanks.
>
> I haven't specified a important part of my question because I didn't
> think about it. :)
>
> A "Reference" can have more then one "Persons" (relation is
> "ReferenceAuthor"). The "Persons" are orderd by an index
> ("ReferenceAuthor."Index").
> How can I order by "Person._lastname" of only the FRIST author? I could
> write something like this
>
>   join(ReferenceAuthor, ReferenceAuthor.Index=0)
>
> But my problem is that I don't know how to name/adress this index
> field? As you can see in my first post this column doesn't have a
> "name" (in pythonic meaning). It is just a Column("Index", ...).
>

ReferenceAuthor is an instance of sqlalchemy.Table, so you can refer to its
columns as ReferenceAuthor.c.Index.

Simon

-- 
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.

Re: [sqlalchemy] Re: order by child object's field

2016-03-21 Thread c.buhtz 
On 2016-03-20 13:12 Jonathan Vanasco  wrote:
>  .contains_eager('_periodical')\   # this lets sqlalchemy

This eager-loading thing is a nice hint. I will use this in the future.
Thanks.

I haven't specified a important part of my question because I didn't
think about it. :)

A "Reference" can have more then one "Persons" (relation is
"ReferenceAuthor"). The "Persons" are orderd by an index
("ReferenceAuthor."Index").
How can I order by "Person._lastname" of only the FRIST author? I could
write something like this

  join(ReferenceAuthor, ReferenceAuthor.Index=0)

But my problem is that I don't know how to name/adress this index
field? As you can see in my first post this column doesn't have a
"name" (in pythonic meaning). It is just a Column("Index", ...).

-- 
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.

[sqlalchemy] Re: order by child object's field

2016-03-20 Thread Jonathan Vanasco 
There might be a way to do it off the relationships API entirely.  I do the 
following way all the time:

Join the child table, order by it, then use `contains_eager(childfield)` so 
sqlalchemy won't do another query for the child data

return self.session.query(Reference) \ 
 .join(Periodical, Reference.periodical_id == Periodical.id)\
 .order_by(Periodical.id.asc())\ 
 .contains_eager('_periodical')\   # this lets sqlalchemy know you 
loaded the `_periodical` relationship, it will build those objects
 .all() 


-- 
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.