How to get unambiguous column name for selectable?

[Andrew Pashkin]

Here is the working example:

import sqlalchemy as sa
from sqlalchemy.ext.declarative import declarative_base


engine = sa.create_engine('sqlite:///:memory:', echo=True)
Base = declarative_base()


class Foo(Base):
    __tablename__ = 'foo'

    id = sa.Column(sa.Integer, primary_key=True)
    name = sa.Column(sa.String)

    def __repr__(self):
        return 'Foo(id=%s, name="%s")' % (self.id, self.name)


class Bar(Base):
    __tablename__ = 'bar'

    id = sa.Column(sa.Integer, primary_key=True)
    name = sa.Column(sa.String)
    foo_id  = sa.Column(sa.Integer, sa.ForeignKey('foo.id'),
                        nullable=False)
    foo     = sa.orm.relationship('Foo', backref='bars')


Base.metadata.create_all(engine)
Session = sa.orm.sessionmaker(bind=engine)
session = Session()

baz = Foo(name='baz')
qux = Foo(name='qux')

session.add(baz)
session.add(qux)
session.add(Bar(name='spam', foo=baz))
session.add(Bar(name='parrot', foo=baz))
session.commit()

q = session.query(Foo).options(sa.orm.joinedload('bars'))
q = q.order_by(q.statement.c.name)
# q = q.order_by(Foo.name)  # Doesn't give an error

print(q.all())

# Gives:
#
# sqlalchemy.exc.OperationalError: (sqlite3.OperationalError)
# ambiguous column name: name

Here I have a two tables with columns name and one references another with FK. When they are joined - name becomes ambiguous, and SQLAlchemy ORM resolves this collision automatically. But how to do the same in case of using SQLAlchemy expressions?

-- 
With kind regards, Andrew Pashkin.
cell phone - +7 (985) 898 57 59
Skype - waves_in_fluids
e-mail - andrew....@gmx.co.uk

[Mike Bayer]

q = session.query(Foo).options(sa.orm.joinedload('bars'))

q = q.order_by(q.with_labels().statement.c.foo_name)

> --
> 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+...@googlegroups.com
> <mailto:sqlalchemy+...@googlegroups.com>.
> To post to this group, send email to sqlal...@googlegroups.com
> <mailto:sqlal...@googlegroups.com>.
> Visit this group at https://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.

[Andrew Pashkin]

On Tuesday, May 24, 2016 at 5:12:32 PM UTC+3, Mike Bayer wrote:

q = q.order_by(q.with_labels().statement.c.foo_name)

 

Is that possible to get column objects (or their tables) in the same order as they were joined in Expressions level and/or ORM level? In that case it would be [Foo.name, Bar.name].

[Mike Bayer]

I can make guesses as to what that's asking but I'd rather you clarify.
What you do mean "get"? like, render in the FROM clause? or a python
accessor like "my_statement.columns" ? the .c. collection there has
the columns in the order that they are rendered in the columns clause,
it's iterable.

[Andrew Pashkin]

On 05/25/2016 06:01 PM, Mike Bayer wrote:
> I can make guesses as to what that's asking but I'd rather you
> clarify. What you do mean "get"? like, render in the FROM clause? or
> a python accessor like "my_statement.columns" ? the .c. collection
> there has the columns in the order that they are rendered in the
> columns clause, it's iterable.

By "order" I meant, that in SELECT ... FROM foo LEFT JOIN bar ON ...
query, the columns of foo would be placed first and the columns of bar
second.
And by "get" I meant Column objects or something else, that would
unambiguously define them.

[Mike Bayer]

You need to be using join() and not joinedload() here.   You shouldn't care about the columns or syntaxes that joinedload() renders.  Search the docs for "the zen of eager loading",

--
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+...@googlegroups.com.
To post to this group, send email to sqlal...@googlegroups.com.

[Andrew Pashkin]

On 05/25/2016 08:36 PM, Mike Bayer wrote:
> You need to be using join() and not joinedload() here. You shouldn't
> care about the columns or syntaxes that joinedload() renders. Search
> the docs for "the zen of eager loading",

