Editor dropdown list filter where condition

npincombe

Hello,
I think that only the most experienced DataTables developers would be able to solve this complex problem but I will greatly appreciate any help you can provide. I've ready every piece of documentation I can find an tried all examples without success.
I have a data table that shows records of payments made by a person (i.e. the payer). The user will only see records that they own (i.e. where users_id = $logged_in_user_ID). Each user has their own list of payers which are stored in a database table that contains their user ID and the name of the payer.
The database table that contains the payment records does not have a way to join to the database table that contains the list of payers. The database of payments contains the payers name rather than an ID. This is by design.
When the user creates a new record using the datatables editor, it displays a dropdown option to select the payer.
The problem I am having is that the dropdown list contains more than just the payers that belong to the user but rather it contains all of the payers including those that belong to other users.
I would like to select only the payers that belong to the logged in user. I can do this using a simple SQL query such as:
select id as key, name as value from income_payers where users_id = $logged_in_user_ID;

Rather than posting all of my code, which is very large, I am providing the relevant parts. Please let me know if this isn't sufficient enough.
Declaring my configuration: I am using Editor-PHP-1.5.6, responsive/2.1.0, select/1.2.0, buttons/1.2.1.

Server Script

$table = "income";
$primarykey = "id";

$editor = Editor::inst($db, $table, $primarykey)
            ->fields(
                         Field::inst('income_payers.name')
                         ->options('income_payers', 'id', 'name')
            )
            ->where('users.id', $userID, '=')
            ->leftJoin('users', 'users.id', '=', 'income.users_id')
            ->leftJoin('income_payers', 'income_payers.users_id', '=', 'users.id')
            ->process($_POST)
            ->json();

JavaScript

editor = new $.fn.dataTable.Editor({
    ajax: "dataTableProcess",
    table: "#table",
    fields: [{
        label: "Payer:",
        name: "income_payers.name",
        type: "select"
      }
    ]
  });

allan

Hi,

There are two options here - both involving changing the ->options() call:

1) Editor 1.6 introduced a new Options class which has more and better options that then old three parameter shorthand. Including the option to specify a where condition, which it sounds like is exactly what you want. Documentation for it is here.

2) Use a closure function. This is useful if you need a complex custom SQL call or to get the list of options from somewhere other than the database.

From why you describe, option 1 sounds like the way to do.

Regards,
Allan

npincombe

Hi Allan,

It worked! You are an absolute genius.

I used the first method that you recommended which was to upgrade from Editor 1.5.6 to 1.6 and use the Options class.
Another key point to note for anyone reading this is to include use DataTables\Editor\Options at the top of the PHP file.

Here is what my working code looked like in the end:

Server Script

$table = "income";
$primarykey = "id";

$editor = Editor::inst($db, $table, $primarykey)
            ->fields(
Field::inst('income.payer')
                    ->options(Options::inst()
                            ->table('income_payers')
                            ->value('id')
                            ->label('name')
                            ->where(function ($q) {                              
                              $q->where('users_id', $userID, '=');
                            })
                    )
->where('users.id', $userID, '=')
            ->leftJoin('users', 'users.id', '=',
                    'income.users_id')            
            ->process($_POST)
            ->json();

Java Script

editor = new $.fn.dataTable.Editor({
    ajax: "dataTableProcess",
    table: "#table",
    fields: [{
        label: "Payer:",
        name: "income.payer",
        type: "select"
      }
    ]
  });

The datatable will show all income records that belong to a specific user ->where('users.id', $userID, '='). If a users wants to create or edit a record using the datatable Editor, they get to choose who the "payer" is from a drop down list of "payers" that belong to the specific user
->where(function ($q) { $q->where('users_id', $userID, '='); }. That is why there are two where clauses.

I hope that this is helpful to someone.

allan

Fantastic - great to hear that this worked!

Allan