2
\$\begingroup\$

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);
}
\$\endgroup\$
2
\$\begingroup\$

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:

| improve this answer | |
\$\endgroup\$
  • \$\begingroup\$ After I read your codes I already know that yours is more readable and easier to maintain :). Thanks you and for the links as well. Anyway for the sorting logic, the default order is based on the creation date. And user can click the table header e.g. (group name, country name, status) to change the sort order. If you can suggest how to optimize more the sorting logic that would be great. Thanks again! \$\endgroup\$ – Paengski Oct 21 '14 at 0:51
  • 1
    \$\begingroup\$ Glad I could help! You may try to do the sorting through Ajax but you would still use query scopes to help do that. I will try to write a few up so you can see. \$\endgroup\$ – Ross Edman Oct 21 '14 at 2:05
  • \$\begingroup\$ I am having a problem sorting the country name using scope query. Basically, I want to sort the Group record based on country name. But since country_name is available in country table it is throwing an error that country_name do not exist in Group table. public function scopeSortCountry($query, $order_by) { // option 1 not working return $query->orderBy('country_name', $order_by); // option 2 not working as well return $query->has('country')->orderBy('country_name', $order_by); } \$\endgroup\$ – Paengski Oct 28 '14 at 3:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.