0

I tried to add some sql to a called query via the event before_compile. I check for the type of class that gets queried and see if it is one of the ones that need extra checks.

However I get an error while executing:
AttributeError: Neither 'Label' object nor 'Comparator' object has an attribute '_query_cls'

SQL

SELECT og.* FROM $$$ og JOIN (
SELECT uu.id as u_id, i.id as s_id FROM upload uu JOIN (
        SELECT MAX(u.upload) as maxu, s.id FROM $$$ s
        JOIN upload u ON s.upload_id = u.id
        GROUP BY s.id
    ) i ON uu.upload = i.maxu
) sss ON og.id = sss.s_id AND og.upload_id = sss.u_id

-- Where $$$ is a Base class

Python

@event.listens_for(Query, "before_compile", retval=True)
def do_filtered_load(query):
    class_name = query.column_descriptions[0]["entity"]

    if class_name in pre_classes:
        t = Session.query(func.max(Upload.upload).label("maxu"), class_name.id.label("s_id")).group_by(
            class_name.id).subquery()
        s = Session.query(Upload.id.label("u_id"), t.c.s_id.label("s_id")).join(t.c.maxu == Upload.upload).subquery()

        query = query.join(s, s.c.s_id == class_name.id and class_name.upload_id == s.c.s_u_id)
    return query

What Am I doing wrong?

Ilja Everilä
  • 50,538
  • 7
  • 126
  • 127
MVr
  • 118
  • 1
  • 8
  • Not knowing at what line the error is actually raised, because you did not include the traceback, I'd say `s.c.s_id == class_name.id and class_name.upload_id == s.c.s_u_id` is to blame. Python's boolean **operations** cannot be overloaded: http://stackoverflow.com/questions/42681231/sqlalchemy-boolean-value-of-this-clause-is-not-defined/42695255#42695255 . If it's not, it is the next issue in line. – Ilja Everilä Mar 15 '17 at 21:41
  • Ah sorry, the actual issue (this time) is `.join(t.c.maxu == Upload.upload)`: you're trying to join the boolean SQL expression, not `t`. Simply add `t`: `.join(t, t.c.maxu == Upload.upload)`. – Ilja Everilä Mar 15 '17 at 21:46
  • @IljaEverilä, It probably fixed some of my code, however I still get the same error. According to the Traceback, the issue is on line 85 (The one where "t" gets defined in "do_filtered_load". – MVr Mar 16 '17 at 07:50
  • Interesting. I admit trying your code with `class_name` set to a version of `Section` model (from your [other related question](http://stackoverflow.com/questions/42811880/fill-sqlalchemy-orm-with-custom-query)), and it worked. Probably in your event handler `class_name` is something you did not expect. Try either adding prints, or setting a trace (`import pdb; pdb.set_trace()`, and run your service in foreground) and inspecting the data. – Ilja Everilä Mar 16 '17 at 08:06
  • I got it to work now, kind off... Im getting random versions back (alternating between 2 upload Id's, who both are not the latest, but thats a problem I can probably fix myself.) Thank you very much for you help and time! – MVr Mar 16 '17 at 08:25
  • Try going through the tag [tag:greatest-n-per-group] for how to perform that type of query. A good alternative to what you're trying would be to either use an antijoin, realised either using NOT EXISTS or a leftjoin where right is null. In your case the idea would be to find those section-upload rows for which no greater section-upload row exists. – Ilja Everilä Mar 16 '17 at 09:46
  • Here's a version using not exists: https://paste.ofcode.org/6RWHCMAeZk2wxiKJ6jXWPa – Ilja Everilä Mar 16 '17 at 10:01
  • Coming back to this (through google), the error is in `Session.query(func.max(Upload.upload).label("maxu"), ...)`, the `Session` is not an instance, but the class, so the label object is passed as `self`: https://stackoverflow.com/questions/57759801/attributeerror-type-object-user-has-no-attribute-query-cls – Ilja Everilä Aug 21 '20 at 13:33

0 Answers0