Masonite ORM

https://orm.masoniteproject.com
Admin
Masonite ORM is a Python ORM built for the Masonite web framework with support for models,...
Tokens:37,023
Snippets:396
Trust Score:6.1
Update:7 months ago
Show doc for...
Tokens:
Raw
### Configure Database Connections using DB URL Helper

Source: https://orm.masoniteproject.com/installation

Demonstrates using the `db_url` helper function from `masoniteorm.config` to configure database connections, leveraging environment variables or direct URL strings. This simplifies connection setup by allowing a single URL to define connection parameters.

```python
from masoniteorm.config import db_url

DATABASES = {
    "default": "mysql",
    "mysql": db_url(),
}
```

```python
import os
from masoniteorm.config import db_url

DATABASES = {
    "default": "mysql",
    "mysql": db_url(os.getenv("DB_URL")),
}
```

```python
from masoniteorm.config import db_url

DATABASES = {
    "default": "mysql",
    "mysql": db_url("mysql://root:@127.0.0.1:3306/masonite"),
}
```

```python
from masoniteorm.config import db_url

DATABASES = {
    "default": "mysql",
    "mysql": db_url("mysql://root:@127.0.0.1:3306/masonite", log_queries=True, prefix="", options={}),
}
```

```python
db_url("sqlite://")
db_url("sqlite://masonite.sqlite3")
```

--------------------------------

### Configure MSSQL Connection Options

Source: https://orm.masoniteproject.com/installation

Provides an example configuration for connecting to Microsoft SQL Server (MSSQL) using Masonite ORM. It includes common connection parameters and a detailed `options` dictionary for advanced settings like trusted connections, instance names, and driver specifications.

```python
"mssql": {
    "host": "127.0.0.1",
    "driver": "mssql",
    "database": "masonite",
    "user": "root",
    "password": "",
    "port": 1433,
    "log_queries": False,
    "options": {
      "trusted_connection": "Yes",
      "integrated_security": "sspi",
      "instance": "SQLExpress",
      "authentication": "ActiveDirectoryPassword",
      "driver": "ODBC Driver 17 for SQL Server",
      "connection_timeout": 15,
    }
  },
```

--------------------------------

### Masonite ORM Database Connection Resolver

Source: https://orm.masoniteproject.com/orator-to-masonite-orm

Example of setting up the database connection resolver in Masonite ORM. It uses ConnectionResolver to manage connections based on the DATABASES configuration.

```python
from masoniteorm.connections import ConnectionResolver

DATABASES = {
  # ...
}

DB = ConnectionResolver().set_connection_details(DATABASES)
```

--------------------------------

### Querying Records with Masonite Models

Source: https://orm.masoniteproject.com/models

Examples of retrieving single records using first() or find(), and fetching multiple records using where() clauses.

```python
user = User.first()
users = User.all()
active_users = User.where('active', 1).first()

# Accessing attributes
user = User.find(1)
print(user.name)
```

--------------------------------

### Masonite ORM Database Configuration

Source: https://orm.masoniteproject.com/orator-to-masonite-orm

Example configuration dictionary for Masonite ORM, defining connection details for multiple database drivers like MySQL, PostgreSQL, SQLite, and MSSQL. It utilizes environment variables for sensitive information.

```python
import os

DATABASES = {
    'default': 'mysql',
    'mysql': {
        'driver': 'mysql',
        'host': os.getenv('MYSQL_DATABASE_HOST'),
        'user': os.getenv('MYSQL_DATABASE_USER'),
        'password': os.getenv('MYSQL_DATABASE_PASSWORD'),
        'database': os.getenv('MYSQL_DATABASE_DATABASE'),
        'port': os.getenv('MYSQL_DATABASE_PORT'),
        'prefix': '',
        'options': {
            'charset': 'utf8mb4',
        },
        'log_queries': True
    },
    'postgres': {
        'driver': 'postgres',
        'host': os.getenv('POSTGRES_DATABASE_HOST'),
        'user': os.getenv('POSTGRES_DATABASE_USER'),
        'password': os.getenv('POSTGRES_DATABASE_PASSWORD'),
        'database': os.getenv('POSTGRES_DATABASE_DATABASE'),
        'port': os.getenv('POSTGRES_DATABASE_PORT'),
        'prefix': '',
        'log_queries': True
    },
    'sqlite': {
        'driver': 'sqlite',
        'database': 'orm.sqlite3',
        'prefix': '',
        'log_queries': True
    },
    'mssql': {
        'driver': 'mssql',
        'host': os.getenv('MSSQL_DATABASE_HOST'),
        'user': os.getenv('MSSQL_DATABASE_USER'),
        'password': os.getenv('MSSQL_DATABASE_PASSWORD'),
        'database': os.getenv('MSSQL_DATABASE_DATABASE'),
        'port': os.getenv('MSSQL_DATABASE_PORT'),
        'prefix': '',
        'log_queries': True
    },
}
```

--------------------------------

### Configure Database Connections in Python

Source: https://orm.masoniteproject.com/installation

Defines database connection details within a Python configuration file. This includes setting up multiple database connections (e.g., default, mysql, postgres, sqlite) with their respective host, driver, database name, user, password, and port. It also demonstrates how to import and initialize the ConnectionResolver.

