On May 23, 2008, at 5:07 PM, Michael Bayer wrote:
>
>> Jeff Putsch wrote:
>> Then I define some selects and execute them:
>>
>> s = select([nis_accounts_table, nis_users_table],
>> from_obj=[nis_accounts_table.join(nis_users_table)]).where(
>> nis_users_table.c.eid != ''
>> )
>>
>> a1 = s.correlate(None).alias()
>> a2 = s.correlate(None).alias()
>>
>> s2 = select([a2.c.domain_id, a2.c.nis_user_id,
>> a2.c.gid, a2.c.gcos, a2.c.shell, a2.c.home,
>> a2.c.terminated, a2.c.reassigned_uid, a2.c.active
>> ], from_obj=[a1.join(a2, (a1.c.eid == a2.c.eid) &
>> (a1.c.uid != a2.c.uid))], use_labels=True)
>>
>> s3 = select([nis_users_table.c.eid], group_by =
>> nis_users_table.c.eid,
>> having = (func.count(nis_users_table.c.uid) > 1) &
>> (nis_users_table.c.eid != '') &
>> ~(nis_users_table.c.uname.like('%_old')))
>>
>> results = s2.where(a1.c.eid.in_(s3) & (a1.c.domain_id ==
>> 41)).execute().fetchall()
>>
>> What I'd really like is for "results" to contain a list of NisAccount
>> objects.
>>
>> If I run the select like this:
>>
>> NisAccount.query().execute(s2.where(a1.c.eid.in_(s3) &
>> (a1.c.domain_id
>> == 41))).fetchall()
>
> execute() is a super ancient method that you should not be using
> (that's what all the warnings mean when you call it). Instead, use
> Query.from_statement(your statement).all().
OK will do. If I understand correctly you're telling me to do
NisAccounts.query().from_statement(...).all()
> The error means that the Query cannot locate one of the necessary
> columns for NisAccount in the row.
That's what I figured.
> In this case I can see that "s2" does not have any column
> corresponding to "nis_accounts.id" in it, so that would be the issue
> here.
I get the same error when I include a2.c.id in "s2". I suspect
it's because of the overriding of statement compilation I'm
doing.
> I would advise trying not to rely upon Query.from_statement() so much
> since it is harder to use. In this case you could say something along
> the lines of:
>
> query.select_from(a1.join(a2,
> <onclause>)).filter(a1.c.eid.in_(s3))....
OK. Now here's where my confusion and lack of experience with SQLAlchemy
really starts to kick in....
> Develop this query iteratively, starting simple with core units of the
> desired SQL and working outwards until you get the results you want.
If I understand correctly,
I need to start with "s" (the innermost query) and do something like:
print NisAccount.query().select_from(a1.join(a2, (a1.c.eid ==
a2.c.eid) & (a1.c.uid != a2.c.uid))).compile()
That gives me the result I expect.
Now, when I add the filter like this:
print NisAccount.query().select_from(a1.join(a2, (a1.c.eid ==
a2.c.eid) & (a1.c.uid != a2.c.uid))).filter(a1.c.eid.in_(s3) &
(a1.c.domain_id == 41)).compile()
I get an infinite recursion error.
I assume that I'm on the right track.
> Also I'd recommend giving 0.5 a try which is better suited to this
> level of Query shenanigans.
Perhaps I've got no choice but to try 0.5 :)
I really appreciate your patience and skill in explaining better
approaches to constructing this type of query using SQLAlchemy.
Thanks,
Jeff.
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~----------~----~----~----~------~----~------~--~---
