{"sError":"Join was performed on the field 'reportedby' which was not included in the Editor field..

luisrortega

Items table have a reportedby, this is a selection from the table users (to ID field)...


Also, I'll like to be able to filter the joined table by an "Active" field, so I don't display users that are no longer active...


[code]
$editor = Editor::inst( $db, 'items' )
->field(
Field::inst( 'id' )->set( false ),
Field::inst( 'title' )->validator( 'Validate::required' ),
Field::inst( 'description' ),
Field::inst( 'reportedby'),
Field::inst( 'product' ),
Field::inst( 'itemtype' ),
Field::inst( 'severity' ),
Field::inst( 'priority' ),
Field::inst( 'customer' ),
Field::inst( 'assignedto' ),
Field::inst( 'testedby' ),
Field::inst( 'work_minutes' ),
Field::inst( 'company')//->validator( 'Validate::required' )
)
->join(
Join::inst( 'users', 'object' )
->join( 'reportedby', 'id' )
->fields(
Field::inst( 'name' )
)
)
;

$out = $editor
->where($key='company',$value = $user['Data'][0]['company'], $op = '=' )
->process($_POST)
->data();


if ( !isset($_POST['action']) ) {
// Get department details
$out['users'] = $db
->select( 'users', 'id as value, name as label' )
->fetchAll();
}

[/code]

luisrortega

if I remark
[code]
// Field::inst( 'reportedby'),
[/code]

or change to

[code]
Field::inst( 'reportedby')->set(false),
[/code]

I still get the same error...

allan

Interesting - that does rather look like it should work! Which version of the Editor libraries are you using? If not 1.2.4, could you update to that version please: https://editor.datatables.net/download .

> Also, I'll like to be able to filter the joined table by an "Active" field, so I don't display users that are no longer active...

A `where()` condition can be applied to the Join, using `->where( 'Active', 't' )` or something similar (and suitable for your data!). However, it is not currently possible to limit the parent table based on the data in the joined table. That is something I'm going to look at adding for 1.3.

Regards,
Allan

luisrortega

Yes, I have 1.2.4.

The were clause worked as expected (thou I had to prefix the table
[code]
->where('users.active','T');
[/code]


Later today I'm going to re-download the full package... just in case!

luisrortega

Just in case I did't describe it correctly here is my table layout...

Table Items
id int autoincrement
description char(40)
reportedby int
assignedto int
...

Table users
id int autoincrement
name char(80)
active char(1)
...


On the editor I intent to have 2 drop down selections reportedby and assignedto, both of them pointing to an "id" on the users table...

allan

I might have it - could you add:

[code]
Field::inst( 'id' )
[/code]

into your field list for the join?

Allan

luisrortega

Still having trouble... There must be something I'm missing... not sure what...

[code]
$editor = Editor::inst( $db, 'items' )
->field(
Field::inst( 'id' )->set( false ),
Field::inst( 'title' )->validator( 'Validate::required' ),
Field::inst( 'description' ),
Field::inst( 'reportedby')->set(false),
Field::inst( 'product' ),
Field::inst( 'itemtype' ),
Field::inst( 'severity' ),
Field::inst( 'priority' ),
Field::inst( 'customer' ),
Field::inst( 'assignedto' ),
Field::inst( 'testedby' ),
Field::inst( 'work_minutes' ),
Field::inst( 'company')
)
->join(
Join::inst( 'users', 'object' )
->join( 'reportedby', 'id' )
->field(
Field::inst( 'id' ),
Field::inst( 'name' )
)
->where( 'users.company', $user['Data'][0]['company'] )
)
;

$out = $editor
->where($key='company',$value = $user['Data'][0]['company'], $op = '=' )
->process($_POST)
->data();

if ( !isset($_POST['action']) ) {
// Get department details
$out['users'] = $db
->select( 'users', 'id as value, name as label' )
->fetchAll();
}
echo json_encode($out);

[/code]

luisrortega

Found the problem... now that open another situation!

The problem was: After creating the table, I manually inserted some records, if the join field (reportedby) have a null value, we got the error... I ran an update statements to have a valid value... and it started working fine!

The new problem: This field should be fine to force a value however I'll be doing another join to a field that might or might not have a value (assignedto)... how can I allow the user to clear a value, and how I adjust my php so that it does not goes "crazy" with null values.


Another quick question is (not sure if I should open another discussion)
The target table here (Items) have 3 fields that link to users (reportedby, testedby, and assignedto), when doing the multiple joins, how do I point to the second and third fields when linking the fieldname on the table...

[code]
->join(
Join::inst( 'users', 'object' )
->join( 'reportedby', 'id' )
->field(
Field::inst( 'id' )->set(false),
Field::inst( 'name' )->set(false)
),
Join::inst( 'users', 'object' )
->join( 'assignedto', 'id' )
->field(
Field::inst( 'id' )->set(false),
Field::inst( 'name' )->set(false)
),
Join::inst( 'products', 'object' )
->join( 'product', 'id' )
->field(
Field::inst( 'id' )->set(false),
Field::inst( 'name' )->set(false)
)

);
[/code]

in my dataset, how do I set the 'assignedto'?

In datatable
[code]
"aoColumns": [
{ "sTitle": "Tittle" ,"mData":"title"},
{ "sTitle": "Description" ,"mData":"description"},
{ "sTitle": "Reported By" ,"mData":"users.name"},
{ "sTitle": "Product" ,"mData":"products.name"},
{ "sTitle": "Priority" ,"mData":"priority"}
{ "sTitle": "AssignedTo" ,"mData":"users.name"} //<--- how I point this to AssignedTo
],
[/code]

in editor
[code]
"label": "Reported By:",
// The 'id' value from the property is used to set the value
// of the select list.
"name": "users.id",
"type": "select"
},
"label": "Assigned To:",
// The 'id' value from the property is used to set the value
// of the select list.
"name": "users.id", //<---- how I set this one?
"type": "select"
},
{
"label": "Product:",
// The 'id' value from the property is used to set the value
// of the select list.
"name": "products.id",
"type": "select"
},
[/code]

allan

The Joined table is presumably just for a list of options and you have a `user-id` column, or something like that in your primary table? If so, that is the value you would use. You populate the list from the `users` object, but the value you are setting is the one on the primary table, not the join.

Allan