```python
# config/database.py
DATABASES = {
  "default": "mysql",
  "mysql": {
    "host": "127.0.0.1",
    "driver": "mysql",
    "database": "masonite",
    "user": "root",
    "password": "",
    "port": 3306,
    "log_queries": False,
    "options": {
      #
    }
  },
  "postgres": {
    "host": "127.0.0.1",
    "driver": "postgres",
    "database": "masonite",
    "user": "root",
    "password": "",
    "port": 5432,
    "log_queries": False,
    "options": {
      #
    }
  },
  "sqlite": {
    "driver": "sqlite",
    "database": "masonite.sqlite3",
  }
}

from masoniteorm.connections import ConnectionResolver

DB = ConnectionResolver().set_connection_details(DATABASES)
```

--------------------------------

### Orator Database Connection Resolver

Source: https://orm.masoniteproject.com/orator-to-masonite-orm

Example of setting up the database connection resolver in Orator ORM. It uses DatabaseManager to manage connections based on the DATABASES configuration.

```python
from orator import DatabaseManager, Model

DATABASES = {
  # ...
}

DB = DatabaseManager(DATABASES)
Model.set_connection_resolver(DB)
```

--------------------------------

### Usage Examples for Dynamic Status Scopes

Source: https://orm.masoniteproject.com/tips-and-tricks

Demonstrates how to use the dynamically generated scope methods for filtering. Examples show filtering by Enum values and by string representations of status values for both Company and Invoice models.

```python
from .company import Company
from .invoice import Invoice

# using the created method
confirmed_companys = Company.is_confirmed().get()

# using a string
pending_invoices = Invoice.has_status("payment_pending").get()

# using an Enum is better than a string 
processed_invoices = Invoice.has_status(InvoiceStatus.PAYMENT_PROCESSED).get()
```

--------------------------------

### Execute Raw SQL Queries in Masonite ORM

Source: https://orm.masoniteproject.com/installation

Demonstrates executing raw SQL statements using the connection resolver. Includes support for query bindings to prevent SQL injection and optional connection targeting.

```python
from config.database import DB

# Basic query
result = DB.statement("select * from users where users.active = 1")

# Query with bindings
result = DB.statement("select * from users where users.active = ?", [1])
```

--------------------------------

### Generate Qmark SQL Query with to_qmark()

Source: https://orm.masoniteproject.com/query-builder

The `to_qmark()` method generates a SQL query with placeholders ('?') for values, suitable for direct database execution. It returns the qmark query string and resets the query builder, so it should not be followed by methods like `get()`.

```python
builder.table('users').count('salary').where('age', 18).to_qmark()
#== SELECT COUNT(`users`.`salary`) AS salary FROM `users` WHERE `users`.`age` = '?'
```

--------------------------------

### Polymorphic One-to-Many Relationship Setup in Python

Source: https://orm.masoniteproject.com/models

Demonstrates the Python model setup for a polymorphic one-to-many relationship in Masonite ORM. This involves defining `morph_many` relationships on models that can be related to a single polymorphic table (e.g., 'likes').

```python
from masoniteorm.relationships import morph_to, morph_many

class Likes(Model):

  @morph_to
  def record(self):
    return

class Article(Model):
  
  @morph_many("record_type", "record_id")
  def likes(self):
    return Like

class Comment(Model):

  @morph_many("record_type", "record_id")
  def likes(self):
    return Like
```

--------------------------------

### LEFT JOIN Clause in Python

Source: https://orm.masoniteproject.com/query-builder

Provides an example of how to perform a LEFT JOIN between tables using the 'left_join' method, specifying the tables and join conditions.

```python
builder.table('users').left_join('table1', 'table2.id', '=', 'table1.table_id')
```

--------------------------------

### Collection Utility Methods

Source: https://orm.masoniteproject.com/collections

Examples of common collection operations including retrieving raw data, calculating averages, chunking data, flattening structures, and filtering items.

```python
# Retrieve all items
users = User.get().all()

# Calculate average
Collection([1, 2, 3, 4, 5]).avg() #== 3
average_price = Product.get().avg('price')

# Chunking
chunks = Collection([1, 2, 3, 4, 5, 6, 7]).chunk(2).serialize() #== [[1, 2], [3, 4], [5, 6], [7]]

# Flattening
flattened = Collection([1, 2, [3, 4, 5, {'foo': 'bar'}]]).flatten().all() #== [1, 2, 3, 4, 5, 'bar']

# Filtering
filtered = Collection([1, 2, 3, 4]).filter(lambda item: item > 2).all() #== [3, 4]
```

--------------------------------

### Install Masonite ORM via Pip

Source: https://orm.masoniteproject.com/installation

Installs the masonite-orm package using pip. This is the primary method for adding the ORM to your Python project.

```shell
$ pip install masonite-orm
```

--------------------------------

### Eager Loading Relationships in Masonite ORM (Python)

Source: https://orm.masoniteproject.com/models

Shows how to eager load related models in Masonite ORM to optimize database queries. The example contrasts the inefficient method of fetching related data with multiple queries against the optimized approach using `with_().get()`.

```python
users = User.all()
for user in users:
    user.phone

# Optimized with eager loading:
users = User.with_('phone').get()
for user in users:
    user.phone
```

--------------------------------

### Advanced Queries with Sub-queries in Python

Source: https://orm.masoniteproject.com/models

Constructs advanced queries using lambda expressions for sub-queries. This example selects users who are active and do not have a 'deleted_at' timestamp.

```python
from app.models.User import User

users = User.where(lambda q: q.where('active', 1).where_null('deleted_at'))
# == SELECT * FROM `users` WHERE (`active` = '1' AND `deleted_at` IS NULL)
```

--------------------------------

### Override Configuration File Path via Environment Variable

Source: https://orm.masoniteproject.com/installation

