Can anyone help me with this interesting (to me) relationship definition in
sqla.
I have some already defined data, not by me, that has, for each user, a
foreign table of contact numbers, where the highest contact number is the
one to use.
For example, users:
Harry, 1081, and Bob 1082
mobiles:
1081;"0412590410", 1082;"0426236245", 1082;"0416253647"
With:
SELECT u.id, u.first_name, m1.mobile
FROM model.tbl_users as u
JOIN model.tbl_mobiles as m1
ON m1.id = (select max(m2.id) from model.tbl_mobiles as m2 where
m2.user_id = u.id)
gives:
1081;"Harry";"0412590410"
1082;"Bob";"0416253647"
What I want to do is model a relationship on the user (eventually with
no_list) where the field is simply the highest number.
I have been hacking away at a try:
mobile = relationship("Mobile",
primaryjoin="Mobile.id ==
select([func.max(Mobile.id)]).where(Mobile.user_id == User.id).as_scalar()",
viewonly=True)
but I can't seem to get the join right. I always get:
ArgumentError: Relationship User.mobile could not determine any unambiguous
local/remote column pairs based on join condition and remote_side
arguments. Consider using the remote() annotation to accurately mark those
elements of the join condition that are on the remote side of the
relationship.
I have tried all sorts of somewhat random remote() and foriegn() bits
around the query to no avail.
I have a complete example here:
https://gist.github.com/mianos/42cf15928f27cc9dfde9996d2e593e78
Ideas? I am sure it's possible. At the moment I am just using a "orderby
desc" on the relationship and using [0] to get the first.
--
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.
