[sqlalchemy] When building a query, option to only join once

[Michael Elsdörfer]

I keep running into this issue where I have a complex set of filter 
parameters, some of which need a join, and it would be nice to have an 
ergonomic way to say: join this table, but not if there already is a join.

I found this old post on the subject: 
https://groups.google.com/g/sqlalchemy/c/ooFYsED4CI8

I was just curious if there have been further developments since then; in 
particular, using new-style querying in 1.4, I guess there isn't a way to 
add a custom, de-duplicating  `join()` method onto the `select()` object?

Michael

-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
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 view this discussion on the web visit 
https://groups.google.com/d/msgid/sqlalchemy/280e2f1b-ae95-487a-9d99-aef1bd73d20an%40googlegroups.com.

[sqlalchemy] How about: Disabling relationship cascades on a per-object basis.

[Michael Elsdörfer]

So, I've been a very happy user of SQLAlchemy over many years, though maybe 
not very deeply. I certainly don't really feel qualified to contribute any 
thoughts on it's design, but I did want to bring this up, since it's 
something I have run into again and again.

I'd say that in the majority of projects that involve SQLAlchemy, sooner or 
later I find myself in a situation where I want to work with database 
objects that I don't want to save to the session. It's just so damn 
convenient to pass them around.

Here are two examples off the top of my head:

- A billing tool was supposed to generate a bunch of objects (line items 
and so on); in preview mode, I want to use the same functions to calculate 
and generate the objects, and serialize them to the browser, but I don't 
want to save them.

- In a system to check if the user has the permission to "add an object", I 
really wanted to construct the object temporarily, unsaved, and send it to 
the permission-layer, but without adding it to the session.

Now often, what I do to solve this is what I think SQLAlchemy wants me to 
do, if I understand correctly: Configure the cascades on the relationships 
accordingly. But this has some problems:

1) It's *really* hard. Each time I find myself going back to the 
documentation, and trying to figure out where stuff needs to be changed 
(what does cascade_backrefs on a backref() mean again?)

2) It's error prone. It's easy to later pull in an object through some 
other relationship; it's also hard to later figure out exactly why the 
cascades where configured in the way that they are, and which code paths 
depend on that particular cascade setting. Changing any cascade may easily 
cause side effects that are not predictable.

So thinking about it, changing the cascade settings on the relationship is 
not really what I want to do. What I really want: The cascade to work in 
most cases, *except this one time*. I find myself searching for a version 
of `session.no_autoflush`. For example:

with session.no_cascade:
   order = Order() 
   order.account = current_account

Since current_account is in the session, the order would ordinarily be 
pulled into it, too. But the decorator could prevent that. 

Or maybe:

from sqlalchemy import taint_no_cascade
order = Order()
taint_no_cascade(order)
order.account = current_account

The whole thing is probably much more complicated than that, but I desire 
this so frequently, I wanted to ask if it's feasable, or has been discussed 
before.

Thanks,

Michael

-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
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] Joins: Returning multiple instances, and a potential bug

[Michael Elsdörfer]

I'm sorry if this is an obvious question, but there seem to be a
couple ways to do joins, and I seem unable to find an example
explaining how to achieve my particular result.

I need to join two tables, and I want the mapper objects of both
tables returned. Essentially, I want

session.query(m1, m2)

but of course, that doesn't do an actual join, and I need to use
filter() instead of an onclause. While in this particular case I don't
really care, I would imagine that there probably are scenarios where
one would, so my first question is: Can the same result (multiple
mappers returned) be achieved while generating an actual sql JOIN
statement?

Now specifically, the query i am using is between three models, Sale,
Item, and an m2m SoldItem. I need to join all three tables, and want
to return Sale and SoldItem objects.

If I use this query:

 session.query(Sale, SoldItem)\
 .join((Item, SoldItem.item_id==Item.id)).\
 .filter(...sale/solditem condition...)