Shows how to specify a custom path for the database configuration file using the `DB_CONFIG_PATH` environment variable. This allows flexibility in organizing project configuration files.

```shell
DB_CONFIG_PATH=app/options/db.py
```

--------------------------------

### Configure Query Logging in Masonite ORM

Source: https://orm.masoniteproject.com/installation

Explains how to route generated SQL queries to Python logging handlers. This allows developers to monitor database activity by outputting queries to the terminal or writing them to log files.

```python
import logging

logger = logging.getLogger('masoniteorm.connection.queries')
logger.setLevel(logging.DEBUG)

handler = logging.StreamHandler()
file_handler = logging.FileHandler('queries.log')

logger.addHandler(handler)
logger.addHandler(file_handler)
```

--------------------------------

### Configure Connection Pooling in Masonite ORM

Source: https://orm.masoniteproject.com/installation

Shows the configuration structure for enabling connection pooling in the database configuration file. This improves performance by maintaining a pool of active connections for MySQL and Postgres databases.

```python
"mysql": {
    "host": "127.0.0.1",
    "driver": "mysql",
    "database": "masonite",
    "connection_pooling_enabled": True,
    "connection_pooling_max_size": 100,
    "connection_pooling_min_size": 2
}
```

--------------------------------

### Manage Database Transactions in Masonite ORM

Source: https://orm.masoniteproject.com/installation

Demonstrates how to perform manual transaction control using commit/rollback, or automatic transaction management using a context manager. These methods ensure data integrity by allowing operations to be grouped and reverted if an exception occurs.

```python
from config.database import DB

# Manual transaction
DB.begin_transaction()
User.create({..})
DB.commit()

# Context manager
with DB.transaction():
  User.create({..})
```

--------------------------------

### Override Configuration File Path with Command-Line Flag

Source: https://orm.masoniteproject.com/installation

Demonstrates how to override the database configuration file path directly when running Masonite ORM commands using the `--config` or `-C` flags. This provides an alternative to using environment variables for specifying the configuration file.

```shell
masonite-orm migrate -C app/options/db.py
```

--------------------------------

### Instantiating the Query Builder

Source: https://orm.masoniteproject.com/query-builder

Demonstrates how to initialize the QueryBuilder class and specify database connections. This is the entry point for performing raw database operations.

```python
from masoniteorm.query import QueryBuilder

# Basic instantiation
builder = QueryBuilder().table("users")

# Specifying connection on the fly
builder = QueryBuilder().on('staging').table("users")
```

--------------------------------

### Masonite ORM Model Definition

Source: https://orm.masoniteproject.com/orator-to-masonite-orm

Defines a simple User model in Masonite ORM. It inherits from masoniteorm.models.Model, allowing direct import and usage without explicit resolver setup.

```python
from masoniteorm.models import Model

class User(Model):
    pass
```

--------------------------------

### Create and Bulk Create Records in Python

Source: https://orm.masoniteproject.com/query-builder

Demonstrates how to insert single records into a table using a dictionary with the `create` method, and how to insert multiple records efficiently using a list of dictionaries with the `bulk_create` method.

```python
builder.create({"name": "Joe", "active": 1})
builder.bulk_create([
    {"name": "Joe", "active": 1},
    {"name": "John", "active": 0},
    {"name": "Bill", "active": 1},
])
```

--------------------------------

### Orator Fetching Builder Relations

Source: https://orm.masoniteproject.com/orator-to-masonite-orm

Example of fetching a related model's builder in Orator ORM. The `user.phone()` call delays the relationship execution, allowing further query modifications before fetching.

```python
user = User.find(1)
user.phone().where('active', 1).get()
```

--------------------------------

### Masonite Fetching Builder Relations

Source: https://orm.masoniteproject.com/orator-to-masonite-orm

Example of fetching a related model's builder in Masonite ORM. The `user.related('phone')` method replaces Orator's direct relationship call for delayed execution and query modification.

```python
user = User.find(1)
user.related('phone').where('active', 1).get()
```

--------------------------------

### Working with Collections

Source: https://orm.masoniteproject.com/models

Demonstrates how to handle multiple query results using collections, including iteration and using helper methods like pluck().

```python
users = User.where('active', 1).get()
for user in users:
  print(user.name)

# Finding multiple by primary key
users = User.find([1,2,3])

# Using collection methods
user_emails = User.where('active', 1).get().pluck('email')
```

--------------------------------

### Model Static Method Invocation

Source: https://orm.masoniteproject.com/white-page

Demonstrates the difference between manual instantiation and the cleaner static method syntax enabled by the ORM's Meta Class magic.

```python
class User(Model):
    pass

# Manual instantiation
result = User().where('...')

# Static method call enabled by Meta Class
result = User.where('...')
```

--------------------------------

### Order Query Results Using Raw SQL in Python

Source: https://orm.masoniteproject.com/query-builder

Explains how to use `order_by_raw` to apply custom, raw SQL expressions for ordering query results, providing flexibility for complex sorting requirements.

```python
builder.order_by_raw("name asc")
```

--------------------------------

### Execute Raw SQL Queries in Python

Source: https://orm.masoniteproject.com/query-builder

Shows how to execute raw SQL queries for selects, wheres, and general statements using `select_raw`, `where_raw`, and `statement`. It also covers passing query bindings and using the `Raw` expression class for updates.

```python
builder.table('users').select_raw("COUNT(`username`) as username").where_raw("`username` = 'Joe'").get()
builder.statement("select count(*) from users where active = 1")
builder.statement("select count(*) from users where active = '?'", [1])
from masoniteorm.expressions import Raw
builder.update({
    "name": Raw('"alias"')
})
```

