Laravel Eloquent ORM Cheat Sheet
22 Oct, 2023
Abdelrahman Etry
Laravel is a popular PHP framework that has gained a reputation for its elegant syntax, straightforward documentation, and efficient features. One of the most significant features of Laravel is its powerful Object-Relational Mapping (ORM) system, Eloquent. Eloquent simplifies database interaction and makes it easy to perform database operations in an Object-Oriented manner. It offers many features like basic insert/update/delete operations, complex joins, relations, scopes and even query caching. It is a fast, expressive and simple ORM, making database interaction both easy and enjoyable. In this article, you'll have a cheat sheet for for Laravel Eloquent.
Cheat Sheet:
1- Select
select('col1','col2')
| select certain fields
|
select(array('col1','col2'))
| converted to array internally
|
select(DB::raw('count(*) as user_count, status'))
| allows you to select whatever you want and basically write raw SQL statements
|
addSelect('col3','col4')
| select column with select statement
|
distinct()
| return a value with No Duplicates
|
2- Where
| where('id', 2) | returns results according to some condition |
| orWhere('id', '!=', 1) | normally used after where() to have multiple conditions |
whereIn('id', [1,2,3,4])
| check whether column contains value from the array or list
|
whereNotIn('id', [1,2,3,4])
| check whether column does not contain value from the array or list
|
| where(function ($query){ $query->where('a', '2')->orWhere('b', '!=', 3) }) | use multiple where conditions with or where
|
whereRaw('salary> ? and age = 30', array(25))
| query builder that throws the input with the same value observed in SQL queries and is accessed using the where clause
|
whereExists(function ($query) {$query->from('licenses')->whereColumn('licenses.order_id', 'orders.id');})
| list all orders that include a license
|
orWhereExists(function ($query) {$query->from('licenses')->whereColumn('licenses.item_id', 'items.id');})
| list all items that include a license usually occurs after a whereExists condition for having multiple whereExists conditions
|
whereNotExists() and orWhereNotExists()
| similar to the above examples but the only difference is that it negates the logical expression. |
|
3- Join
join('departments','users.department_id','=','department.id')
| selecting data from other tables to limiting the matches of your query
|
4- Eager Loading
| with('table1','table2') | loads first rows in the relationships table
|
with(array('table1','table2','table1.nestedtable3'))
| converted to array internally
|
5- Grouping
groupBy('state_id','locality')
| group records from a database table based on a specific column or columns
|
havingRaw('count > 1 ')
| similar to normal having() but writing sql instead |
having('name','LIKE',"john")
| if we have to compare with a number or static value
|
orHavingRaw('brand LIKE ?',array("%$keyword%"))
| usually comes after a havingRaw() to have multiple conditions |
6- Cache
remember($minutes)
| cache the results of a query for a specific amount of time
|
rememberForever()
| cache the results of a query indefinitely
|
7- Offset & Limit
take(10)
| get data from a database table with a limit
|
limit(10)
| get number of data from databas
|
skip(10)
| skip some records when you fetch data from the database table
|
offset(10)
| use for skip number of data
|
forPage($pageNo, $perPage)
| chunk query results passed into the function |
8- Order
orderBy('id','DESC')
| sorting the results of the query based on type ASC or DESC for a specific column |
orderBy(DB::raw('RAND()'))
| specify a raw SQL expression to use for sorting the results of a query
|
orderByRaw('type = ? , type = ? ', array('published','draft'))
| specify a raw SQL expression to use for sorting the results of a query
|
latest()
| allow you to easily order results by date, default sorts by created_at column
|
latest('column')
| allow you to easily order results by specific column
|
oldest()
| allow you to easily order results by date, default sorts by created_at column
|
oldest('column')
| allow you to easily order results by specific column
|
9- Create
| insert(array('email' => 'john@example.com', 'votes' => 0)) | insert a single row in database |
insert(array(array('email' => 'taylor@example.com', 'votes' => 0),array('email' => 'dayle@example.com', 'votes' => 0)))
| insert records from array as a batch or in other words Batch Insert |
insertGetId(array('email' => 'john@example.com', 'votes' => 0))
| return record id after insertion in database |
10- Update
update(array('email' => 'john@example.com'))
| update a single row in database |
increment('column')
| increment a specific column in database
|
decrement('column')
| decrement a specific column in database |
touch()
| update timestamp
|
11- Delete
delete()
| delete a model instance
|
forceDelete()
| when softdeletes enabled
|
destroy($ids)
| delete by array of primary keys
|
roles()->detach()
| delete from pivot table: associated by 'belongsToMany'
|
12- Getters
find($id)
| retrieve records from the database using the primary key
|
findOrFail($id)
| abort the process if no record is found
|
findMany($ids, $columns)
| get multiple results of find with array of IDs or columns
|
first()
| return the first record found from the database
|
firstOrFail()
| will abort if no record is found in your query
|
all()
| create a new query object and call get() on it
|
get()
| retrieve all the rows that fit the where clauses
|
getFresh()
| get non-cached results |
getCached()
| get cached results |
chunk(1000, function($rows){$rows->each(function($row){})
| fetches data from the database in smaller numbers/amounts
|
lists('column')
| get numeric index |
pluck('column')
| Pluck a single column's value from the first result of a query
|
value('column')
| Get a single column's value from the first result of a query
|
13- Pagination
paginate(10)
| takes care of setting the proper limit and offset based on the current page being viewed by the user
|
simplePaginate(10)
| if you don't need the numbers of pages and you just want to show links for Previous and Next
|
getPaginationCount()
| get total no of records
|
14- Aggregation
count()
| returns the total number of records in result
|
max('column')
| get maximum column value
|
min('column')
| get minimum column value
|
sum('column')
| get the sum of a column's values
|
avg('column')
| calculating the average of a column in the collection
|
aggregate('sum', array('column'))
| computes on a set of values and returns a single value
|
15- Others
toSql()
| output sql query
|
exists()
| check if any row exists
|
fresh()
| Return a fresh data for current model from database
|
16- Object Methods
toArray()
| converts all of the collection's nested objects that are an instance to an array
|
toJson()
| convert a model to JSON
|
relationsToArray()
| Get the model's relationships in array form
|
implode('column', ',')
| comma separated values of a column
|
isDirty()
| returns true if any attributes have changed or false if no changes are detected
|
getDirty()
| Get the attributes that have been changed but not saved to database
|
17- Debugging
DB::enableQueryLog()
| give access to store all execute query in cache
|
DB::getQueryLog()
| get all query logs
|
Model::where()->toSql()
| outputs SQL query |
.jpg)
.jpg)
.jpg)
.jpg)
.jpg)
 (1).jpg)
.jpg)