DhUFr6Ub

1<?php
2Model::
3
4  /*Select*/
5  select('col1','col2')
6  ->select(array('col1','col2'))
7  ->select(DB::raw('businesses.*, COUNT(reviews.id) as no_of_ratings, IFNULL(sum(reviews.score),0) as rating'))  
8  ->addSelect('col3','col4')
9  ->distinct() // distinct select
10  
11  /*From*/
12  ->from('table')
13  ->from(DB::raw('table, (select @n :=0) dummy'))
14  ->from(DB::raw("({$subQuery->toSql()}) T ")->mergeBindings($subQuery->getQuery())
15
16  
17  /*Query*/
18  ->where('column','value')
19  ->where('column','LIKE','%'.$value.'%')
20  ->where(function ($query) {
21  	$query->where('a', '=', 1)
22    	->orWhere('b', '=', 1);
23  })
24  ->orWhere('column','!=', 'value')
25  ->whereRaw('age > ? and votes = 100', array(25))
26  
27  ->whereRaw(DB::raw("id in (select city_id from addresses GROUP BY addresses.city_id)"))
28  
29  ->whereExists(function($query)
30  {
31  	$query->select(DB::raw(1))
32        ->from('business_language')
33        ->whereRaw('business_language.language_id = languages.id')
34        ->groupBy('business_language.language_id')
35        ->havingRaw("COUNT(*) > 0");
36  })
37  ->orWhereExists()
38  ->whereNotExists()
39  ->orWhereNotExists()
40  
41  ->whereIn('column',[1,2,3])
42  ->orWhereIn()
43  ->whereNotIn('id', function($query){
44    $query->select('city_id')
45    ->from('addresses')
46    ->groupBy('addresses.city_id');
47  })
48  ->whereNotIn()
49  ->orWhereNotIn
50  
51  ->whereNull('column') //where `column` is null
52  ->orWhereNull('column') //or where `column` is null
53  ->whereNotNull('column')  //where `column` is not null 
54  ->orWhereNotNull('column')  //or where `column` is not null 
55  
56  ->whereDay()
57  ->whereMonth('column', '=', 1) //
58  ->whereYear('column', '>', 2000) //uses sql YEAR() function on 'column'
59  ->whereDate('column', '>', '2000-01-01')
60  
61  /*Joins*/
62  ->join('business_category','business_category.business_id','=','businesses.id')
63  ->leftJoin('reviews','reviews.business_id', '=', 'businesses.id')
64  ->join('business_category',function($join) use($cats) {
65    $join->on('business_category.business_id', '=', 'businesses.id')
66    ->on('business_category.id', '=', $cats, 'and', true);
67  })
68  ->join(DB::raw('(SELECT *, ROUND(AVG(rating),2) avg FROM reviews WHERE rating!=0 GROUP BY item_id ) T' ), function($join){
69  	$join->on('genre_relation.movie_id', '=', 'T.id')
70  })
71  
72  /*Eager Loading */
73  ->with('table1','table2')
74  ->with(array('table1','table2','table1.nestedtable3'))
75  ->with(array('posts' => function($query) use($name){
76    $query->where('title', 'like', '%'.$name.'%')
77      ->orderBy('created_at', 'desc');
78  }))
79  
80  
81  /*Grouping*/
82  ->groupBy('state_id','locality')
83  ->havingRaw('count > 1 ')
84  ->having('items.name','LIKE',"%$keyword%")
85  ->orHavingRaw('brand LIKE ?',array("%$keyword%"))
86				
87  /*Cache*/
88  ->remember($minutes)
89  ->rememberForever()
90    
91  /*Offset & Limit*/
92  ->take(10)
93  ->limit(10)
94  ->skip(10)
95  ->offset(10)
96  ->forPage($pageNo, $perPage)
97  
98  /*Order*/
99  ->orderBy('id','DESC')
100  ->orderBy(DB::raw('RAND()'))
101  ->orderByRaw('type = ? , type = ? ', array('published','draft'))
102  ->latest() // on 'created_at' column
103  ->latest('column')
104  ->oldest() // on 'created_at' column
105  ->oldest('column')
106  
107  /*Create*/
108  ->insert(array('email' => 'john@example.com', 'votes' => 0))
109  ->insert(array(   
110    array('email' => 'taylor@example.com', 'votes' => 0),
111    array('email' => 'dayle@example.com', 'votes' => 0)
112  )) //batch insert
113  ->insertGetId(array('email' => 'john@example.com', 'votes' => 0)) //insert and return id
114  
115  /*Update*/
116  ->update(array('email' => 'john@example.com'))
117  ->update(array('column' => DB::raw('NULL')))
118  ->increment('column')
119  ->decrement('column')
120  ->touch() //update timestamp
121  
122  /*Delete*/
123  ->delete()
124  ->forceDelete() // when softdeletes enabled
125  ->destroy($ids) // delete by array of primary keys
126  ->roles()->detach() //delete from pivot table: associated by 'belongsToMany'
127  
128  
129  /*Getters*/
130  ->find($id)
131  ->find($id, array('col1','col2'))
132  ->findOrFail($id)
133  ->findMany($ids, $columns)
134  ->first(array('col1','col2'))
135  ->firstOrFail()
136  ->all()
137  ->get()
138  ->get(array('col1','col2')) 
139  ->getFresh() // no caching
140  ->getCached() // get cached result
141  ->chunk(1000, function($rows){
142  	$rows->each(function($row){
143  		
144  	});
145  })
146  ->lists('column') // numeric index
147  ->lists('column','id') // 'id' column as index
148  ->lists('column')->implode('column', ',') // comma separated values of a column
149  ->pluck('column')  //Pluck a single column's value from the first result of a query.
150  ->value('column')  //Get a single column's value from the first result of a query.
151  
152  /*Paginated results*/
153  ->paginate(10)
154  ->paginate(10, array('col1','col2'))
155  ->simplePaginate(10)
156  ->getPaginationCount() //get total no of records
157  
158  /*Aggregate*/
159  ->count()
160  ->count('column')
161  ->count(DB::raw('distinct column'))
162  ->max('rating')
163  ->min('rating')
164  ->sum('rating')
165  ->avg('rating')
166  ->aggregate('sum', array('rating')) // use of aggregate functions
167  
168  /*Others*/
169  ->toSql() // output sql query
170  ->exists() // check if any row exists
171  ->fresh() // Return a fresh data for current model from database
172  
173  /*Object methods*/
174  ->toArray() //
175  ->toJson()
176  ->relationsToArray() //Get the model's relationships in array form.
177  ->implode('column', ',') // comma separated values of a column
178  ->isDirty()
179  ->getDirty() //Get the attributes that have been changed but not saved to DB
180  
181//Debugging
182DB::enableQueryLog();
183DB::getQueryLog();
184Model::where()->toSql() // output sql query