pastvne
Bruce Decker
Hi All:
I'm working on code on a cascading drop-down using the eval code. My problem now is not with Fabrik but (off topic) frustration with the new Joomla database api. I can get a query to run and return the columns of the first table but I can't find the joined columns in my result.
Tables
I want to join #viewlevels to csp_document_types
The key to #__viewlevels is stored in a field called 'view_level' within csp_document_types.
My code is:
I've checked and re-checked the columnname a.view_level in csp_document_types.
The dump results are:
rows
array(1) {
[0]=>
object(stdClass)#645 (9) {
["id"]=>
string(1) "1"
["date_time"]=>
string(19) "2017-11-27 00:58:04"
["division_code_old"]=>
string(1) "1"
["document_id"]=>
string(13) "test document"
["status"]=>
string(9) "published"
["role"]=>
string(1) "1"
["template"]=>
string(1) "1"
["view_level"]=>
string(1) "2"
["division_code"]=>
string(1) "1"
}
}
And the $query object looks like this:
$queryobject(JDatabaseQueryMysqli)#633 (25) {
["offset"rotected]=>
int(0)
["limit"rotected]=>
int(0)
["db"rotected]=>
object(JDatabaseDriverMysqli)#16 (23) {
["name"]=>
string(6) "mysqli"
["serverType"]=>
string(5) "mysql"
["connection"rotected]=>
object(mysqli)#17 (19) {
["affected_rows"]=>
int(1)
["client_info"]=>
string(79) "mysqlnd 5.0.12-dev - 20150407 - $Id: b5c5906d452ec590732a93b051f3827e02749b83 $"
["client_version"]=>
int(50012)
["connect_errno"]=>
int(0)
["connect_error"]=>
NULL
["errno"]=>
int(0)
["error"]=>
string(0) ""
["error_list"]=>
array(0) {
}
["field_count"]=>
int(9)
["host_info"]=>
string(25) "Localhost via UNIX socket"
["info"]=>
NULL
["insert_id"]=>
int(0)
["server_info"]=>
string(10) "5.6.37-log"
["server_version"]=>
int(50637)
["stat"]=>
string(160) "Uptime: 1724061 Threads: 34 Questions: 2131263781 Slow queries: 137902 Opens: 22269391 Flush tables: 1 Open tables: 4096 Queries per second avg: 1236.188"
["sqlstate"]=>
string(5) "00000"
["protocol_version"]=>
int(10)
["thread_id"]=>
int(32108852)
["warning_count"]=>
int(0)
}
["nameQuote"rotected]=>
string(1) "`"
["nullDate"rotected]=>
string(19) "0000-00-00 00:00:00"
["_database":"JDatabaseDriver"rivate]=>
string(16) "bluepi18_joom829"
["count"rotected]=>
int(11)
["cursor"rotected]=>
NULL
["debug"rotected]=>
bool(false)
["limit"rotected]=>
int(0)
["log"rotected]=>
array(0) {
}
["timings"rotected]=>
array(0) {
}
["callStacks"rotected]=>
array(0) {
}
["offset"rotected]=>
int(0)
["options"rotected]=>
array(9) {
["driver"]=>
string(6) "mysqli"
["host"]=>
string(9) "localhost"
["user"]=>
string(16) "bluepi18_joom829"
["password"]=>
string(10) ".pYda97!S3"
["database"]=>
string(16) "bluepi18_joom829"
["prefix"]=>
string(6) "josri_"
["select"]=>
bool(true)
["port"]=>
int(3306)
["socket"]=>
NULL
}
["sql"rotected]=>
*RECURSION*
["tablePrefix"rotected]=>
string(6) "josri_"
["utf"rotected]=>
bool(true)
["utf8mb4"rotected]=>
bool(true)
["errorNum"rotected]=>
int(0)
["errorMsg"rotected]=>
string(0) ""
["transactionDepth"rotected]=>
int(0)
["disconnectHandlers"rotected]=>
array(0) {
}
}
["sql"rotected]=>
NULL
["type"rotected]=>
string(6) "select"
["element"rotected]=>
NULL
["select"rotected]=>
object(JDatabaseQueryElement)#636 (3) {
["name"rotected]=>
string(6) "SELECT"
["elements"rotected]=>
array(1) {
[0]=>
string(3) "a.*"
}
["glue"rotected]=>
string(1) ","
}
["delete"rotected]=>
NULL
["update"rotected]=>
NULL
["insert"rotected]=>
NULL
["from"rotected]=>
object(JDatabaseQueryElement)#635 (3) {
["name"rotected]=>
string(4) "FROM"
["elements"rotected]=>
array(1) {
[0]=>
string(27) "`csp_document_types` AS `a`"
}
["glue"rotected]=>
string(1) ","
}
["join"rotected]=>
array(1) {
[0]=>
object(JDatabaseQueryElement)#623 (3) {
["name"rotected]=>
string(9) "LEFT JOIN"
["elements"rotected]=>
array(1) {
[0]=>
string(55) "`#__viewlevels` AS `b` ON (`a`.`view_level` = `b`.`id`)"
}
["glue"rotected]=>
string(1) ","
}
}
["set"rotected]=>
NULL
["where"rotected]=>
NULL
["group"rotected]=>
NULL
["having"rotected]=>
NULL
["columns"rotected]=>
NULL
["values"rotected]=>
NULL
["order"rotected]=>
NULL
["autoIncrementField"rotected]=>
NULL
["call"rotected]=>
NULL
["exec"rotected]=>
NULL
["union"rotected]=>
NULL
["unionAll"rotected]=>
NULL
["selectRowNumber"rotected]=>
NULL
}
I'm sure this is something dumb on my part but I can't can't see the flaw in the directives.
Also, I find this new Joomla database api really cumbersome compared with the old. I can't find any place where I can do a $query->toString() or something where I can see the query. Do I need to look at MYSQL logs somewhere to see the actual query that I can throw into Workbench? Are there other debugging tools with this API? I can't seem to find them.
Sorry a bit off topic but appreciate the help.
I'm working on code on a cascading drop-down using the eval code. My problem now is not with Fabrik but (off topic) frustration with the new Joomla database api. I can get a query to run and return the columns of the first table but I can't find the joined columns in my result.
Tables
Code:
table a: (csp_document_types)
table b: (#__viewlevels)
I want to join #viewlevels to csp_document_types
The key to #__viewlevels is stored in a field called 'view_level' within csp_document_types.
My code is:
Code:
$db = JFactory::getDbo();
$query = $db->getQuery(true);
$query
->select('a.*', 'b.*')
->from($db->quoteName('csp_document_types','a'))
->join('LEFT',$db->quoteName('#__viewlevels','b') . ' ON (' . $db->quoteName('a.view_level') . ' = ' . $db->quoteName('b.id') . ')') ;
$db->setQuery($query);
$rows = $db->loadObjectList();
var_dump($rows);
var_dump($query);
I've checked and re-checked the columnname a.view_level in csp_document_types.
The dump results are:
rows
array(1) {
[0]=>
object(stdClass)#645 (9) {
["id"]=>
string(1) "1"
["date_time"]=>
string(19) "2017-11-27 00:58:04"
["division_code_old"]=>
string(1) "1"
["document_id"]=>
string(13) "test document"
["status"]=>
string(9) "published"
["role"]=>
string(1) "1"
["template"]=>
string(1) "1"
["view_level"]=>
string(1) "2"
["division_code"]=>
string(1) "1"
}
}
And the $query object looks like this:
$queryobject(JDatabaseQueryMysqli)#633 (25) {
["offset"rotected]=>
int(0)
["limit"rotected]=>
int(0)
["db"rotected]=>
object(JDatabaseDriverMysqli)#16 (23) {
["name"]=>
string(6) "mysqli"
["serverType"]=>
string(5) "mysql"
["connection"rotected]=>
object(mysqli)#17 (19) {
["affected_rows"]=>
int(1)
["client_info"]=>
string(79) "mysqlnd 5.0.12-dev - 20150407 - $Id: b5c5906d452ec590732a93b051f3827e02749b83 $"
["client_version"]=>
int(50012)
["connect_errno"]=>
int(0)
["connect_error"]=>
NULL
["errno"]=>
int(0)
["error"]=>
string(0) ""
["error_list"]=>
array(0) {
}
["field_count"]=>
int(9)
["host_info"]=>
string(25) "Localhost via UNIX socket"
["info"]=>
NULL
["insert_id"]=>
int(0)
["server_info"]=>
string(10) "5.6.37-log"
["server_version"]=>
int(50637)
["stat"]=>
string(160) "Uptime: 1724061 Threads: 34 Questions: 2131263781 Slow queries: 137902 Opens: 22269391 Flush tables: 1 Open tables: 4096 Queries per second avg: 1236.188"
["sqlstate"]=>
string(5) "00000"
["protocol_version"]=>
int(10)
["thread_id"]=>
int(32108852)
["warning_count"]=>
int(0)
}
["nameQuote"rotected]=>
string(1) "`"
["nullDate"rotected]=>
string(19) "0000-00-00 00:00:00"
["_database":"JDatabaseDriver"rivate]=>
string(16) "bluepi18_joom829"
["count"rotected]=>
int(11)
["cursor"rotected]=>
NULL
["debug"rotected]=>
bool(false)
["limit"rotected]=>
int(0)
["log"rotected]=>
array(0) {
}
["timings"rotected]=>
array(0) {
}
["callStacks"rotected]=>
array(0) {
}
["offset"rotected]=>
int(0)
["options"rotected]=>
array(9) {
["driver"]=>
string(6) "mysqli"
["host"]=>
string(9) "localhost"
["user"]=>
string(16) "bluepi18_joom829"
["password"]=>
string(10) ".pYda97!S3"
["database"]=>
string(16) "bluepi18_joom829"
["prefix"]=>
string(6) "josri_"
["select"]=>
bool(true)
["port"]=>
int(3306)
["socket"]=>
NULL
}
["sql"rotected]=>
*RECURSION*
["tablePrefix"rotected]=>
string(6) "josri_"
["utf"rotected]=>
bool(true)
["utf8mb4"rotected]=>
bool(true)
["errorNum"rotected]=>
int(0)
["errorMsg"rotected]=>
string(0) ""
["transactionDepth"rotected]=>
int(0)
["disconnectHandlers"rotected]=>
array(0) {
}
}
["sql"rotected]=>
NULL
["type"rotected]=>
string(6) "select"
["element"rotected]=>
NULL
["select"rotected]=>
object(JDatabaseQueryElement)#636 (3) {
["name"rotected]=>
string(6) "SELECT"
["elements"rotected]=>
array(1) {
[0]=>
string(3) "a.*"
}
["glue"rotected]=>
string(1) ","
}
["delete"rotected]=>
NULL
["update"rotected]=>
NULL
["insert"rotected]=>
NULL
["from"rotected]=>
object(JDatabaseQueryElement)#635 (3) {
["name"rotected]=>
string(4) "FROM"
["elements"rotected]=>
array(1) {
[0]=>
string(27) "`csp_document_types` AS `a`"
}
["glue"rotected]=>
string(1) ","
}
["join"rotected]=>
array(1) {
[0]=>
object(JDatabaseQueryElement)#623 (3) {
["name"rotected]=>
string(9) "LEFT JOIN"
["elements"rotected]=>
array(1) {
[0]=>
string(55) "`#__viewlevels` AS `b` ON (`a`.`view_level` = `b`.`id`)"
}
["glue"rotected]=>
string(1) ","
}
}
["set"rotected]=>
NULL
["where"rotected]=>
NULL
["group"rotected]=>
NULL
["having"rotected]=>
NULL
["columns"rotected]=>
NULL
["values"rotected]=>
NULL
["order"rotected]=>
NULL
["autoIncrementField"rotected]=>
NULL
["call"rotected]=>
NULL
["exec"rotected]=>
NULL
["union"rotected]=>
NULL
["unionAll"rotected]=>
NULL
["selectRowNumber"rotected]=>
NULL
}
I'm sure this is something dumb on my part but I can't can't see the flaw in the directives.
Also, I find this new Joomla database api really cumbersome compared with the old. I can't find any place where I can do a $query->toString() or something where I can see the query. Do I need to look at MYSQL logs somewhere to see the actual query that I can throw into Workbench? Are there other debugging tools with this API? I can't seem to find them.
Sorry a bit off topic but appreciate the help.
Last edited: