Problem
I just started using Laravel 4. I read some tutorials on how to implement CRUD in Laravel, and I was able to develop a portion of it. What I am going to show you is more on Controller and Eloquent ORM.
I have here 2 models “Group” which can only have one Country, but Country can be assigned to different Group (“one to many”).
Group Model
class Group extends Eloquent
{
protected $table = 'group';
protected $guarded = array('group_id');
protected $primaryKey = 'group_id';
public function country()
{
return $this->belongsTo('country', 'country_id');
}
}
Country Model
class Country extends Eloquent
{
protected $table = 'country';
protected $primaryKey = 'country_id';
public function group() {
return $this->hasMany('group', 'country_id');
}
}
In my controller, I have index() function which accepts user search filter and pagination request.
Group Controller
public function index()
{
// search variables here
$s_name = Input::get("s_name");
$s_status = Input::get("s_status");
// order variables here
$sortBy = Input::get("sort_by");
$orderBy = Input::get("order_by");
// get all the groups
$groups = Group::with('country')
->join('country', 'country.country_id', '=', 'group.country_id');
// search conditions
$groups = $groups->whereIn('group_status', array(ACTIVE, INACTIVE));
if (!empty($s_name)) {
$groups = $groups->where('group_name', 'LIKE', "%$s_name%");
}
if (!empty($s_status)) {
$groups = $groups->where('group_status', 'LIKE', "%$s_status%");
}
// order
if (!empty($sortBy)) {
$orderBy = (empty($orderBy)) ? ASCENDING : $orderBy;
if ($sortBy == "o_name") {
$groups = $groups->orderBy('group_name', $orderBy);
} else if ($sortBy == "o_country") {
$groups = $groups->orderBy('country_short_name', $orderBy);
} else if ($sortBy == "o_status") {
$groups = $groups->orderBy('group_status', $orderBy);
}
} else {
$groups = $groups->orderBy('group.created_at', DESCENDING);
}
$groups = $groups->paginate(PAGINATION_LIMIT);
}
Solution
Typically the primary key on the Models is the id in the database table. You have these set as group_id and country_id. This is possible, but confusing. You usually only reference these when making relationships and Laravel will do this automatically if your keys follow a convention. Here is an example:
Group Model
class Group extends Eloquent {
protected $table = 'group'; // this is assumed by the model name
protected $guarded = array('id'); // this is usually id
protected $primaryKey = 'id'; // this usually isn't necessary
public function country()
{
return $this->belongsTo('country'); // this is referencing `country_id` on the groups table and associating it with the Country model.
}
// this pertains to part of the answer about the controller
public function scopeStatus($query, $status)
{
return $query->where('group_status', 'LIKE', $status);
}
public function scopeName($query, $name)
{
return $query->where('group_name', 'LIKE', $name);
}
}
Country Model
class Country extends Eloquent
{
protected $table = 'country';
protected $primaryKey = 'country_id'; // not necessary, should be `id`
public function groups() {
return $this->hasMany('group'); // you should have a `group_id` column on the `country` table to associate with, this is automatically inferred by Laravel
}
}
Group Controller
There are many pieces of your Controller which should be part of your Model. These custom queries can be reused and make your application more flexible. You can use query scopes to add to your models.
I will do my best to show you an example. I will be updating your model as well so you can see what I am moving into the model.
public function index()
{
// search variables here
$s_name = Input::get("s_name");
$s_status = Input::get("s_status");
// order variables here
$sortBy = Input::get("sort_by");
$orderBy = Input::get("order_by");
// if you are going to filter all groups anyways
// there is not a reason to get all of them, just
// query the ones you need! :)
if (!empty($s_name)) {
$groups = Group::name($s_name); // this is a query scope defined in your model
}
if (!empty($s_status)) {
$groups = Group::status($s_status); // this is a query scope defined in your model
}
//.. other logic here
}
I tried to understand your other sorting logic as well but couldn’t comprehend all of it without seeing the data. This sorting could be added as a filter in your model or even to your query scopes. Then all of this logic would be removed from your Controller and could be re-used throughout your application.
Hopefully this helps!
Here are some other resources on query scopes. I find many people miss the power of these: