So using 1.3, where you can make a relationship to aliased....my
MariaDB here won't do it:
sqlalchemy.exc.NotSupportedError:
(MySQLdb._exceptions.NotSupportedError) (1235, "This version of
MariaDB doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'")
[SQL: SELECT domain_suffix_1.id AS domain_suffix_1_id,
domain_suffix_1.suffix AS domain_suffix_1_suffix,
domain_suffix_1.domain_suffix_id__parent AS
domain_suffix_1_domain_suffix_id__parent
FROM domain_suffix AS domain_suffix_1
WHERE %s = domain_suffix_1.domain_suffix_id__parent AND
domain_suffix_1.id IN (SELECT domain_suffix_1.id
FROM domain_suffix AS domain_suffix_1
WHERE %s = domain_suffix_1.domain_suffix_id__parent ORDER BY
domain_suffix_1.suffix ASC
LIMIT %s) ORDER BY domain_suffix_1.suffix ASC]
[parameters: (1, 1, 25)]
SQLite seemed to work:
SELECT domain_suffix_1.id AS domain_suffix_1_id,
domain_suffix_1.suffix AS domain_suffix_1_suffix,
domain_suffix_1.domain_suffix_id__parent AS
domain_suffix_1_domain_suffix_id__parent
FROM domain_suffix AS domain_suffix_1
WHERE ? = domain_suffix_1.domain_suffix_id__parent AND
domain_suffix_1.id IN (SELECT domain_suffix_1.id
FROM domain_suffix AS domain_suffix_1
WHERE ? = domain_suffix_1.domain_suffix_id__parent ORDER BY
domain_suffix_1.suffix ASC
LIMIT ? OFFSET ?) ORDER BY domain_suffix_1.suffix ASC
2019-02-23 13:28:45,910 INFO sqlalchemy.engine.base.Engine (1, 1, 25, 0)
[DomainSuffix('sub_00'), DomainSuffix('sub_01'),
DomainSuffix('sub_02'), DomainSuffix('sub_03'),
DomainSuffix('sub_04'), DomainSuffix('sub_05'),
DomainSuffix('sub_06'), DomainSuffix('sub_07'),
DomainSuffix('sub_08'), DomainSuffix('sub_09'),
DomainSuffix('sub_10'), DomainSuffix('sub_11'),
DomainSuffix('sub_12'), DomainSuffix('sub_13'),
DomainSuffix('sub_14'), DomainSuffix('sub_15'),
DomainSuffix('sub_16'), DomainSuffix('sub_17'),
DomainSuffix('sub_18'), DomainSuffix('sub_19'),
DomainSuffix('sub_20'), DomainSuffix('sub_21'),
DomainSuffix('sub_22'), DomainSuffix('sub_23'),
DomainSuffix('sub_24')]
POC
from sqlalchemy import and_
from sqlalchemy import Column
from sqlalchemy import create_engine
from sqlalchemy import ForeignKey
from sqlalchemy import Integer
from sqlalchemy import select
from sqlalchemy import Unicode
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import aliased
from sqlalchemy.orm import relationship
from sqlalchemy.orm import Session
Base = declarative_base()
class DomainSuffix(Base):
__tablename__ = "domain_suffix"
id = Column(Integer, primary_key=True)
suffix = Column(Unicode(128), nullable=False, default=None)
domain_suffix_id__parent = Column(
Integer, ForeignKey("domain_suffix.id"), nullable=True
)
suffixes = relationship("DomainSuffix")
def __repr__(self):
return "DomainSuffix(%r)" % self.suffix
DomainSuffixAlias = aliased(DomainSuffix)
DomainSuffix.domain_suffix__children__max25 = relationship(
DomainSuffixAlias,
primaryjoin=(
and_(
DomainSuffix.id == DomainSuffixAlias.domain_suffix_id__parent,
DomainSuffixAlias.id.in_(
select([DomainSuffixAlias.id])
.where(
DomainSuffix.id
== DomainSuffixAlias.domain_suffix_id__parent
)
.order_by(DomainSuffixAlias.suffix.asc())
.limit(25)
.correlate()
),
)
),
order_by=DomainSuffixAlias.suffix.asc(),
viewonly=True,
)
e = create_engine("sqlite://", echo=True)
Base.metadata.drop_all(e)
Base.metadata.create_all(e)
s = Session(e)
s.add(
DomainSuffix(
suffix="main",
suffixes=[DomainSuffix(suffix="sub_%.2d" % i) for i in range(50)],
)
)
s.commit()
d1 = s.query(DomainSuffix).filter(DomainSuffix.suffix=="main").first()
print(d1.domain_suffix__children__max25)
assert len(d1.domain_suffix__children__max25) <= 25
On Sat, Feb 23, 2019 at 1:22 PM Mike Bayer <[email protected]> wrote:
>
> what happened to the window function thing I showed you ?
>
> On Fri, Feb 22, 2019 at 6:11 PM Jonathan Vanasco <[email protected]>
> wrote:
> >
> > I can't seem to figure this out - I need to define a self-referential
> > relationship on an table with a max of 25 items.
> >
> > I use the following recipe often to handle this across tables, but I can't
> > seem to figure out how to do this with a single table as Aliased object
> > (which I'd use) is not allowed in the primaryjoin.
> >
> >
> > ---
> >
> > class DomainSuffix(DeclaredTable):
> > __tablename__ = 'domain_suffix'
> > id = sa_Column(sa_Integer, primary_key=True)
> > suffix = sa_Column(sa_Unicode(128), nullable=False, default=None)
> > domain_suffix_id__parent = sa_Column(sa_Integer,
> > sa_ForeignKey("domain_suffix.id"), nullable=True)
> >
> >
> > DomainSuffix.domain_suffix__children__max25 = sa_orm_relationship(
> > DomainSuffixAlias,
> > primaryjoin=(
> > and_(
> > DomainSuffix.id == DomainSuffixAlias.domain_suffix_id__parent,
> > DomainSuffixAlias.id.in_(
> > sqlalchemy.select([DomainSuffixAlias.id])\
> > .where(DomainSuffix.id ==
> > DomainSuffixAlias.domain_suffix_id__parent)\
> > .order_by(DomainSuffixAlias.suffix.asc())\
> > .limit(25)\
> > .correlate()
> > )
> > )
> > ),
> > order_by=DomainSuffixAlias.suffix.asc(),
> > viewonly=True,
> > )
> >
> > --
> > 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 [email protected].
> > To post to this group, send email to [email protected].
> > Visit this group at https://groups.google.com/group/sqlalchemy.
> > For more options, visit https://groups.google.com/d/optout.
--
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 [email protected].
To post to this group, send email to [email protected].
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