--------------------------------

### Query Builder Chaining and Execution

Source: https://orm.masoniteproject.com/white-page

Illustrates how the QueryBuilder handles method chaining and returns either a Collection or a Model instance upon execution.

```python
# Initial query builder creation
user = User.where('age', 18)

# Chaining methods on the QueryBuilder
user = User.where('age', 18).where('name', 'Joe').limit(1)

# Returning a collection of results
results = User.where('age', 18).where('name', 'Joe').limit(1).get()

# Returning a single model instance
user = User.where('age', 18).where('name', 'Joe').limit(1).first()
```

--------------------------------

### Specify Selects on `get()` Method in Python

Source: https://orm.masoniteproject.com/models

Enables specifying custom columns for selection directly within the `get()` method. This works in conjunction with `where` clauses to filter and select specific data.

```python
store.where("active", 1).get(["username", "administrator as is_admin"])
#== SELECT `username`, `administrator` as is_admin FROM `users` WHERE `active` = 1
```

--------------------------------

### Applying Where Clauses

Source: https://orm.masoniteproject.com/query-builder

Demonstrates how to filter results using various where conditions, including equality, comparison operators, null checks, and list-based filtering.

```python
builder.table('users').where('username', 'Joe').where('age', 18).get()
builder.table('users').where({"username": "Joe", "age": 18}).get()
builder.table('users').where('age', '>', 18).get()
builder.table('users').where_null('admin').get()
builder.table('users').where_in('age', [18, 21, 25]).get()
```

--------------------------------

### Define Limit Syntax

Source: https://orm.masoniteproject.com/white-page

Shows how database-specific limit syntax is abstracted into methods within individual Grammar subclasses.

```python
# MySQLGrammar
def limit_string(self):
  return "LIMIT {limit}"
```

```python
# MSSQLGrammar
def limit_string(self):
  return "TOP {limit}"
```

--------------------------------

### Get unique items from a collection

Source: https://orm.masoniteproject.com/collections

Returns a collection containing only unique items. For dictionaries or objects, a specific key can be provided to determine uniqueness.

```python
collection = Collection([1, 1, 2, 2, 3, 4, 2])
unique = collection.unique()
unique.all() #== [1, 2, 3, 4]

# Unique by key
collection = Collection([
    {'name': 'Sam', 'role': 'admin'},
    {'name': 'Joe', 'role': 'basic'},
    {'name': 'Joe', 'role': 'admin'},
])
unique = collection.unique('name')
```

--------------------------------

### Run Masonite ORM Seed Files

Source: https://orm.masoniteproject.com/commands

Executes all available seed files to populate the database. This command supports options to specify the database connection, run in dry mode, target a specific table, or use a custom seed directory. It is essential for setting up or resetting database states.

```bash
$	 masonite-orm seed:run

# Example with a specific connection:
$	 masonite-orm seed:run --connection staging

# Example in dry mode:
$	 masonite-orm seed:run --dry

# Example for a specific table:
$	 masonite-orm seed:run --table users

# Example with a custom directory:
$	 masonite-orm seed:run --directory app/seeds
```

--------------------------------

### Query Building and Execution with Masonite ORM

Source: https://orm.masoniteproject.com/white-page

Demonstrates the process of initiating a query from a Model, chaining conditions using the QueryBuilder, and executing the query to retrieve a collection of results.

```python
# Initialize model and build query
user = User
user.where('id', 1).where('active', 1)

# Execute query and retrieve collection
results = user.select('id').where('id', 1).where('active', 1).get()
```

--------------------------------

### Apply Column Modifiers in Schema Definitions

Source: https://orm.masoniteproject.com/schema-and-migrations

Examples of using chainable modifiers to alter column behavior such as nullability, uniqueness, default values, and positioning.

```python
table.string('is_admin').after('email')
table.integer('age').unsigned()
table.boolean('is_admin').default(False)
table.string('role_id').primary()
table.string('name').comment('A users name')
```

--------------------------------

### Orator ORM Scope Implementation

Source: https://orm.masoniteproject.com/orator-to-masonite-orm

Demonstrates how to define a scope named 'popular' in Orator ORM. Scopes are reusable query constraints applied to models.

```python
from orator.orm import scope

class User(Model):

  @scope
  def popular(self, query):
        return query.where('votes', '>', 100)
```

--------------------------------

### WHERE LIKE and WHERE NOT LIKE Queries in Python

Source: https://orm.masoniteproject.com/query-builder

Demonstrates how to perform case-sensitive LIKE and NOT LIKE searches on string columns using the Masonite ORM query builder. These methods allow for pattern matching in database queries.

```python
builder.table('users').where_like('name', "Jo%").get()
builder.table('users').where_not_like('name', "Jo%").get()
```

--------------------------------

### Define Numeric and Specialized Columns in Masonite Migrations

Source: https://orm.masoniteproject.com/schema-and-migrations

Examples of defining numeric columns with precision and specialized database types such as UUIDs, ENUMs, and table-level comments.

```python
table.decimal('salary', 17, 6)
table.double('salary', 17, 6)
table.enum('flavor', ['chocolate', 'vanilla'])
table.uuid('id')
table.table_comment('The users table')
```

--------------------------------

### Query Limits and Offsets in Python

Source: https://orm.masoniteproject.com/query-builder

Shows how to apply LIMIT and OFFSET clauses to database queries using the Masonite ORM query builder. These methods are essential for pagination and retrieving specific ranges of records.

```python
builder.table('users').limit(10).get()
```

```python
builder.table('users').offset(10).get()
```

```python
builder.table('users').limit(10).offset(10).get()
```

--------------------------------

### Query Compilation Methods

Source: https://orm.masoniteproject.com/white-page

Illustrates the progression of the _compile_select method from a hardcoded string to an abstracted implementation. It uses helper methods like process_columns and process_from to build the final query dynamically.

```python
def _compile_select(self):
    self.select_format().format(
        columns=self.process_columns(),
        table=self.process_from(),
        limit=self.process_limit(),
        wheres=self.process_wheres()
    )
```

--------------------------------

### Customizing Belongs To Many Relationship Options

Source: https://orm.masoniteproject.com/models

Examples of overriding default relationship settings, such as pivot table naming, timestamp management, and custom attribute names for the pivot object.

```python
@belongs_to_many(pivot_id=None)
@belongs_to_many(table="home_ownership")
@belongs_to_many(with_timestamps=True)
@belongs_to_many(attribute="ownerships")
```

--------------------------------

### Integrating Models with Query Builder

Source: https://orm.masoniteproject.com/query-builder

Shows how to use the Query Builder in conjunction with Masonite ORM models. This allows for returning model instances instead of raw dictionaries.

```python
from masoniteorm.models import Model

class User(Model):
    pass

# Querying with model context
user = QueryBuilder(model=User).table("users").first()
```

--------------------------------

### Define Integer Columns in Masonite ORM

Source: https://orm.masoniteproject.com/schema-and-migrations

Defines INT equivalent columns. Optionally accepts a length parameter for custom integer sizes. Example: `table.integer('age', length=5)`.

```python
table.integer('id')
table.integer('age', length=5)
```

--------------------------------

### Scaffold and Define a Masonite Model

Source: https://orm.masoniteproject.com/models

Demonstrates how to generate a model using the command line and the basic Python class structure required to extend the base Model class.

```bash
$ python masonite-orm model Post
```

```python
from masoniteorm.models import Model

class Post(Model):
    """Post Model"""
    pass
```

--------------------------------

### Define String Column in Masonite ORM

Source: https://orm.masoniteproject.com/schema-and-migrations

Defines a VARCHAR equivalent column. Optionally accepts a length parameter for custom string sizes. Example: `table.string('name', length=181)`.

```python
table.string('name')
table.string('email', length=181)
```

--------------------------------

### Masonite ORM Scope Implementation

Source: https://orm.masoniteproject.com/orator-to-masonite-orm

Demonstrates how to define a scope named 'popular' in Masonite ORM. Scopes are reusable query constraints applied to models, with a similar syntax to Orator.

```python
from masoniteorm.scopes import scope

class User(Model):

  @scope
  def popular(self, query):
        return query.where('votes', '>', 100)
```

--------------------------------

### Take items from a collection

Source: https://orm.masoniteproject.com/collections

Returns a new collection containing a specified number of items. Passing a positive integer retrieves items from the start, while a negative integer retrieves them from the end.

```python
collection = Collection([0, 1, 2, 3, 4, 5])
chunk = collection.take(3)
chunk.all() #== [0, 1, 2]

# Negative integer example
chunk = collection.take(-2)
chunk.all() #== [4, 5]
```

--------------------------------

### Define SQL Select Format

Source: https://orm.masoniteproject.com/white-page

Demonstrates how different database grammars define the structure of a SELECT statement using Python string interpolation to handle syntax variations.

```python
# MySQLGrammar
def select_format(self):
    return "SELECT {columns} FROM {table} {limit}"
```

```python
# MSSQLGrammar
def select_format(self):
    return "SELECT {limit} {columns} FROM {table}"
```

--------------------------------

### Delete Records Based on Query in Python

Source: https://orm.masoniteproject.com/models

Deletes records that match a specific query condition. This example deletes users where the 'active' field is equal to 0, using the User model.

```python
from app.models.User import User

user = User.where('active', 0).delete()
```

--------------------------------

### Low Level Builder Methods

Source: https://orm.masoniteproject.com/query-builder

Utility methods for managing query builder instances and retrieving schema information.

```APIDOC
## .new()

### Description
Creates a new clean builder instance. This instance does not have any clauses, selects, or limits from the original builder instance, making it ideal for performing subqueries.

### Response
- **Builder** - A new, empty query builder instance.

## .where_from_builder()

### Description
Creates a WHERE clause from an existing builder instance.

### Parameters
- **builder** (Object) - Required - The builder instance to extract the WHERE clause from.

## .get_table_name()

### Description
Retrieves the name of the table associated with the current builder instance.

### Response
- **string** - The name of the table.
```

--------------------------------

### Masonite ORM Seed with Data Creation

Source: https://orm.masoniteproject.com/seeding

Example of a Masonite ORM seed that creates a new user record in the database. It uses the User model and the create method to insert data.

```python
from masoniteorm.seeds import Seeder
from models import User

class UserTableSeeder(Seeder):

    def run(self):
        """Run the database seeds."""
        User.create({
            "username": "Joe",
            "email": "joe@masoniteproject.com",
            "password": "secret"
        })
```

--------------------------------

### Configure Polymorphic Relationships

Source: https://orm.masoniteproject.com/models

Explains how to set up polymorphic relationships by mapping model types in the connection resolver and defining relationships in the models.

```python
DB = ConnectionResolver().set_connection_details(DATABASES)
DB.morph_map({
    "Article": Article,
    "Comment": Comment,
})

class Image(Model):
  @morph_to
  def record(self):
    return

class Article(Model):
  @morph_one("record_type", "record_id")
  def image(self):
    return Like

class Comment(Model):
  @morph_one("record_type", "record_id")
  def image(self):
    return Like
```