Yes, I know that, but with join() my question still relevant. What I
want to do is for given column name and an arbitrary SA query -
retrieve unambiguous reference to the actual column of that query with
priority to those columns, which are "closer" to the root SELECT.

So for SELECT foo.name, bar.name FROM foo LEFT JOIN bar ON ... and
column name "name", I would like to build a function, that would return
a reference to a column object for foo.name.

I found a way of doing this, using froms attribute and finding original
SELECT by walking down relations, listed in it. But I wonder if there
are better ways.

[Mike Bayer]

On 05/25/2016 02:15 PM, Andrew Pashkin wrote:
> On 05/25/2016 08:36 PM, Mike Bayer wrote:
>> You need to be using join() and not joinedload() here. You shouldn't
>> care about the columns or syntaxes that joinedload() renders. Search
>> the docs for "the zen of eager loading",

> Yes, I know that, but with join() my question still relevant. What I
> want to do is for given column name and an arbitrary SA query -
> retrieve unambiguous reference to the actual column of that query with
> priority to those columns, which are "closer" to the root SELECT.

>
> So for SELECT foo.name, bar.name FROM foo LEFT JOIN bar ON ... and
> column name "name", I would like to build a function, that would return
> a reference to a column object for foo.name.

Part of what you're asking is exactly something SQLAlchemy Core can do
very well, the "priority to the column that's "closer" to the root
SELECT" part, and the other part is not possible: "given "name",
magically know that I want "name" from Foo, not Bar".

What heuristic would be used to know that "name" is for Foo and not Bar
? (why do you even need this?) If you're going by, "I know that Foo
is first and Bar is second", that's brittle - what makes Foo be first?
That part of the program that happens to put "Foo.name" first should
instead be tagging "Foo.name" as important in some way that is more
explicit than just "it happens to be first". An annotation is one way
this could be done (e.g. the _annotate() method).

[Andrew Pashkin]

On 05/25/2016 09:28 PM, Mike Bayer wrote:

why do you even need this?

Basically I have a REST API for which I want to add functionality of filtering and sorting, according to user provided parameters. User supposed to provide a field name and the magic function should resolve this field name in a given query to an unambiguous column reference, to use it for sorting and filtering.

what makes Foo be first?

Well, my idea is, that for this query:

SELECT foo.name, bar.name FROM foo LEFT JOIN bar ON ...

foo is the first, because it's in the FROM part and bar is the second, because it's being joined.

This logic is analogous to how Django ORM works with relations. In such query - Foo.objects.select_related('bars'), Django allows user to reference columns in this way: .filter(bars__name='spam').filter(name='parrot') - notice, in the latter filter(), Django resolves name as Foo.name, because it was in FROM.

[Mike Bayer]

On 05/25/2016 02:38 PM, Andrew Pashkin wrote:
> On 05/25/2016 09:28 PM, Mike Bayer wrote:
>> why do you even need this?
> Basically I have a REST API for which I want to add functionality of
> filtering and sorting, according to user provided parameters. User
> supposed to provide a field name and the magic function should resolve
> this field name in a given query to an unambiguous column reference, to
> use it for sorting and filtering.

Well first off, your original example that uses joinedload(), you will
never get a hold of anything from Bar, so for that, feel free to order
by the single entity based on name. Core / ORM handles this case
automatically as of 1.0:

q = session.query(Foo).options(joinedload('bars'))
q = q.order_by("name")


output:

SELECT foo.id AS foo_id, foo.name AS foo_name, bar_1.id AS bar_1_id,
bar_1.bar_id AS bar_1_bar_id
FROM foo LEFT OUTER JOIN bar AS bar_1 ON foo.id = bar_1.bar_id ORDER BY
foo.name

Similarly, use filter_by(name='x'), again, Bar is part of joinedload(),
you can't do anything to it:


