Laravel 4中的多选过滤搜索

I need help/guidance in developing a multi-select filter search for my Laravel 4 app.

我需要帮助/指导,为我的Laravel 4应用程序开发多选过滤器搜索。

I have a table in my database called 'accounts'. This table is linked to other tables in the database via the following relationships:

我的数据库中有一个名为'accounts'的表。此表通过以下关系链接到数据库中的其他表:

'users' via belongs to (User model has a has many relationship to accounts) 'account_types' via belongs to (AccountType model has a has one relationship to accounts)

'用户'通过属于(用户模型与帐户有很多关系)'account_types'属于(AccountType模型与帐户有一个关系)

In my view I would like 3 multi-select boxes, company names (taken from the accounts table 'company_name' field), account managers (taken from the account_managers table, 'first_name' and 'last_name' fields) and account type (taken from the account_types table, 'type' field).

在我看来,我想要3个多选框,公司名称(取自账户表'company_name'字段),客户经理(取自account_managers表,'first_name'和'last_name'字段)和账户类型(取自account_types表,'type'字段)。

When a user selects values from these multi-select boxes and submits the form, I need to search the relevant tables and bring back the results. I don't want to use joins for this, as it is very slow. Especially, when bringing back values for the multi-select boxes.

当用户从这些多选框中选择值并提交表单时,我需要搜索相关表并返回结果。我不想为此使用连接,因为它非常慢。特别是,当返回多选框的值时。

If possible I would like to use Eloquent relationships in a way that brings back the results quickly.

如果可能的话,我想以一种快速恢复结果的方式使用Eloquent关系。

I have this working with joins and query strings but it is very slow, up to 10 to 15 seconds.

我使用连接和查询字符串,但它非常慢,最多10到15秒。

I hope someone can help me out with this. Cheers.

我希望有人可以帮助我解决这个问题。干杯。

1 个解决方案

#1

OK, I have this working like a charm now by implementing select2, rather than simply loading all contacts in one go. Works much nicer too.

好吧,我现在通过实现select2而不是简单地一次性加载所有联系人,就像魅力一样。工作得更好。

Here's my index method in AdminContactsController.php:

这是AdminContactsController.php中的索引方法:

public function index()
{

    $contact_names_value = explode(',', Input::get('contact_names_value'));
    $accounts_value = explode(',', Input::get('accounts_value'));
    $account_managers_value = explode(',', Input::get('account_managers_value'));

    // In the view, there is a dropdown box, that allows the user to select the amount of records to show per page. Retrive that value or set a default.
    $perPage = Input::get('perPage', 10);

    // This code retrieves the order from  that has been selected by the user by clicking on table ciolumn titles. The value is placed in the session and is used later in the Eloquent query and joins.
    $order = Session::get('contact.order', 'cname.asc');
    $order = explode('.', $order);

    $message = Session::get('message');

    $default = ($perPage === null ? 10 : $perPage);

    $contacts_trash = Contact::contactsTrash($order)->get();

    $this->layout->content = View::make('admin.contacts.index', array(
        'contacts'          => Contact::contacts($order, $contact_names_value, $accounts_value, $account_managers_value, $perPage)->paginate($perPage)->appends(array('accounts_value' => Input::get('accounts_value'), 'account_managers_value' => Input::get('account_managers_value'))),
        'contacts_trash'    => $contacts_trash,
        'perPage'           => $perPage,
        'message'           => $message,
        'default'           => $default
    ));
}

My scopeContacts method in my Contact.php model:

我的Contact.php模型中的scopeContacts方法:

public function scopeContacts($query, $order, $contact_names_value, $accounts_value, $account_managers_value, $perPage)
{
    $query->leftJoin('accounts', 'accounts.id', '=', 'contacts.account_id')
        ->leftJoin('users', 'users.id', '=', 'accounts.user_id')
        ->orderBy($order[0], $order[1])
        ->select(array('contacts.*', DB::raw('contacts.id as cid'), DB::raw('CONCAT(contacts.first_name," ",contacts.last_name) as cname'), DB::raw('CONCAT(users.first_name," ",users.last_name) as amname')));

    if (empty($contact_names_value[0])) {
        //
    } else {
        $query = $query->whereIn('contacts.id', $contact_names_value);
    }

    if (empty($accounts_value[0])) {
        //
    } else {
        $query = $query->whereIn('accounts.id', $accounts_value);
    }

    if (empty($account_managers_value[0])) {
        //
    } else {
        $query->whereIn('users.id', $account_managers_value);
    }
}

Here's my JS code:

这是我的JS代码:

$('#contact_names_value').select2({
    placeholder: 'Search contacts',
    minimumInputLength: 3,
    ajax: {
        url: '/admin/get-contact',
        dataType: 'json',
        data: function (term, page) {
            return {
                contact_names_value: term
            };
        },
        results: function (data, page) {
            return {results: data};
        }
    },
    tags: true
});

Here's my method getContactByName implemented in my AdminContactsController.php (similar methods implemented for users and accounts) code:

这是我的方法getContactByName在我的AdminContactsController.php(为用户和帐户实现的类似方法)代码中实现的:

public function getContactByName()
{
    $name = Input::get('contact_names_value');
    return Contact::select(array('id', DB::raw('concat(first_name," ",last_name) as text')))->where(DB::raw('concat(first_name," ",last_name)'), 'like', "%$name%")->get();
}

Notice during my select statement, I do a DB::raw and set the 'first_name' and 'last_name' fields to be selected as 'text'. I think this was one of the major issues, as the plugin requires 'id' and 'text' to function.

请注意,在我的select语句中,我执行DB :: raw并将'first_name'和'last_name'字段设置为'text'。我认为这是一个主要问题,因为插件需要'id'和'text'才能运行。

My route was simply:

我的路线很简单:

Route::get('admin/get-contact', 'AdminContactsController@getContactByName');

1 个解决方案

#1

更多相关文章

随机推荐