--------------------------------

### Order Query Results in Python

Source: https://orm.masoniteproject.com/query-builder

Illustrates how to order query results by one or more columns, specifying ascending (default) or descending order. It covers ordering by a single column, multiple columns, and mixed order directions.

```python
builder.order_by("column")
builder.order_by("column", "desc")
builder.order_by("name, email, active")
builder.order_by("name, email desc, active")
```

--------------------------------

### Define Belongs To Many Relationship in Masonite ORM

Source: https://orm.masoniteproject.com/models

Demonstrates how to define a many-to-many relationship in a model using the @belongs_to_many decorator. Includes examples for basic usage and custom foreign key mapping.

```python
from masoniteorm.models import Model
from masoniteorm.relationships import belongs_to_many

class Store(Model):
  @belongs_to_many
  def products(self):
    from app.models.Product import Product
    return Product
```

```python
class Store(Model):
  @belongs_to_many("store_id", "product_id", "id", "id")
  def products(self):
    from app.models.Product import Product
    return Product
```

--------------------------------

### Register Custom Cast Classes in Masonite ORM

Source: https://orm.masoniteproject.com/models

Allows defining and registering custom casting logic for model attributes. Requires a class with 'get' and 'set' methods, registered in the model's __cast_map__.

```python
from some.place.CustomCaster import CustomCaster

class User(Model):
  __cast_map__ = {"custom_key": CustomCaster}

```

--------------------------------

### Create Masonite ORM Seed File

Source: https://orm.masoniteproject.com/commands

Generates a new seed file for a specified table. The command accepts a table name as an argument and an optional directory for storing seed files. This is useful for populating your database with initial data.

```bash
$	 masonite-orm seed {table}

# Example with table name:
$	 masonite-orm seed users

# Example with custom directory:
$	 masonite-orm seed users --directory app/seeds
```

--------------------------------

### GROUP BY Clauses in Python

Source: https://orm.masoniteproject.com/query-builder

Demonstrates how to group query results by one or more columns using the 'group_by' method. This is commonly used with aggregate functions.

```python
builder.table('users').group_by('active').get()
```

```python
builder.table('users').group_by('active, name, is_admin').get()
```

--------------------------------

### Define Table Schema with Blueprint (Python)

Source: https://orm.masoniteproject.com/white-page

Demonstrates how to use the Schema and Blueprint classes to define a new table or alter an existing one. The Schema class sets the table name and mode, while the Blueprint class is used as a context manager to add columns with specified types.

```python
Schema.table('users') as blueprint:
    blueprint.string('name')
    blueprint.integer('age')
```

--------------------------------

### Dynamic Eager Loading with Lambda Expressions (Python)

Source: https://orm.masoniteproject.com/models

Explains how to dynamically modify eager loading queries using a dictionary and lambda expressions. This allows for conditional eager loading based on specific criteria, such as filtering activated phones.

```python
users = User.with_({
  'phone': lambda q: q.where("activated", 1)
}).get()
for user in users:
    user.phone

# Combining multiple eager loads with dynamic conditions:
users = User.with_("friends", "cars", {
  'phone': lambda q: q.where("activated", 1)
}).get()
for user in users:
    user.phone
```

--------------------------------

### Basic JOIN Clause in Python

Source: https://orm.masoniteproject.com/query-builder

Demonstrates the syntax for creating a basic JOIN clause between two tables using the 'join' method. This method specifies the tables and the columns to join on.

```python
builder.join('other_table', 'column1', '=', 'column2')
```

--------------------------------

### Retrieve Item from Collection by Key or Index (Python)

Source: https://orm.masoniteproject.com/collections

The `get` method retrieves an item from a collection using its key or index. If the key is not found, it returns `None` or a specified default value. This method works with both list-like and dictionary-like collections.

```python
collection = Collection([1, 2, 3])
collection.get(0) #== 1
collection.get(4) #== None
collection.get(4, 'default') #== 'default'

collection = Collection({"apples": 1, "cherries": 2})
collection.get("apples") #== 1
```

--------------------------------

### Create Single Record in Masonite ORM

Source: https://orm.masoniteproject.com/models

Illustrates how to create a new record by passing a dictionary to the `create()` method. This inserts the record and returns a new model instance. Use `.fresh()` after creation to fetch any default fields not provided initially.

```python
User.create({"username": "Joe"})
```

```python
user = User.create({"username": "Joe"}).fresh()

user.active #== 1
```

--------------------------------

### Get a random item or multiple items from a collection in Python

Source: https://orm.masoniteproject.com/collections

Returns a random item from the collection. An optional integer count can be provided to retrieve multiple random items, which will be returned as a Collection. Raises ValueError if the collection is smaller than the requested count.

```python
user = User.all().random() #== returns a random User instance

users = User.all().random(3) #== returns a Collection of 3 users
users.count() #== 3
users.all() #== returns a list of 3 users
```

--------------------------------

### Setup StatusMixin for Dynamic Scopes

Source: https://orm.masoniteproject.com/tips-and-tricks

The StatusMixin provides a way to add dynamic scope methods to models based on Enum values. It requires a __statuses__ attribute to be defined on the model and utilizes a StatusScope class to generate these methods.

```python
# statusMixin.py
from .statusScope import StatusScope

class StatusMixin:
    """Mixin to add methods for status filtering"""

    def boot_StatusMixin(self, builder):
        if not hasattr(self, "__statuses__"):
            raise AttributeError("__statuses__ is not defined")
        builder.set_global_scope(StatusScope(getattr(self, "__statuses__")))
```

