{
  "path": "/diesel-view-table-trick.html",
  "site": "at://did:plc:x67qh7v3fd7znbdhauc45ng3/site.standard.publication/3mjcd2t6afe25",
  "$type": "site.standard.document",
  "title": "Diesel.rs Trick: Treat View as Table",
  "updatedAt": "2017-08-23T00:00:00.000Z",
  "publishedAt": "2017-08-23T00:00:00.000Z",
  "textContent": "[Diesel] is a type-safe query builder for Rust.\nIts goal is to give you an idiomatic interface for interacting with your database\nso it's easy to write highly performant and correct queries.\nBut SQL is quite a complex beast,\nand sadly,\nthis also makes Diesel an inherently complex tool.\n\n[Diesel]: https://diesel.rs\n\nOn the implementation side,\none of the most complex parts of Diesel\nis the association handling.\nWe take extra care\nto only allow you\nto query fields from tables\nthat are part of the query\n(either as FROM or as JOIN).\nBut currently, this is limited in some ways.\nE.g., we don't have a way\nto join the same table twice\n(as there is no automatic type-level aliasing\nfor the types that represent the database tables).\n\nTo work around that,\nand similar problems,\nand as an alternative to using the raw SQL escape hatch,\nyou can use this trick:\nCreate a view in a migration,\nand query this view like a table\n(by writing a table! macro call for your view).\n\nExample\n\nLet's imagine we have a simple schema\nwith two tables users and follows\n(this is SQLite syntax):\n\nHere is a SQL query\nto get the content of the follows table\nincluding the names of the follower/followed user:\n\nThis is a query you can't currently express in Diesel\nwithout resorting to weird tricks.\nBut if this is a query you need, you can easily save it as a view:\n\nThen tell Diesel about it:\n\nVoilà!\nYou can now query this like a table.\nLet's describe their structures:\n\nAs you can see,\ntwo records are associated with #[derive(Associations)] and #[belongs_to].\nAfter that\nyou can load the followers for the user\nusing the belonging_to:\n\nPostgres even allows you to call insert, update, and delete on [simple views] like this.\n(You can accomplish similar functionality\nby using INSTEAD OF triggers on SQLite.)\n\n[simple views]: https://www.postgresql.org/docs/9.6/static/sql-createview.html#SQL-CREATEVIEW-UPDATABLE-VIEWS\n\nThis also works great for aggregate queries,\nor to abstract over database-specific operations\nyour application doesn't need to care about.\n\n- - -\n\nThanks to [@keyridan] for adding the associations example to this post!\n\n[@keyridan]: https://github.com/keyridan",
  "canonicalUrl": "https://deterministic.space//diesel-view-table-trick.html"
}