Unknown Column in Where Clause for Simple Join

spoony82

Hello hello,

Quick question - I can't get my 'group_id' column to work for a where clause for my simple join of two tables (contacts & contacts_groups).

The join is working fine and the JSON returned appears to be correct. I just can't get the ->where statement to accept a value for group_id ? It works fine if I try to filter by columns in the contacts table though.

Any help would be appreciated, I'm new to DT, cheers.

[code]
$editor = Editor::inst( $db, 'contacts', 'contact_id' )
->fields(
Field::inst( 'contact_firstname' )
->validator( 'Validate::required' ),
Field::inst( 'contact_email' )
->validator( 'Validate::email' ),
Field::inst( 'contact_mobile' )
)
->join(
Join::inst( 'contacts_groups', 'array' )
->join( 'contact_id', 'contact_id' )
->field(
Field::inst( 'group_id' )
)
);

$out = $editor
->where( $key = "group_id", $value = '7', $op = '=' )
->process($_POST)
->data();
[/code]

allan

> ->where( $key = "group_id", $value = '7', $op = '=' )

I think you want:

[code]
->where( "group_id", '7', '=' )
[/code]

At the moment you are assigning the value of "group_id" to the local variable `$key` - and then passing through the require (I'm not even sure what that would do in PHP!).

Regards,
Allan

spoony82

Hi Allan, same thing unfortunately, using:

[code]
$out = $editor
->where( "group_id", '7', '=' )
->process($_POST)
->data();
[/code]

Returns:

Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[42S22]: Column not found: 1054 Unknown column 'group_id' in 'where clause'' in D:\Websites\HootCast\web2\php\lib\Database\Driver\Mysql\Query.php:98

allan

Ah - sorry - I missed on first reading that the group_id is in the join rather than the parent table.

The PHP libraries that come with Editor 1.2.3 don't actually have a `where` method for the join class, but it is implemented in the current development version and will ship with v1.3 of Editor. I can zip the current development libraries up and send them over to you if you like? Its just a case of moving the `where` method call into the Join chain that you have - the syntax is the same.

Can I ping you the updated libraries at the e-mail address you used to sign up for a DataTables account with?

Thanks,
Allan

spoony82

Hi Allan, that would be great, thanks so much!

spoony82

Hi Allan,

I now have the data displaying but the where clause isn't applying to the data and I'm getting all 15 records back and I should be getting one record.

[code]

$editor = Editor::inst( $db, 'contacts', 'contact_id' )
->fields(
Field::inst( 'contact_firstname' )
->validator( 'Validate::required' ),
Field::inst( 'contact_email' )
->validator( 'Validate::email' ),
Field::inst( 'contact_mobile' )
)
->join(
Join::inst( 'contacts_groups', 'array' )
->join( 'contact_id', 'contact_id' )
->field(
Field::inst( 'group_id' )
)
->where( 'group_id', '7', '=' )
);


$out = $editor
->process($_POST)
->data();

if ( !isset($_POST['action']) ) {
$out['contacts_groups'] = $db
->select( 'groups', 'group_id as value, group_title as label' )
->fetchAll();
}

[/code]

The SQL I'm trying to recreate and that returns one record is:

"SELECT * FROM contacts INNER join contacts_groups ON contacts_groups.contact_id = contacts.contact_id WHERE group_id = 7"

The JSON returned begins with this but the rest of the data is fine.

"{"id":-1,"fieldErrors":[],"sError":"","aaData":[...."

allan

Okay - so what Editor is doing there is its is querying the main table and getting all records in the table. Then it is adding the contacts_groups where group_id is 7 for each row. So I rather suspect that what you are seeing is that in the data returned, only one of the rows has data in the `contacts_groups` parameter. Is that correct?

There isn't actually, currently a method of using a where condition at the top level, using nested data - this is something that I feel should be added. I'm currently looking at the Editor Join class to see how it can be improved for the up-coming v1.3 release - trying to make everything occur in a single SQL statement. The difficulty I'm facing with that is the lack of support for Array types across different SQL engines. You've got your Join as an array type above, so I presume you are expecting it to return more than one record for each row in the master table? 1.3's libraries will support a single query for object data, but without an array type, I'm not sure how it can be done for array information, unless yourself or anyone else has any suggestions?

Regards,
Allan

spoony82

Spot on Allan, only the first record has the group_id of 7 in the contacts_groups parameter (see below).

I use DT extensively on another site with this kind of query in every table, but none of them use Editor yet. I would've thought it would be quite common to want to filter a record set like this? Apparently not if I'm opening up a can of worms (sorry!).

I can't think of any way around this because I don't fully understand what happens with the PHP libraries included with Editor.

At this point I would need to drop Editor and go back to a manual CRUD implementation because I need nearly all my DT's on this site to be filled with information only relevant to the logged in user. :/

JSON:

[code]
{ "aaData" : [ { "DT_RowId" : "row_1",
"contact_email" : "****@gmail.com",
"contact_firstname" : "David Bartlett",
"contact_mobile" : "****",
"contacts_groups" : [ { "group_id" : "7" } ]
},
{ "DT_RowId" : "row_8",
"contact_email" : "****@gmail.com",
"contact_firstname" : "Dave",
"contact_mobile" : "****",
"contacts_groups" : [ ]
},
{ "DT_RowId" : "row_9",
"contact_email" : "email@email.com",
"contact_firstname" : "Dave",
"contact_mobile" : "",
"contacts_groups" : [ ]
},
{ "DT_RowId" : "row_11",
"contact_email" : "sdfsdg@sdgds.com",
"contact_firstname" : "John",
"contact_mobile" : "04818948",
"contacts_groups" : [ ]
},
{ "DT_RowId" : "row_12",
"contact_email" : "dsiughisdug@dsguds.com",
"contact_firstname" : "Mary",
"contact_mobile" : "058964984",
"contacts_groups" : [ ]
},
{ "DT_RowId" : "row_13",
"contact_email" : "email@email.email.com",
"contact_firstname" : "Johnny",
"contact_mobile" : "05168468",
"contacts_groups" : [ ]
},
{ "DT_RowId" : "row_14",
"contact_email" : "email@email.email.com",
"contact_firstname" : "Johnny",
"contact_mobile" : "05168468",
"contacts_groups" : [ ]
},
{ "DT_RowId" : "row_15",
"contact_email" : "email@dot.com",
"contact_firstname" : "Testu",
"contact_mobile" : "235325325325",
"contacts_groups" : [ ]
},
{ "DT_RowId" : "row_16",
"contact_email" : "email@dot.com",
"contact_firstname" : "Full name2",
"contact_mobile" : "2352352365",
"contacts_groups" : [ ]
},
{ "DT_RowId" : "row_17",
"contact_email" : "email@dot.com",
"contact_firstname" : "Full name",
"contact_mobile" : "2352352365",
"contacts_groups" : [ ]
},
{ "DT_RowId" : "row_18",
"contact_email" : "bob@bob.com",
"contact_firstname" : "Bob2",
"contact_mobile" : "0418196819",
"contacts_groups" : [ ]
},
{ "DT_RowId" : "row_19",
"contact_email" : "email@email.com",
"contact_firstname" : "Test",
"contact_mobile" : "234325",
"contacts_groups" : [ ]
},
{ "DT_RowId" : "row_23",
"contact_email" : "****@optusnet.com.au",
"contact_firstname" : "david",
"contact_mobile" : "****",
"contacts_groups" : [ ]
},
{ "DT_RowId" : "row_25",
"contact_email" : "",
"contact_firstname" : "bob",
"contact_mobile" : "",
"contacts_groups" : [ ]
},
{ "DT_RowId" : "row_26",
"contact_email" : "*@optusnet.com.au",
"contact_firstname" : "dave",
"contact_mobile" : "",
"contacts_groups" : [ ]
}
],
"contacts_groups" : [ { "0" : "1",
"1" : "Test Marketing",
"label" : "Test Marketing",
"value" : "1"
},
{ "0" : "2",
"1" : "Group Test",
"label" : "Group Test",
"value" : "2"
},
{ "0" : "3",
"1" : "Group",
"label" : "Group",
"value" : "3"
},
{ "0" : "4",
"1" : "Another one",
"label" : "Another one",
"value" : "4"
},
{ "0" : "5",
"1" : "new group",
"label" : "new group",
"value" : "5"
},
{ "0" : "6",
"1" : "Another one 2",
"label" : "Another one 2",
"value" : "6"
},
{ "0" : "7",
"1" : "Another one 3",
"label" : "Another one 3",
"value" : "7"
}
],
"fieldErrors" : [ ],
"id" : -1,
"sError" : ""
}
[/code]

allan

Can `contacts_groups` be more than one item long? If not, and it can be defined as just an object, then the work I'm planning for the 1.3 version of the libraries will I think help resolve this issue. I can promote this work to a higher priority if it will indeed help you resolve the issue.

In this case your DataTable is only going to have a single record though. Is that correct? I presume that isn't the case for most of the other tables? What is the structure of the data there?

Thanks,
Allan

spoony82

Hi Allan,

In this case yes the DT would only have one record but that's just based on test data. In reality this data will be much larger.

Basically I have a master list of contacts (contacts table), a link table called contacts_groups and then a groups table (which I haven't even referenced in this thread or example yet).

Each contact in the contacts table can belong to any number of groups linked by the contacts_groups table.

When a user logs in they can see the groups (and the contacts) that they are assigned to as admins and administer them accordingly (edit main details, add or remove them from groups).

Does this make sense?

spoony82

Hi Allan,

I've moved back to normal datatables with my own crud features using modals so feel free to take your time with finding a solution to this one. I really needed to add distinct to the SQL query anyway and we hadn't discussed that either.

Cheers

allan

Perfect sense - thanks for clarifying it for me. I'll more certainly take this onboard and see how I can fully incorporate support this style of schema into future versions of Editor.

I should also say that you can use Editor's client-side libraries with your own server-side libraries, allowing complete flexibility for how you want to manipulate / read the database. The main part of Editor is in fact the client-side libraries with the server-side libraries designed to be used for the most common cases, but not all (acknowledging that server environments can vary significantly, or that Editor might be integrated into an existing project). There is information about that here: http://editor.datatables.net/server/ .

However, as I say, thanks for the feedback! I really appreciate your taking the time to help me make Editor better.

Regards,
Allan

lowrymel

Hi Alan,

Can I also get an advanced look at 1.3.? Like spoony82, I am attempting a where clause on a linked table property.

Thanks!

Mike

allan

Can I drop you a mail at the e-mail address you used to sign up for your DataTables account? Also, keep in mind that the where condition is only applied to the linked data, not both parent and joined at the same time (a problem I'm still looking at how to resolve across SQL engines).

Allan

lowrymel

Yes please. Sorry for the delayed response.