D6XnE0dU

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