orm

SQLAlchemy ordering by count on a many to many relationship

by

I haven’t used SQLAlchemy much so I figured I’d give it a shot. I didn’t try to use your models, I just wrote some new ones (similar enough though): likes = db.Table(‘likes’, db.Column(‘user_id’, db.Integer, db.ForeignKey(‘user.id’)), db.Column(‘post_id’, db.Integer, db.ForeignKey(‘post.id’)) ) class User(db.Model): id = db.Column(db.Integer, primary_key=True) username = db.Column(db.String(20)) def __repr__(self): return “<User(‘%s’)>” % self.username class … Read more

Laravel Eloquent sort by relation table column

by

Eager loading uses separate queries so you need join for this: $products = Shop\Product::join(‘shop_products_options as po’, ‘po.product_id’, ‘=’, ‘products.id’) ->orderBy(‘po.pinned’, ‘desc’) ->select(‘products.*’) // just to avoid fetching anything from joined table ->with(‘options’) // if you need options data anyway ->paginate(5); SELECT clause is there in order to not appending joined columns to your Product model. … Read more

Bulk update in SQLAlchemy Core using WHERE

by

Read the Updating and Deleting Rows with Core section of the tutorial. The following code should get you started: from sqlalchemy import bindparam stmt = addresses.update().\ where(addresses.c.id == bindparam(‘_id’)).\ values({ ‘user_id’: bindparam(‘user_id’), ’email_address’: bindparam(’email_address’), }) conn.execute(stmt, [ {‘user_id’: 1, ’email_address’ : ‘jack@yahoo.com’, ‘_id’:1}, {‘user_id’: 1, ’email_address’ : ‘jack@msn.com’, ‘_id’:2}, {‘user_id’: 2, ’email_address’ : ‘www@www.org’, ‘_id’:3}, … Read more

Dapper and SQL Injections

by

How does Dapper help protect against SQL injections? It makes it really, really easy to do fully parameterized data access, without ever needing to either concatenate input. In particular, because you don’t need to jump through lots of “add parameter, set the parameter type, check for null because ADO.NET has sucky null-handling, rinse/repeat for 20 … Read more

When to Use EntityManager.clear()?

by

The articles explains it. Clearing the entity manager empties its associated cache, forcing new database queries to be executed later in the transaction. It’s almost never necessary to clear the entity manager when using a transaction-bound entity manager. I see two reasons to clear: when doing batch processing, in order to avoid having a giant … Read more

How to turn off hbm2ddl?

by

Just omitting hibernate.hbm2ddl.auto defaults to Hibernate not doing anything. From the reference documentation: 1.1.4. Hibernate configuration The hbm2ddl.auto option turns on automatic generation of database schemas directly into the database. This can also be turned off by removing the configuration option, or redirected to a file with the help of the SchemaExport Ant task. Setting … Read more

Dapper.Rainbow VS Dapper.Contrib

by

I’ve been using Dapper for a while now and have wondered what the Contrib and Rainbow projects were all about myself. After a bit of code review, here are my thoughts on their uses: Dapper.Contrib Contrib provides a set of extension methods on the IDbConnection interface for basic CRUD operations: Get Insert Update Delete The … Read more

SQLAlchemy: how to filter date field?

by

In fact, your query is right except for the typo: your filter is excluding all records: you should change the <= for >= and vice versa: qry = DBSession.query(User).filter( and_(User.birthday <= ‘1988-01-17’, User.birthday >= ‘1985-01-17’)) # or same: qry = DBSession.query(User).filter(User.birthday <= ‘1988-01-17’).\ filter(User.birthday >= ‘1985-01-17’) Also you can use between: qry = DBSession.query(User).filter(User.birthday.between(‘1985-01-17’, ‘1988-01-17’))