On Wed, Jul 4, 2018 at 3:58 PM, Thomas Levine <_...@thomaslevine.com> wrote: > I have written a couple queries in SQL, and I request assistance > in translating them into SQL Alchemy. > > > > The first query concerns the mail filtering algorithm for a mail user > agent. I want to run something like this SQL. > > select mailfiling.manual_folder_id > from mail > natural join mailfiling > join emailaddress on emailaddress.id = mail.sender_id > where emailaddress.id in (7, 12) > group by emailaddress.id, mailfiling.manual_folder_id > order by count(*) desc limit 1;
we don't have a built-in "natural join" construct but if you make use of normal query.join() techniques, if you have a relationship to link mail to mailfiling it would look like query.join(Mail.filings), something like that. if there are other parts of this query you're unsure about which API to use I can show you that as well if you can be more specific. > > Here are the relevant SQLAlchemy models. > https://thomaslevine.com/scm/pgmh/artifact/e278d413b3dd7661 > > I would probably use it as hybrid_property or a column_property > in the Mail class, returning the folder associated with a particular > mail. A column_property() can't automatically add JOINs to the parent query, it only works to other tables using correlated subqueries, as in the second example at http://docs.sqlalchemy.org/en/latest/orm/mapped_sql_expr.html#using-column-property. for a hybrid_property(), again a hybrid can't automatically add the JOINs, so the expression capabilities of hybrid wouldn't add much usefulness other than adding the "manual_folder_id" column to the query. if you want to render that query as is, as linked from a parent object, you're looking at a plain descriptor at http://docs.sqlalchemy.org/en/latest/orm/mapped_sql_expr.html#using-a-plain-descriptor > > > > The second query concerns the search index for the same mail user agent. > How do I assign the result of SQL functions? This SQL does what I want. > > create table test (t tsvector); > insert into test (t) values ( > setweight(to_tsvector('english', 'Thomas Levine'), 'A') || > setweight(to_tsvector('english', 'blah blah'), 'B') > ); > > I would use it in the initialization of the Mail model from the same > file as above. you need to name the target column explicitly, otherwise it looks mostly like the SQL: test.insert().values( t=( func.setweight(func.to_tsvector('english', 'TL'), 'A') | func.setweight(func.to_tsvector('english', 'y') 'B') ) ) As always, since this is specific to Postgresql, if you just need to run that statement without any fanfare just use the string directly, there's no need to go through all that trouble: conn.execute(""" insert into test (t) values ( setweight(to_tsvector('english', 'Thomas Levine'), 'A') || setweight(to_tsvector('english', 'blah blah'), 'B') ); """) > > -- > 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 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. -- 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 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.
