Re: [sqlalchemy] How can i use LIKE with an association proxy?
First off, this is unusual because most people would just use the
primary attribute, association proxy isn't buying you anything here:
s.query(User).filter(User._all_emails.any(UserEmail.email.like('foo')))
vs.
s.query(User).filter(User.all_emails.any(UserEmail.email.like('foo')))
same amount of typing!
but anyway, sure it's a bug, this is
https://bitbucket.org/zzzeek/sqlalchemy/issue/3397/association-proxy-any-on-o2m-non-object
fixed in 4f6e9ccae93b9c50298b04135.
On 4/28/15 1:26 PM, Adrian wrote:
Ugh, somehow my reply sent by email nerver arrived here... here's my
code: https://gist.github.com/ThiefMaster/40cd1f91e2a792150496
--
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
mailto:sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com
mailto:sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
--
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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] How can i use LIKE with an association proxy?
Ugh, somehow my reply sent by email nerver arrived here... here's my code: https://gist.github.com/ThiefMaster/40cd1f91e2a792150496 -- 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 http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
[sqlalchemy] How can i use LIKE with an association proxy?
In my user have I have an association proxy so I can access all email
addresses of the user via User.all_emails.
For a simple exact search I simply
.filter(User.all_emails.contains('f...@example.com')).
Is it also possible to use e.g. a LIKE match (besides manually joining the
Emails table and using Email.email.like(...))?
--
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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] How can i use LIKE with an association proxy?
FWIW, another option is to pull in all the addresses and use a class method to filter. class User(): def all_emails_like(self, expression): return [e for e in self.all_emails if regex_match(expression, e)] I've found that depending on your app/db and the size of `.all_emails`, shifting the performance to python can be negligible or even faster. -- 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 http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] How can i use LIKE with an association proxy?
That's the first thing I've tried. Unfortunately it doesn't work...
--- 1 User.find_all(User.all_emails.any(UserEmail.email.like('%adrian%')))
/home/adrian/dev/indico/env/lib/python2.7/site-packages/sqlalchemy/ext/associationproxy.pyc
in any(self, criterion, **kwargs)
367
368
-- 369 if self._value_is_scalar:
370 value_expr = getattr(
371 self.target_class, self.value_attr).has(criterion,
**kwargs)
/home/adrian/dev/indico/env/lib/python2.7/site-packages/sqlalchemy/util/langhelpers.pyc
in __get__(self, obj, cls)
723 if obj is None:
724 return self
-- 725 obj.__dict__[self.__name__] = result = self.fget(obj)
726 return result
727
/home/adrian/dev/indico/env/lib/python2.7/site-packages/sqlalchemy/ext/associationproxy.pyc
in _value_is_scalar(self)
231 def _value_is_scalar(self):
232 return not self._get_property().\
-- 233 mapper.get_property(self.value_attr).uselist
234
235 @util.memoized_property
AttributeError: 'ColumnProperty' object has no attribute 'uselist'
My relationship and association proxy are defined like this:
_all_emails = db.relationship(
'UserEmail',
lazy=True,
viewonly=True,
primaryjoin='User.id == UserEmail.user_id',
collection_class=set,
backref=db.backref('user', lazy=False)
)
On Monday, April 27, 2015 at 5:28:49 PM UTC+2, Michael Bayer wrote:
the has() / any() operators can allow this:
User.all_emails.any(Email.email.like('%foo%'))
it will produce an EXISTS subquery expression, which is not as efficient
in SQL as a regular JOIN.
--
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 javascript:.
To post to this group, send email to sqlal...@googlegroups.com
javascript:.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
--
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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] How can i use LIKE with an association proxy?
On 4/27/15 1:52 PM, Adrian wrote:
That's the first thing I've tried. Unfortunately it doesn't work...
--- 1
User.find_all(User.all_emails.any(UserEmail.email.like('%adrian%')))
wait, what is UserEmail, that's the association. This would be the
endpoint class.
Can you share all three classes and the important parts of their
mappings please ?
/home/adrian/dev/indico/env/lib/python2.7/site-packages/sqlalchemy/ext/associationproxy.pyc
in any(self, criterion, **kwargs)
367
368
-- 369 if self._value_is_scalar:
370 value_expr = getattr(
371 self.target_class,
self.value_attr).has(criterion, **kwargs)
/home/adrian/dev/indico/env/lib/python2.7/site-packages/sqlalchemy/util/langhelpers.pyc
in __get__(self, obj, cls)
723 if obj is None:
724 return self
-- 725 obj.__dict__[self.__name__] = result = self.fget(obj)
726 return result
727
/home/adrian/dev/indico/env/lib/python2.7/site-packages/sqlalchemy/ext/associationproxy.pyc
in _value_is_scalar(self)
231 def _value_is_scalar(self):
232 return not self._get_property().\
-- 233 mapper.get_property(self.value_attr).uselist
234
235 @util.memoized_property
AttributeError: 'ColumnProperty' object has no attribute 'uselist'
My relationship and association proxy are defined like this:
_all_emails = db.relationship(
'UserEmail',
lazy=True,
viewonly=True,
primaryjoin='User.id == UserEmail.user_id',
collection_class=set,
backref=db.backref('user', lazy=False)
)
On Monday, April 27, 2015 at 5:28:49 PM UTC+2, Michael Bayer wrote:
the has() / any() operators can allow this:
User.all_emails.any(Email.email.like('%foo%'))
it will produce an EXISTS subquery expression, which is not as
efficient in SQL as a regular JOIN.
--
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 javascript:.
To post to this group, send email to sqlal...@googlegroups.com
javascript:.
Visit this group at http://groups.google.com/group/sqlalchemy
http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout
https://groups.google.com/d/optout.
--
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
mailto:sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com
mailto:sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
--
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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
