how to create a multiple where using php editor 1.4.0

xnrsisbi

Hi,

first of all, happy new year to all :-)
i need to create a php to retrieve data from database using editor 1.4.0, my code is something like this:
...

->leftJoin( 'tablename2', 'tablename2.id', '=', 'tablename1.type' )

->where($key = 'tablename1.uid' and_where() $key = 'tablename2.uid', $value = $uid, $op = '=' )

->process( $_POST )

->json();

Eg: i need to filter both tables (tablename 1 and 2) to retrieve user only values
My data is not loading in table and a json error is thrown

I searched the docs and i got this page http://editor.datatables.net/docs/1.4.0-beta/php/class-DataTables.Editor.html#_where but i'm not sure how to apply the following instructions:
"The complex case allows full control over the query conditions by providing a closure function that has access to the database Query that Editor is using, so you can use the where(), or_where(), and_where() and where_group() methods as you require."

Can anyone please help me set a multiple where clause?
Many thanks in advance

allan

It looks like you just need to simplify the code you've got there significantly - I think you've over thought it :-)

->where( 'tablename1.uid', $uid )
->where( 'tablename2.uid', $uid )

Multiple where() calls will automatically use and AND statement.

Allan

xnrsisbi

Thanks Allan, that did the trick... but (yes there is always a '...but') when editing or creating a new record the dropdown related with column 'name' in tablename2 is not filtered by 'uid' and all records (from all users) are showed :-(

is there any way to do that?

PS: Have a happy new year :-)

allan

You have a select field which shows options based on another table? To provide a custom filter for that, you would need to use the options() Field method that is available in 1.4 as a closure method to get the data required.

You can see an example of how to use a closure in options() in this example. You would use the where condition in the 3rd parameter passed into selectDistinct() - assuming you choose to use that method.

Allan

xnrsisbi

Maybe i'm not explaining myself clearly... sorry about that.
Lets see, i have two tables (products and product type), the table "products" have a field named "product type" in which i save the product type id. The table "product type" has an id field and a name field.

What i need is to be able to show (on edit and on create) in a select box the name of the product types and save back in the products table the product type id... up until this point i have it all working smoothly :-)

my porblem now is that in the select box i got ALL product types and not only the product types of this user which is supposed if i use in my editor php the following:

Editor::inst( $db, 'product' )
    ->field(
        Field::inst( 'product.id' ),
        Field::inst( 'product.code' )->validator( 'Validate::unique' ),
        Field::inst( 'product.name' )->validator( 'Validate::unique' ),
        Field::inst( 'product.type' )->options( 'product_type', 'id', 'name' ),
        Field::inst( 'product_type.name' )
    )
    ->leftJoin( 'product_type', 'product_type.id', '=', 'product.type' )
    ->where( 'product_type.uid', $uid, '=' )
    ->where( 'product.uid', $uid, '=' )
    ->process( $_POST ) 
    ->json();

allan

Hi,

Thanks for the clarification. I think the answer is that you still need to use a closure method in your product.type options() method. At the moment, as you say, it is pulling out all of the data for that field, but you need a "custom" query that will apply a where filter, based on the logged in user.

Have I understood correctly now?

Thanks,
Allan

xnrsisbi

Not sure i fully understand you :-(

as far as i can understand from your examples you say that what i need is this:

->options( function () use ( $db ) {
$userList = $db->selectDistinct( 'product_type', 'id, name', where product_type.uid = $uid, 'name ASC' );
$out = array();

while ( $row = $userList->fetch() ) {

$out[] = array(

"value" => $row['id'],

"label" => $row['name']

);

}

return $out;

} ),

allan

Try this:

->options( function () use ( $db ) {
    $userList = $db->selectDistinct( 'product_type', 'id, name', array('product_type.uid' => $uid), 'name ASC' );
    $out = array();

    while ( $row = $userList->fetch() ) {
        $out[] = array(
            "value" => $row['id'],
            "label" => $row['name']
        );
    }

    return $out;
} ),

Allan

xnrsisbi

PERFECT!!!!
Works like a charm

many thanks for your precious help Allan!!!