### Lookup and Get Records in Squeryl Source: https://context7.com/squeryl/squeryl/llms.txt Illustrates retrieving records from a table in Squeryl. 'lookup' returns an Option, while 'get' throws an exception if the record is not found. Also shows how to retrieve all rows. ```scala import org.squeryl.PrimitiveTypeMode._ transaction { // Lookup returns Option[T] val maybeAuthor: Option[Author] = LibraryDb.authors.lookup(1L) maybeAuthor match { case Some(author) => println(s"Found: ${author.name}") case None => println("Author not found") } // Get throws NoSuchElementException if not found try { val author: Author = LibraryDb.authors.get(1L) println(s"Author: ${author.name}") } catch { case e: NoSuchElementException => println("Author not found") } // Get all rows val allAuthors: Iterable[Author] = LibraryDb.authors.allRows } ``` -------------------------------- ### SQL Functions and Expressions Source: https://context7.com/squeryl/squeryl/llms.txt Provides examples of using built-in SQL functions and operators within Squeryl queries. ```APIDOC ## SQL Functions and Expressions ### Description Squeryl provides a type-safe DSL for common SQL operations including string manipulation, null handling, and arithmetic. ### Supported Operations - String: `upper()`, `lower()`, `||` (concatenation) - Null Handling: `nvl()`, `isNull`, `isNotNull` - Comparison: `like`, `between`, `===` - Arithmetic: `+`, `-`, `*`, `/` ### Request Example ```scala val discountedPrices = from(LibraryDb.books)(b => select((b.title, b.price * BigDecimal("0.9"))) ) ``` -------------------------------- ### Utilize SQL Functions and Expressions in Squeryl Source: https://context7.com/squeryl/squeryl/llms.txt Provides examples of using built-in SQL functions like upper, lower, nvl, and operators like like, between, isNull, and arithmetic operations within Squeryl queries. ```scala import org.squeryl.PrimitiveTypeMode._ transaction { val upperNames = from(LibraryDb.authors)(a => select(upper(a.name))) val lowerNames = from(LibraryDb.authors)(a => select(lower(a.name))) val pricesWithDefault = from(LibraryDb.books)(b => select(nvl(b.price, BigDecimal("0")))) val matchingBooks = from(LibraryDb.books)(b => where(b.title like "%Foundation%").select(b)) val priceRange = from(LibraryDb.books)(b => where(b.price.between(BigDecimal("10"), BigDecimal("30"))).select(b)) val nullPrices = from(LibraryDb.books)(b => where(b.price.isNull).select(b)) val notNullPrices = from(LibraryDb.books)(b => where(b.price.isNotNull).select(b)) val discountedPrices = from(LibraryDb.books)(b => select((b.title, b.price * BigDecimal("0.9")))) val fullInfo = from(LibraryDb.books, LibraryDb.authors)((b, a) => where(b.authorId === a.id).select(&(b.title || " by " || a.name))) } ``` -------------------------------- ### Configure Squeryl Database Adapters Source: https://context7.com/squeryl/squeryl/llms.txt Shows how to set up SessionFactory for various database engines including H2, PostgreSQL, MySQL, Oracle, SQL Server, SQLite, and Derby. Each configuration requires a specific adapter class to handle dialect-specific SQL. ```scala import org.squeryl.{Session, SessionFactory} import org.squeryl.adapters._ SessionFactory.concreteFactory = Some(() => Session.create( java.sql.DriverManager.getConnection("jdbc:h2:mem:test", "sa", ""), new H2Adapter ) ) SessionFactory.concreteFactory = Some(() => Session.create( java.sql.DriverManager.getConnection("jdbc:postgresql://localhost/mydb", "user", "pass"), new PostgreSqlAdapter ) ) SessionFactory.concreteFactory = Some(() => Session.create( java.sql.DriverManager.getConnection("jdbc:mysql://localhost/mydb", "user", "pass"), new MySQLInnoDBAdapter ) ) SessionFactory.concreteFactory = Some(() => Session.create( java.sql.DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:XE", "user", "pass"), new OracleAdapter ) ) SessionFactory.concreteFactory = Some(() => Session.create( java.sql.DriverManager.getConnection("jdbc:sqlserver://localhost;databaseName=mydb", "user", "pass"), new MSSQLServer ) ) SessionFactory.concreteFactory = Some(() => Session.create( java.sql.DriverManager.getConnection("jdbc:sqlite:mydb.db"), new SQLiteAdapter ) ) SessionFactory.concreteFactory = Some(() => Session.create( java.sql.DriverManager.getConnection("jdbc:derby:mydb;create=true"), new DerbyAdapter ) ) ``` -------------------------------- ### Basic Select and Where Queries in Squeryl Source: https://context7.com/squeryl/squeryl/llms.txt Covers constructing basic Squeryl queries using 'from', 'select', and 'where'. Supports selecting all columns, filtering with various conditions (>, lt, between, like, in), and retrieving specific columns as tuples. ```scala import org.squeryl.PrimitiveTypeMode._ transaction { // Simple select all val allBooks = from(LibraryDb.books)(b => select(b)) // Select with where clause val expensiveBooks = from(LibraryDb.books)(b => where(b.price gt BigDecimal("20")) .select(b) ) // Select specific columns (returns tuple) val bookInfo = from(LibraryDb.books)(b => where(b.price.between(BigDecimal("10"), BigDecimal("30"))) .select((b.title, b.price)) ) // Multiple conditions with and/or val filteredBooks = from(LibraryDb.books)(b => where((b.price gt BigDecimal("15")) and (b.title like "%Foundation%")) .select(b) ) // Using in operator val selectedBooks = from(LibraryDb.books)(b => where(b.id in List(1L, 2L, 3L)) .select(b) ) // Iterate results for (book <- expensiveBooks) { println(s"${book.title}: ${book.price}") } // Get single result (throws if not exactly one) val singleBook = from(LibraryDb.books)(b => where(b.title === "Foundation") .select(b) ).single // Get optional single result val maybeBook: Option[Book] = from(LibraryDb.books)(b => where(b.title === "Unknown") .select(b) ).headOption } ``` -------------------------------- ### Squeryl Inner, Outer, and Complex Joins Source: https://context7.com/squeryl/squeryl/llms.txt Demonstrates various join types in Squeryl, including inner joins using 'from' and 'join' syntax, left outer joins, and complex multi-table joins. It shows how to select specific columns or entire tables and how to handle optional results from outer joins. ```Scala import org.squeryl.PrimitiveTypeMode._ transaction { // Inner join using from with multiple tables val booksWithAuthors = from(LibraryDb.books, LibraryDb.authors)((b, a) => where(b.authorId === a.id) .select((b.title, a.name)) ) // Inner join using join syntax val booksWithAuthors2 = join(LibraryDb.books, LibraryDb.authors)((b, a) => select((b, a)) .on(b.authorId === a.id) ) // Left outer join (authors with optional books) val authorsWithBooks = join(LibraryDb.authors, LibraryDb.books.leftOuter)((a, b) => select((a, b)) .on(a.id === b.map(_.authorId)) ) for ((author, maybeBook) <- authorsWithBooks) { maybeBook match { case Some(book) => println(s"${author.name} wrote ${book.title}") case None => println(s"${author.name} has no books") } } // Multiple joins val complexJoin = join( LibraryDb.authors, LibraryDb.books.leftOuter, LibraryDb.books )((a, b1, b2) => select((a, b1, b2)) .orderBy(a.id) .on(a.id === b1.map(_.authorId), a.id === b2.authorId) ) } ``` -------------------------------- ### Initialize Squeryl Session and Manage Transactions in Scala Source: https://context7.com/squeryl/squeryl/llms.txt Demonstrates how to initialize the Squeryl SessionFactory with a database adapter (H2 in this case) and manage database operations within transactions using `transaction` and `inTransaction` blocks. This ensures proper connection management and transactional integrity. ```scala import org.squeryl.{Session, SessionFactory} import org.squeryl.adapters.H2Adapter // Initialize the session factory (typically done once at application startup) Class.forName("org.h2.Driver") SessionFactory.concreteFactory = Some(() => Session.create( java.sql.DriverManager.getConnection("jdbc:h2:mem:test;DB_CLOSE_DELAY=-1", "sa", ""), new H2Adapter ) ) // Execute code within a transaction import org.squeryl.PrimitiveTypeMode._ transaction { // All database operations here are within a single transaction // Automatically commits on success, rolls back on exception } // Use inTransaction for nested transaction support // Creates new transaction only if none exists inTransaction { // Database operations } ``` -------------------------------- ### Database Adapters Configuration Source: https://context7.com/squeryl/squeryl/llms.txt Details how to configure Squeryl to work with various database systems using specific adapter classes. ```APIDOC ## Database Adapters ### Description Squeryl uses adapter classes to handle dialect-specific SQL syntax. Configure the `SessionFactory` with the appropriate adapter for your database engine. ### Supported Adapters - H2Adapter - PostgreSqlAdapter - MySQLInnoDBAdapter - OracleAdapter - MSSQLServer - SQLiteAdapter - DerbyAdapter ### Request Example ```scala SessionFactory.concreteFactory = Some(() => Session.create( java.sql.DriverManager.getConnection("jdbc:postgresql://localhost/mydb", "user", "pass"), new PostgreSqlAdapter ) ) ``` -------------------------------- ### Query Ordering and Pagination in Squeryl Source: https://context7.com/squeryl/squeryl/llms.txt Explains how to apply ordering and pagination to Squeryl queries. Supports sorting by single or multiple columns in ascending or descending order, and implementing pagination using the 'page' method. ```scala import org.squeryl.PrimitiveTypeMode._ transaction { // Order by single column val booksByPrice = from(LibraryDb.books)(b => select(b) .orderBy(b.price asc) ) // Order by multiple columns val sortedBooks = from(LibraryDb.books)(b => select(b) .orderBy(b.authorId asc, b.price desc) ) // Pagination val page1 = from(LibraryDb.books)(b => select(b) .orderBy(b.title) ).page(0, 10) // offset=0, limit=10 val page2 = from(LibraryDb.books)(b => select(b) .orderBy(b.title) ).page(10, 10) // offset=10, limit=10 // Distinct results val uniquePrices = from(LibraryDb.books)(b => select(b.price) ).distinct // For update (row locking) val lockedBook = from(LibraryDb.books)(b => where(b.id === 1L) .select(b) ).forUpdate.single } ``` -------------------------------- ### Implement Composite Keys with Squeryl Source: https://context7.com/squeryl/squeryl/llms.txt Shows how to define and use entities with composite primary keys in Squeryl. This includes creating entities, inserting data, looking up records, and querying using composite key conditions. Requires Squeryl library. ```Scala import org.squeryl.{Schema, KeyedEntity, PersistenceStatus} import org.squeryl.PrimitiveTypeMode._ import org.squeryl.dsl.CompositeKey2 // Entity with composite primary key case class OrderItem( orderId: Long, productId: Long, quantity: Int ) extends PersistenceStatus { def id = CompositeKey2(orderId, productId) } object OrderDb extends Schema { val orderItems = table[OrderItem] } transaction { OrderDb.create // Insert with composite key val item = OrderDb.orderItems.insert(OrderItem(1L, 100L, 5)) // Lookup by composite key val found = OrderDb.orderItems.lookup(CompositeKey2(1L, 100L)) // Query with composite key conditions val items = from(OrderDb.orderItems)(i => where(i.orderId === 1L and i.productId === 100L) .select(i) ) // Update val updated = item.copy(quantity = 10) OrderDb.orderItems.update(updated) } ``` -------------------------------- ### Define Database Schema and Entities with Squeryl in Scala Source: https://context7.com/squeryl/squeryl/llms.txt Illustrates how to define Scala case classes as entities (e.g., `Author`, `Book`) that map to database tables and how to create a `Schema` object to represent the database structure. It includes defining table relationships, column constraints, and indexes, along with methods to create the schema in the database or print DDL statements. ```scala import org.squeryl.{Schema, KeyedEntity} import org.squeryl.PrimitiveTypeMode._ // Define entity classes class Author(var name: String, var email: String) extends KeyedEntity[Long] { val id: Long = 0 // Auto-incremented primary key } class Book(var title: String, var authorId: Long, var price: BigDecimal) extends KeyedEntity[Long] { val id: Long = 0 } // Define the schema object LibraryDb extends Schema { val authors = table[Author]("authors") val books = table[Book]("books") // Define column constraints and indexes on(authors)(a => declare( a.email is(unique, indexed), a.name is(dbType("varchar(200)")) )) on(books)(b => declare( b.title is(indexed), columns(b.authorId, b.title) are(unique) )) } // Create schema in database transaction { LibraryDb.create } // Print DDL statements without executing LibraryDb.printDdl ``` -------------------------------- ### Dynamic Query Construction with inhibitWhen Source: https://context7.com/squeryl/squeryl/llms.txt Demonstrates how to build a flexible search function that conditionally applies filters to a database query based on the presence of optional input parameters. ```APIDOC ## GET /books/search ### Description Retrieves a list of books based on optional filtering criteria. Clauses are only applied to the SQL query if the corresponding parameter is provided. ### Method GET ### Endpoint /books/search ### Parameters #### Query Parameters - **titleFilter** (String) - Optional - Partial match string for book title. - **minPrice** (BigDecimal) - Optional - Minimum price threshold. - **maxPrice** (BigDecimal) - Optional - Maximum price threshold. - **authorId** (Long) - Optional - Filter by specific author ID. ### Request Example GET /books/search?titleFilter=Foundation&minPrice=10 ### Response #### Success Response (200) - **List[Book]** (Array) - A list of book objects matching the provided criteria. #### Response Example [ { "id": 1, "title": "Foundation", "price": 12.99, "authorId": 5 } ] ``` -------------------------------- ### Configure Lifecycle Callbacks in Squeryl Source: https://context7.com/squeryl/squeryl/llms.txt Illustrates how to define and use lifecycle callbacks (before insert, update, select, etc.) for entities in Squeryl. This allows for automatic auditing, data transformation, and custom entity creation logic. Depends on Squeryl library. ```Scala import org.squeryl.{Schema, KeyedEntity} import org.squeryl.PrimitiveTypeMode._ import org.squeryl.internals.LifecycleEvent class AuditedEntity(var name: String, var createdAt: java.util.Date, var updatedAt: java.util.Date) extends KeyedEntity[Long] { val id: Long = 0 def this() = this("", new java.util.Date(), new java.util.Date()) } object AuditDb extends Schema { val entities = table[AuditedEntity] override def callbacks: Seq[LifecycleEvent] = Seq( // Before insert callback - set creation timestamp beforeInsert[AuditedEntity]() call { e => e.createdAt = new java.util.Date() e.updatedAt = e.createdAt }, // Before update callback - update timestamp beforeUpdate[AuditedEntity]() call { e => e.updatedAt = new java.util.Date() }, // After select callback - logging afterSelect[AuditedEntity]() call { e => println(s"Loaded entity: ${e.id}") }, // Transform callback - modify entity before insert beforeInsert[AuditedEntity]() map { e => new AuditedEntity(e.name.trim.toLowerCase, e.createdAt, e.updatedAt) }, // Factory for creating instances during select factoryFor(entities) is { new AuditedEntity("default", new java.util.Date(), new java.util.Date()) } ) } ``` -------------------------------- ### Squeryl One-to-Many Relationships Source: https://context7.com/squeryl/squeryl/llms.txt Shows how to define and utilize one-to-many relationships between database tables in Squeryl. This includes defining the relationship, configuring foreign key constraints with cascade options, associating entities, and querying related data from both sides of the relationship. ```Scala import org.squeryl.{Schema, KeyedEntity} import org.squeryl.PrimitiveTypeMode._ import org.squeryl.dsl._ class Department(var name: String) extends KeyedEntity[Long] { val id: Long = 0 } class Employee(var name: String, var departmentId: Long) extends KeyedEntity[Long] { val id: Long = 0 } object CompanyDb extends Schema { val departments = table[Department] val employees = table[Employee] // Define one-to-many relationship val departmentToEmployees = oneToManyRelation(departments, employees) .via((d, e) => d.id === e.departmentId) // Configure foreign key constraints departmentToEmployees.foreignKeyDeclaration.constrainReference(onDelete cascade) } transaction { CompanyDb.create val engineering = CompanyDb.departments.insert(new Department("Engineering")) // Use relation to associate employees val employees: OneToMany[Employee] = CompanyDb.departmentToEmployees.left(engineering) // Associate and insert in one step employees.associate(new Employee("Alice", 0)) employees.associate(new Employee("Bob", 0)) // Query related employees for (emp <- employees) { println(s"Employee: ${emp.name}") } // Navigate from employee to department val alice = CompanyDb.employees.where(_.name === "Alice").single val dept: ManyToOne[Department] = CompanyDb.departmentToEmployees.right(alice) println(s"Alice's department: ${dept.single.name}") // Delete all employees in department employees.deleteAll } ``` -------------------------------- ### Squeryl Subqueries in Queries Source: https://context7.com/squeryl/squeryl/llms.txt Illustrates how to use subqueries within Squeryl queries, including usage with 'in', 'exists', and 'notExists' operators in WHERE clauses, as well as scalar subqueries in SELECT and WHERE clauses. It also shows how to construct nested queries. ```Scala import org.squeryl.PrimitiveTypeMode._ transaction { // Subquery with in operator val authorsWithExpensiveBooks = from(LibraryDb.authors)(a => where(a.id in from(LibraryDb.books)(b => where(b.price gt BigDecimal("25")) .select(b.authorId) ) ) .select(a) ) // Subquery with exists val authorsWithBooks = from(LibraryDb.authors)(a => where(exists( from(LibraryDb.books)(b => where(b.authorId === a.id) .select(b) ) )) .select(a) ) // Subquery with notExists val authorsWithoutBooks = from(LibraryDb.authors)(a => where(notExists( from(LibraryDb.books)(b => where(b.authorId === a.id) .select(b) ) )) .select(a) ) // Scalar subquery in where clause val aboveAverageBooks = from(LibraryDb.books)(b => where(b.price gt from(LibraryDb.books)(b2 => compute(avg(b2.price)))) .select(b) ) // Nested queries val nestedQuery = from( from(LibraryDb.books)(b => select(b)) )(b => where(b.price gt BigDecimal("10")).select(b)) } ``` -------------------------------- ### Composite Keys Source: https://context7.com/squeryl/squeryl/llms.txt Illustrates how to use composite primary keys for entities in Squeryl. ```APIDOC ## Composite Keys Support for composite primary keys using CompositeKey classes. ```scala import org.squeryl.{Schema, KeyedEntity, PersistenceStatus} import org.squeryl.PrimitiveTypeMode._ import org.squeryl.dsl.CompositeKey2 // Entity with composite primary key case class OrderItem( orderId: Long, productId: Long, quantity: Int ) extends PersistenceStatus { def id = CompositeKey2(orderId, productId) } object OrderDb extends Schema { val orderItems = table[OrderItem] } transaction { OrderDb.create // Insert with composite key val item = OrderDb.orderItems.insert(OrderItem(1L, 100L, 5)) // Lookup by composite key val found = OrderDb.orderItems.lookup(CompositeKey2(1L, 100L)) // Query with composite key conditions val items = from(OrderDb.orderItems)(i => where(i.orderId === 1L and i.productId === 100L) .select(i) ) // Update val updated = item.copy(quantity = 10) OrderDb.orderItems.update(updated) } ``` ``` -------------------------------- ### Insert Records into Squeryl Tables in Scala Source: https://context7.com/squeryl/squeryl/llms.txt Shows how to insert single records and perform batch inserts into Squeryl tables. The `insert` method on a `Table` object is used, and for single inserts, it returns the entity with its auto-generated ID populated. Batch inserts are more efficient for multiple records. ```scala import org.squeryl.PrimitiveTypeMode._ transaction { // Insert single record val author = LibraryDb.authors.insert(new Author("Isaac Asimov", "asimov@example.com")) println(s"Inserted author with id: ${author.id}") // Auto-generated ID // Batch insert (more efficient for multiple records) LibraryDb.books.insert(List( new Book("Foundation", author.id, BigDecimal("19.99")), new Book("I, Robot", author.id, BigDecimal("14.99")), new Book("The Caves of Steel", author.id, BigDecimal("16.99")) )) } ``` -------------------------------- ### Squeryl Set Operations (Union, Intersect, Except) Source: https://context7.com/squeryl/squeryl/llms.txt Demonstrates how to combine the results of multiple Squeryl queries using set operations. It covers 'union' (distinct results), 'unionAll' (all results including duplicates), 'intersect' (common results), and 'except' (results in the first query but not the second). ```Scala import org.squeryl.PrimitiveTypeMode._ transaction { val cheapBooks = from(LibraryDb.books)(b => where(b.price lt BigDecimal("15")) .select(b) ) val recentBooks = from(LibraryDb.books)(b => where(b.id gt 100L) .select(b) ) // Union (removes duplicates) val combined = cheapBooks union recentBooks // Union all (keeps duplicates) val combinedAll = cheapBooks unionAll recentBooks // Intersect (common records) val cheapAndRecent = cheapBooks intersect recentBooks // Except (in first but not second) val cheapButNotRecent = cheapBooks except recentBooks } ``` -------------------------------- ### Lifecycle Callbacks Source: https://context7.com/squeryl/squeryl/llms.txt Explains how to define and utilize lifecycle callbacks for entity events such as insert, update, and select. ```APIDOC ## Lifecycle Callbacks Define callbacks for entity lifecycle events like insert, update, delete, and select. ```scala import org.squeryl.{Schema, KeyedEntity} import org.squeryl.PrimitiveTypeMode._ import org.squeryl.internals.LifecycleEvent class AuditedEntity(var name: String, var createdAt: java.util.Date, var updatedAt: java.util.Date) extends KeyedEntity[Long] { val id: Long = 0 def this() = this("", new java.util.Date(), new java.util.Date()) } object AuditDb extends Schema { val entities = table[AuditedEntity] override def callbacks: Seq[LifecycleEvent] = Seq( // Before insert callback - set creation timestamp beforeInsert[AuditedEntity]() call { e => e.createdAt = new java.util.Date() e.updatedAt = e.createdAt }, // Before update callback - update timestamp beforeUpdate[AuditedEntity]() call { e => e.updatedAt = new java.util.Date() }, // After select callback - logging afterSelect[AuditedEntity]() call { e => println(s"Loaded entity: ${e.id}") }, // Transform callback - modify entity before insert beforeInsert[AuditedEntity]() map { e => new AuditedEntity(e.name.trim.toLowerCase, e.createdAt, e.updatedAt) }, // Factory for creating instances during select factoryFor(entities) is { new AuditedEntity("default", new java.util.Date(), new java.util.Date()) } ) } ``` ``` -------------------------------- ### Update Records in Squeryl Tables in Scala Source: https://context7.com/squeryl/squeryl/llms.txt Demonstrates various methods for updating records in Squeryl tables. This includes updating a single `KeyedEntity`, performing partial updates using `where` clauses for efficiency, and executing batch updates on multiple records. ```scala import org.squeryl.PrimitiveTypeMode._ transaction { // Update single entity (requires KeyedEntity) val book = LibraryDb.books.where(_.title === "Foundation").single book.price = BigDecimal("24.99") LibraryDb.books.update(book) // Partial update with where clause (more efficient) update(LibraryDb.books)(b => where(b.authorId === 1L) .set(b.price := b.price * BigDecimal("1.1")) // 10% price increase ) // Update with subquery in set clause update(LibraryDb.books)(b => where(b.id === 1L) .set(b.price := from(LibraryDb.books)(b2 => compute(avg(b2.price)))) ) // Batch update val booksToUpdate = LibraryDb.books.where(_.price lt BigDecimal("15")).toList LibraryDb.books.update(booksToUpdate.map(b => { b.price = BigDecimal("15"); b })) } ``` -------------------------------- ### Aggregations and Grouping in Squeryl Source: https://context7.com/squeryl/squeryl/llms.txt Details how to perform aggregate functions like count, sum, average, min, and max in Squeryl. Also covers grouping results by a column and applying a 'having' clause to filter grouped results. ```scala import org.squeryl.PrimitiveTypeMode._ transaction { // Count all records val totalBooks: Long = from(LibraryDb.books)(b => compute(count)) // Count with condition val expensiveCount: Long = from(LibraryDb.books)(b => where(b.price gt BigDecimal("20")) .compute(count) ) // Count distinct val uniqueAuthors: Long = from(LibraryDb.books)(b => compute(countDistinct(b.authorId))) // Sum, Avg, Min, Max val totalValue: Option[BigDecimal] = from(LibraryDb.books)(b => compute(sum(b.price))) val avgPrice: Option[BigDecimal] = from(LibraryDb.books)(b => compute(avg(b.price))) val minPrice: Option[BigDecimal] = from(LibraryDb.books)(b => compute(min(b.price))) val maxPrice: Option[BigDecimal] = from(LibraryDb.books)(b => compute(max(b.price))) // Group by with aggregation val booksByAuthor = from(LibraryDb.books)(b => groupBy(b.authorId) .compute(count, sum(b.price)) ) for (group <- booksByAuthor) { println(s"Author ${group.key}: ${group.measures._1} books, total: ${group.measures._2}") } // Group by with having clause val prolificAuthors = from(LibraryDb.books)(b => groupBy(b.authorId) .having(count gt 5L) .compute(count) ) } ``` -------------------------------- ### Define Many-to-Many Relationships with Squeryl Source: https://context7.com/squeryl/squeryl/llms.txt Demonstrates how to define and manage many-to-many relationships in Squeryl using an association table. It covers associating entities, querying related data, and accessing custom association objects. Dependencies include Squeryl library. ```Scala import org.squeryl.{Schema, KeyedEntity} import org.squeryl.PrimitiveTypeMode._ import org.squeryl.dsl._ class Student(var name: String) extends KeyedEntity[Long] { val id: Long = 0 } class Course(var title: String) extends KeyedEntity[Long] { val id: Long = 0 } // Association class for the join table class Enrollment(var studentId: Long, var courseId: Long, var grade: Option[String]) extends KeyedEntity[Long] { val id: Long = 0 } object UniversityDb extends Schema { val students = table[Student] val courses = table[Course] val enrollments = table[Enrollment] // Define many-to-many relationship val studentCourses = manyToManyRelation(students, courses, "enrollments") .via[Enrollment]((s, c, e) => (e.studentId === s.id, e.courseId === c.id)) } transaction { UniversityDb.create val alice = UniversityDb.students.insert(new Student("Alice")) val math = UniversityDb.courses.insert(new Course("Mathematics")) val physics = UniversityDb.courses.insert(new Course("Physics")) // Get courses for a student (ManyToMany relation) val aliceCourses: ManyToMany[Course, Enrollment] = UniversityDb.studentCourses.left(alice) // Associate with custom enrollment data val enrollment = new Enrollment(0, 0, Some("A")) aliceCourses.associate(math, enrollment) // Simple association (creates default Enrollment) aliceCourses.associate(physics) // Query courses for student for (course <- aliceCourses) { println(s"Alice is enrolled in: ${course.title}") } // Get students for a course val mathStudents: ManyToMany[Student, Enrollment] = UniversityDb.studentCourses.right(math) // Access association objects for ((course, enrollment) <- aliceCourses.associationMap) { println(s"${course.title}: Grade = ${enrollment.grade.getOrElse("N/A")}") } // Dissociate aliceCourses.dissociate(physics) // Dissociate all aliceCourses.dissociateAll } ``` -------------------------------- ### Optimistic Concurrency Control Source: https://context7.com/squeryl/squeryl/llms.txt Explains how to implement optimistic locking in Squeryl to prevent data conflicts during concurrent updates. ```APIDOC ## Optimistic Concurrency Control ### Description Implements optimistic locking by extending the `Optimistic` trait. This ensures that updates fail with a `StaleUpdateException` if the record has been modified by another transaction since it was read. ### Usage - Extend `KeyedEntity[T]` and `Optimistic` traits. - Use `update` for standard checked updates. - Use `forceUpdate` to bypass version checking. ### Request Example ```scala class Product(var name: String, var stock: Int) extends KeyedEntity[Long] with Optimistic { val id: Long = 0 } ``` ``` -------------------------------- ### Many-to-Many Relationships Source: https://context7.com/squeryl/squeryl/llms.txt Demonstrates how to define and manage many-to-many relationships using an association table with custom data. ```APIDOC ## Many-to-Many Relationships Define many-to-many relationships with an association table. ```scala import org.squeryl.{Schema, KeyedEntity} import org.squeryl.PrimitiveTypeMode._ import org.squeryl.dsl._ class Student(var name: String) extends KeyedEntity[Long] { val id: Long = 0 } class Course(var title: String) extends KeyedEntity[Long] { val id: Long = 0 } // Association class for the join table class Enrollment(var studentId: Long, var courseId: Long, var grade: Option[String]) extends KeyedEntity[Long] { val id: Long = 0 } object UniversityDb extends Schema { val students = table[Student] val courses = table[Course] val enrollments = table[Enrollment] // Define many-to-many relationship val studentCourses = manyToManyRelation(students, courses, "enrollments") .via[Enrollment]((s, c, e) => (e.studentId === s.id, e.courseId === c.id)) } transaction { UniversityDb.create val alice = UniversityDb.students.insert(new Student("Alice")) val math = UniversityDb.courses.insert(new Course("Mathematics")) val physics = UniversityDb.courses.insert(new Course("Physics")) // Get courses for a student (ManyToMany relation) val aliceCourses: ManyToMany[Course, Enrollment] = UniversityDb.studentCourses.left(alice) // Associate with custom enrollment data val enrollment = new Enrollment(0, 0, Some("A")) aliceCourses.associate(math, enrollment) // Simple association (creates default Enrollment) aliceCourses.associate(physics) // Query courses for student for (course <- aliceCourses) { println(s"Alice is enrolled in: ${course.title}") } // Get students for a course val mathStudents: ManyToMany[Student, Enrollment] = UniversityDb.studentCourses.right(math) // Access association objects for ((course, enrollment) <- aliceCourses.associationMap) { println(s"${course.title}: Grade = ${enrollment.grade.getOrElse("N/A")}") } // Dissociate aliceCourses.dissociate(physics) // Dissociate all aliceCourses.dissociateAll } ``` ``` -------------------------------- ### Delete Records in Squeryl Source: https://context7.com/squeryl/squeryl/llms.txt Demonstrates how to delete records from a table in Squeryl. Supports deletion by primary key, using a where clause for conditional deletion, or by providing a query object. ```scala import org.squeryl.PrimitiveTypeMode._ transaction { // Delete by primary key val deleted: Boolean = LibraryDb.books.delete(1L) // Delete with where clause val rowsDeleted: Int = LibraryDb.books.deleteWhere(b => b.price lt BigDecimal("10")) // Delete from query val cheapBooks = from(LibraryDb.books)(b => where(b.price lt BigDecimal("5")).select(b)) LibraryDb.books.delete(cheapBooks) } ``` -------------------------------- ### Build dynamic queries with inhibitWhen in Squeryl Source: https://context7.com/squeryl/squeryl/llms.txt This snippet demonstrates how to construct a search function that conditionally filters database records based on optional parameters. It uses the inhibitWhen method to ignore specific query criteria if the corresponding input parameter is empty. ```scala import org.squeryl.PrimitiveTypeMode._ def searchBooks( titleFilter: Option[String], minPrice: Option[BigDecimal], maxPrice: Option[BigDecimal], authorId: Option[Long] ): List[Book] = { transaction { from(LibraryDb.books)(b => where( (b.title like titleFilter.map(t => s"%$t%").getOrElse("")).inhibitWhen(titleFilter.isEmpty) and (b.price gte minPrice.getOrElse(BigDecimal("0"))).inhibitWhen(minPrice.isEmpty) and (b.price lte maxPrice.getOrElse(BigDecimal("999999"))).inhibitWhen(maxPrice.isEmpty) and (b.authorId === authorId.getOrElse(0L)).inhibitWhen(authorId.isEmpty) ) .select(b) ).toList } } val allBooks = searchBooks(None, None, None, None) val foundationBooks = searchBooks(Some("Foundation"), None, None, None) val cheapBooks = searchBooks(None, None, Some(BigDecimal("15")), None) val authorBooks = searchBooks(None, Some(BigDecimal("10")), Some(BigDecimal("30")), Some(1L)) ``` -------------------------------- ### Implement Optimistic Concurrency Control in Squeryl Source: https://context7.com/squeryl/squeryl/llms.txt Demonstrates how to use the Optimistic trait to prevent concurrent update conflicts. It shows entity definition, transaction handling, and how StaleUpdateException is thrown when a version mismatch occurs. ```scala import org.squeryl.{Schema, KeyedEntity, Optimistic, StaleUpdateException} import org.squeryl.PrimitiveTypeMode._ class Product(var name: String, var stock: Int) extends KeyedEntity[Long] with Optimistic { val id: Long = 0 } object InventoryDb extends Schema { val products = table[Product] } transaction { InventoryDb.create val product = InventoryDb.products.insert(new Product("Widget", 100)) product.stock = 90 InventoryDb.products.update(product) val productCopy = InventoryDb.products.lookup(product.id).get transaction { val p = InventoryDb.products.lookup(product.id).get p.stock = 85 InventoryDb.products.update(p) } try { productCopy.stock = 80 InventoryDb.products.update(productCopy) } catch { case e: StaleUpdateException => println("Concurrent modification detected, refresh and retry") } InventoryDb.products.forceUpdate(productCopy) } ``` === COMPLETE CONTENT === This response contains all available snippets from this library. No additional content exists. Do not make further requests.