q = session.query(Foo).options(joinedload('bars'))
q = q.order_by(name='foob')
q = q.order_by("name")


output:

SELECT foo.id AS foo_id, foo.name AS foo_name, bar_1.id AS bar_1_id,
bar_1.bar_id AS bar_1_bar_id
FROM foo LEFT OUTER JOIN bar AS bar_1 ON foo.id = bar_1.bar_id
WHERE foo.name = ? ORDER BY foo.name

there's a lot more that can be done here but if you're querying against
a single entity, its attribute names are first class referenceable.

Assuming you want more, phrase this as an input/output situation for me.
Give me input and what the desired output you want is. The big guns
here are a function called corresponding_column() which I can show you
how to use if that's what's needed.

[Andrew Pashkin]

On 05/25/2016 11:16 PM, Mike Bayer wrote:

Assuming you want more, phrase this as an input/output situation for me.  Give me input and what the desired output you want is.  The big guns here are a function called corresponding_column() which I can show you how to use if that's what's needed

Here is the full "input":

q = session.query(Foo).outerjoin(Bar)

q = q.order_by('name')

print(q)

Here is the output:

SELECT foo.id AS foo_id, foo.name AS foo_name

FROM foo
LEFT OUTER JOIN bar

ON foo.id = bar.foo_id
ORDER BY bar.name

While desired output is ORDER BY foo.name.

Here is my solution:

def get_leftmost(selectable):
    current = selectable.froms[0]
    while True:
        if hasattr(current, 'left'):
            current = current.left
        elif hasattr(current, 'append'):
            current = current[0]
        elif hasattr(current, 'columns'):
            return current
        else:
            raise AssertionError

print(q.order_by(get_leftmost(q.statement).c.name))

It gives me:

SELECT foo.id AS foo_id, foo.name AS foo_name

FROM foo LEFT OUTER
JOIN bar ON foo.id = bar.foo_id
ORDER BY foo.name

Which is what I want. But a) it's too hacky b) I'm not sure if froms guarantees any ordering.

[Andrew Pashkin]

Bump.

[Mike Bayer]

I wasn't sure what the question was here.

To get the first ORM entity, use column_descriptions:

query = query.order_by(query.column_descriptions[0]["entity"].name)


http://docs.sqlalchemy.org/en/latest/orm/query.html?highlight=column_descriptions#sqlalchemy.orm.query.Query.column_descriptions

On 07/15/2016 04:49 AM, Andrew Pashkin wrote:
> Bump.
>

[Andrew Pashkin]

On 07/15/2016 04:14 PM, Mike Bayer wrote:
> I wasn't sure what the question was here.
>
> To get the first ORM entity, use column_descriptions:
>
> query = query.order_by(query.column_descriptions[0]["entity"].name)

Are entities in column_descriptions ordered? Is there something similar
for SA SQL expressions?

[Mike Bayer]

On 07/21/2016 10:09 AM, Andrew Pashkin wrote:
> On 07/15/2016 04:14 PM, Mike Bayer wrote:
>> I wasn't sure what the question was here.
>>
>> To get the first ORM entity, use column_descriptions:
>>
>> query = query.order_by(query.column_descriptions[0]["entity"].name)
> Are entities in column_descriptions ordered?

yes

Is there something similar
> for SA SQL expressions?
>

select.c

[Andrew Pashkin]

On 07/21/2016 05:13 PM, Mike Bayer wrote:
>> Are entities in column_descriptions ordered?
>
> yes

In what order?

> Is there something similar
>> for SA SQL expressions?
> select.c

How to retrieve an entity from select.c, which is suitable for putting
it in select.order_by?

[Andrew Pashkin]

I think, that in case of Select, Select.froms can be used from retrieving tables. But what what to know is if it's ordered too?

[Mike Bayer]

for putting an expression into select.order_by you probably want to
iterate instead through select.inner_columns:

http://docs.sqlalchemy.org/en/latest/core/selectable.html?highlight=select.inner_columns#sqlalchemy.sql.expression.Select.inner_columns


>