--------------------------------

### Compile SQL Query (Python)

Source: https://orm.masoniteproject.com/white-page

Shows the final step in the schema definition process where the `to_sql()` method is called on the blueprint object. This method compiles the defined schema changes into the appropriate SQL `CREATE` or `ALTER` statement based on the initial schema mode.

```python
# Assuming 'blueprint' is already defined and populated with columns
sql_query = blueprint.to_sql()
```

--------------------------------

### Masonite ORM: Limiting and Offsetting Query Results

Source: https://orm.masoniteproject.com/query-builder

Methods to control the number of records returned and the starting point of the result set. `.limit()` and `.take()` restrict the number of rows, while `.offset()` and `.skip()` define the number of rows to skip. These are essential for pagination.

```python
from masoniteorm.query import Query

query = Query()
# Limit results to 10
limited_results = query.limit(10).get()

# Offset results by 10
offset_results = query.offset(10).get()

# Using aliases
take_results = query.take(5).get()
skip_results = query.skip(5).get()

print(limited_results)
print(offset_results)
print(take_results)
print(skip_results)
```

--------------------------------

### Call Named Masonite ORM Factories

Source: https://orm.masoniteproject.com/seeding

Demonstrates how to call a specifically named factory, 'admin_users', using the Masonite ORM Factory. This allows for targeted data generation based on predefined factory configurations.

```python
from config.factories import Factory
from models import User

users = Factory(User, 50).create(name="admin_users") #== <masoniteorm.collections.Collection object>
```

--------------------------------

### Bulk Create Records in Masonite ORM

Source: https://orm.masoniteproject.com/models

Demonstrates creating multiple records efficiently using `bulk_create()`. This method returns a collection of created users. For very large datasets, using `User.builder.new().bulk_create()` can be faster by bypassing model hydration.

```python
User.bulk_create([
  {"username": "Joe"},
  {"username": "John"},
  {"username": "Bill"},
  {"username": "Nick"},
])
```

```python
User.builder.new().bulk_create([
  {"username": "Joe"},
  {"username": "John"},
  {"username": "Bill"},
  {"username": "Nick"},
])
```

--------------------------------

### Advanced JOIN Clauses using JoinClause in Python

Source: https://orm.masoniteproject.com/query-builder

Illustrates how to build complex JOIN clauses from scratch using the 'JoinClause' class. This allows for multiple ON conditions and WHERE clauses within the join.

```python
from masoniteorm.expressions import JoinClause

clause = (
    JoinClause('other_table as ot')
    .on('column1', '=', 'column2')
    .on('column3', '=', 'column4')
    .where('column3', '>', 4)
)

builder.join(clause)
```

--------------------------------

### Fetching Records with Select and Filters

Source: https://orm.masoniteproject.com/query-builder

Covers various methods for retrieving data, including column selection, distinct filtering, and fetching single or multiple records.

```python
builder.table('users').select('username').get()
builder.table('users').select('profiles.username as name').get()
builder.table('users').select('name').distinct().get()
builder.table('users').first()
builder.table('users').all()
```

--------------------------------

### Advanced Query Building and Table Management

Source: https://orm.masoniteproject.com/query-builder

Shows how to use subqueries, table selection, and administrative tasks like truncating tables. These methods provide granular control over the generated SQL queries.

```python
# Selecting with aliases and subqueries
query = User.table("users").select("name as username").add_select("order_count", lambda q: q.table("orders").where("user_id", "users.id"))

# Truncating a table
User.truncate("users", foreign_keys=True)
```

--------------------------------

### Perform CRUD and Query Operations in Masonite ORM

Source: https://orm.masoniteproject.com/query-builder

Demonstrates common DML operations such as fetching records, creating entries, and executing raw SQL statements. These methods allow for fluent database interaction within the Masonite framework.

```python
# Fetching records
users = User.all()
first_user = User.first()

# Creating and updating
User.create({"name": "John Doe", "email": "john@example.com"})
User.where("id", 1).update({"name": "Jane Doe"})

# Pagination and chunking
paginated = User.paginate(limit=15, page=1)
for chunk in User.chunk(100):
    process(chunk)

# Raw SQL execution
User.statement("SELECT * FROM users WHERE active = 1")
```

--------------------------------

### Paginate Records in Python

Source: https://orm.masoniteproject.com/query-builder

Explains two methods for paginating query results: 'length aware' pagination which includes total record counts (making two queries), and 'simple pagination' which omits the total count for efficiency (making one query).

```python
builder.table("users").where("active", 1).paginate(number_of_results, page)
builder.table("users").where("active", 1).simple_paginate(number_of_results, page)
```

--------------------------------

### Column Addition Proxy Call (Python)

Source: https://orm.masoniteproject.com/white-page

Illustrates how calling a column definition method on the Blueprint class, such as `blueprint.string('name')`, internally translates to a call to `table.add_column` with the appropriate arguments. This shows the underlying mechanism for column manipulation.

```python
Schema.table('users') as blueprint:
    blueprint.string('name')

# This is a proxy call to:
table.add_column('name', column_type='string')
```

--------------------------------

### Default Eager Loading with __with__ Attribute (Python)

Source: https://orm.masoniteproject.com/models

Demonstrates how to configure default eager loading for all queries of a specific model by using the `__with__` attribute. This ensures specified relationships are always loaded, reducing the need to explicitly call `with_`.

```python
from masoniteorm.models import Model
from masoniteorm.relationships import belongs_to_many
class Store(Model):

  __with__ = ['products']

  @belongs_to_many
  def products(self):
    from app.models.Product import Product
    return Product
```

--------------------------------

### SQL Compilation Methods

Source: https://orm.masoniteproject.com/query-builder

Methods used to compile query builder instances into raw SQL strings for debugging or execution.

```APIDOC
## .to_sql()

### Description
Returns a string of the fully compiled SQL to be generated.

### Method
N/A (Method call)

### Response
- **string** - The compiled SQL query string.

## .to_qmark('')

### Description
Returns a string of the SQL to be generated but with `?` placeholders where the SQL bindings are placed. This method also resets the query builder instance.

### Parameters
- **bindings** (string) - Required - The binding values to be replaced by placeholders.

### Response
- **string** - The SQL string with `?` placeholders.
```

--------------------------------

### Raw GROUP BY Clauses in Python

Source: https://orm.masoniteproject.com/query-builder

Shows how to use raw SQL expressions within the GROUP BY clause using the 'group_by_raw' method. This provides flexibility for complex grouping scenarios not covered by standard methods.

```python
builder.table('users').group_by_raw('COUNT(*)').get()
```

--------------------------------

### Database-Specific SQL Formatting

Source: https://orm.masoniteproject.com/white-page

Demonstrates how SQL query structures are abstracted to support different database grammars like MySQL and Microsoft SQL Server. By using format strings, the ORM can swap syntax components based on the specific database driver.

```python
# MySQL
"SELECT {columns} FROM {table} {limit}".format(
    columns="*",
    table="`users`",
    limit="LIMIT 1"
)
```

```python
# Microsoft SQL Server
"SELECT {limit} {columns} FROM {table} ".format(
    columns="*",
    table="`users`",
    limit="TOP 1"
)
```

--------------------------------

### Conditional Queries with 'when' Method in Python

Source: https://orm.masoniteproject.com/query-builder

Demonstrates the use of the 'when' method for conditionally adding query constraints. This method accepts a condition and a callable, executing the callable only if the condition is truthy, simplifying conditional logic in queries.

```python
def show(self, request: Request):
    age = request.input('age')
    article = Article.where('active', 1).when(age >= 21, lambda q: q.where('age_restricted', 1))
```

--------------------------------

### Paginate Collection Items (Python)

Source: https://orm.masoniteproject.com/collections

The `for_page` method creates a new collection containing items for a specific page. It takes the page number and the number of items per page as arguments. The original collection remains unchanged.

```python
collection = Collection([1, 2, 3, 4, 5, 6, 7, 8, 9])
chunk = collection.for_page(2, 4).all() #== 4, 5, 6, 7
```

--------------------------------

### Perform Right Join in Python

Source: https://orm.masoniteproject.com/query-builder

Demonstrates how to perform a right join between two tables using the query builder. It specifies the tables to join, the join condition, and the comparison operator.

```python
builder.table('users').right_join('table1', 'table2.id', '=', 'table1.table_id')
```

--------------------------------

### Subqueries in WHERE Clauses using Python

Source: https://orm.masoniteproject.com/query-builder

Shows how to construct subqueries within WHERE clauses by passing a callable to the 'where' method. This is useful for complex filtering conditions.

```python
builder.table("users").where(lambda q: q.where("active", 1).where_null("activated_at")).get()
# SELECT * FROM "users" WHERE ("users"."active" = '1' AND "users"."activated_at" IS NULL)
```

--------------------------------

### Aggregate Data with Sum, Avg, Count, Max, Min in Python

Source: https://orm.masoniteproject.com/query-builder

Details how to use aggregate functions like SUM, AVG, COUNT, MAX, and MIN on table columns. It shows how to retrieve the aggregated value and how to apply aliases to the aggregate results.

```python
salary = builder.table('users').sum('salary').first().salary
salary = builder.table('users').avg('salary').first().salary
salary = builder.table('users').count('salary').first().salary
salary = builder.table('users').max('salary').first().salary
salary = builder.table('users').min('salary').first().salary
builder.table('users').sum('salary as payments').get()
```

--------------------------------

### Data Retrieval Methods

Source: https://orm.masoniteproject.com/query-builder

Methods used to fetch, chunk, or paginate records from the database.

```APIDOC
## GET /records

### Description
Methods for retrieving data from the database, including pagination and chunking.

### Methods
- `.all()`: Gets all records.
- `.first()`: Gets the first record.
- `.last()`: Gets the last record.
- `.get()`: Executes the query and returns results.
- `.chunk(size)`: Processes large datasets in smaller chunks using a generator.
- `.paginate(limit, page)`: Returns length-aware pagination data.
- `.simple_paginate(limit, page)`: Returns pagination without total count.

### Parameters
- **limit** (int) - Required for paginate - Number of records per page.
- **page** (int) - Required for paginate - Current page number.

### Response Example
{
  "data": [{"id": 1, "name": "Example"}],
  "total": 100,
  "per_page": 15
}
```

--------------------------------

### Advanced JOIN Clauses using Lambda in Python

Source: https://orm.masoniteproject.com/query-builder

Shows an alternative and cleaner way to construct advanced JOIN clauses by passing a lambda function to the 'join' method. The lambda receives a 'JoinClause' instance that can be built up.

```python
builder.join('other_table as ot', lambda join: (
    (
        join.on('column1', '=', 'column2')
        .on('column3', '=', 'column4')
        .where('column3', '>', 4)
    )
))
```
Tokens:
Raw