it actually generates invalid SQL (Unknown column 'solditem.item_id'
in 'on clause'):

 SELECT ... FROM solditem, sale INNER JOIN item ON
solditem.item_id = item.id

That is, it tries to join sale with item, which is incorrect.
Apparently, the tables in the from clause are listed reverse from how
I passed the classes to query(), so I actually have to use:

session.query(SoldItem, Sale) ...

to make the query work, which is rather confusing. If this is how the
interaction between query() and join() is supposed to work, then
shouldn't the join() apply at least to the last mapper listed? Is this
a bug?

-- 
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalch...@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.

[sqlalchemy] Re: Working with mapper objects without saving them

[Michael Elsdörfer]

Thanks, that looks like pretty much the thing I need.

Although, as a note, I mostly define my relationships in the opposite
direction than then example in the documentation; That is, I define a
order relationship in the Item model, with a items backref, which
I suppose is just a question of preference. Unless I'm missing
something though, this currently requires me to use cascade_backref
like so:

order = db.relationship(Order,
backref=db.backref('items',cascade_backrefs=False))

I.e. it's a slight brain twister, the backref of the backref meaning
the relationship being defined. Just throwing this out there, in case
someone thinks adding another argument which could be passed to
relationship() in a case like mine might be appropriate.

Michael

-- 
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalch...@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.

[sqlalchemy] Inter-Inheritance ForeignKeys - just need some assurance I'm doing it right

[Michael Elsdörfer]

I have, simplified, the following models:

class Booking(db.Model):
id = db.Column(db.Integer, primary_key=True)

class PlanBooking(Booking):
id = db.Column(db.Integer, db.ForeignKey(Booking.id),
primary_key=True)

class FeatureBooking(Booking):
id = db.Column(db.Integer, db.ForeignKey(Booking.id),
primary_key=True)
plan_booking_id = db.Column(db.Integer,
db.ForeignKey(PlanBooking.id))

That is, for each plan, additional features can be purchased, and both
a purchased plan, and a purchased feature have the same set of base
fields. I'm using joined-inheritance.

SQLAlchemy will complain Can't determine join between 'booking' and
'feature_booking'; tables have more than one foreign key constraint
relationship between them. Please specify the 'onclause' of this join
explicitly., for the declaration of the FeatureBooking class.

Using Google, I managed to avoid this by adding, to the FeatureBooking
class:

__mapper_args__ = {
'inherit_condition': (Booking.id == id),
}

I assume this is correct, since it seems to make it work.

Now I would like to map the relationship between FeatureBooking and
PlanBooking, so I define inside FeatureBooking:

plan_booking = db.relationship(PlanBooking,
backref=db.backref('feature_bookings',))

SQLAlchemy says: Could not determine join condition between parent/
child tables on relationship FeatureBooking.plan_booking. Specify a
'primaryjoin' expression.

So I change this to:

plan_booking = db.relationship(PlanBooking,
backref=db.backref('feature_bookings',),
primaryjoin=(PlanBooking.id==plan_booking_id))

And indeed, that seems to work. I don't even need to repeat the
primaryjoin for the backref, it seems to pick it up non-the-less.

So what I'm unsure about is why I need to do all this - it would seem
that SQLAlchemy could determine on it's own both how to a) join the
inheritance relationship, as well as the plan_booking relationship -
both clearly target separate models, and for both models there is a
separate, matching ForeignKey. If I had to guess I would think that
SQLAlchemy in some form considers the FeatueBooking.id and Booking.id
fields to be identical, and thus sees only two identical ForeignKeys
with the same target.

In fact, if the plan_booking_id field is not defined as a ForeignKey
at all, the plan_booking relationship is accepted by SQLAlchemy
without an explicit primaryjoin expression - SQLAlchemy will
automatically choose to join FeatureBooking.id == Booking.id, which is
of course wrong.

So I'm not sure - is this a bug that I should report, just a
deficiency that might not be fixable, or am I missing something and/or
this an easier way to do it?

-- 
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalch...@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.