• Hello Fabrik Community

    Fabrik is now in the hands of the development team that brought you Fabrik for Joomla 4. We have recently transitioned the Fabrik site over to a new server and are busy trying to clean it up. We have upgraded the site to Joomla 4 and are running the latest version of Fabrik 4. We have also upgraded the Xenforo forum software to the latest version. Many of the widgets you might have been used to on the forum are no longer operational, many abandoned by the developers. We hope to bring back some of the important ones as we have time.

    Exciting times to be sure.

    The Fabrik 4.0 Official release is now available. In addition, the Fabrik codebase is now available in a public repository. See the notices about these in the announcements section

    We wish to shout out a very big Thank You to all of you who have made donations. They have really helped. But we can always use more...wink..wink..

    Also a big Thank You to those of you who have been assisting others in the forum. This takes a very big burden off of us as we work on bugs, the website and the future of Fabrik.

[Solved] Joomla Database Query in Eval

Status
Not open for further replies.

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
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":protected]=>
int(0)
["limit":protected]=>
int(0)
["db":protected]=>
object(JDatabaseDriverMysqli)#16 (23) {
["name"]=>
string(6) "mysqli"
["serverType"]=>
string(5) "mysql"
["connection":protected]=>
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":protected]=>
string(1) "`"
["nullDate":protected]=>
string(19) "0000-00-00 00:00:00"
["_database":"JDatabaseDriver":private]=>
string(16) "bluepi18_joom829"
["count":protected]=>
int(11)
["cursor":protected]=>
NULL
["debug":protected]=>
bool(false)
["limit":protected]=>
int(0)
["log":protected]=>
array(0) {
}
["timings":protected]=>
array(0) {
}
["callStacks":protected]=>
array(0) {
}
["offset":protected]=>
int(0)
["options":protected]=>
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":protected]=>
*RECURSION*
["tablePrefix":protected]=>
string(6) "josri_"
["utf":protected]=>
bool(true)
["utf8mb4":protected]=>
bool(true)
["errorNum":protected]=>
int(0)
["errorMsg":protected]=>
string(0) ""
["transactionDepth":protected]=>
int(0)
["disconnectHandlers":protected]=>
array(0) {
}
}
["sql":protected]=>
NULL
["type":protected]=>
string(6) "select"
["element":protected]=>
NULL
["select":protected]=>
object(JDatabaseQueryElement)#636 (3) {
["name":protected]=>
string(6) "SELECT"
["elements":protected]=>
array(1) {
[0]=>
string(3) "a.*"
}
["glue":protected]=>
string(1) ","
}
["delete":protected]=>
NULL
["update":protected]=>
NULL
["insert":protected]=>
NULL
["from":protected]=>
object(JDatabaseQueryElement)#635 (3) {
["name":protected]=>
string(4) "FROM"
["elements":protected]=>
array(1) {
[0]=>
string(27) "`csp_document_types` AS `a`"
}
["glue":protected]=>
string(1) ","
}
["join":protected]=>
array(1) {
[0]=>
object(JDatabaseQueryElement)#623 (3) {
["name":protected]=>
string(9) "LEFT JOIN"
["elements":protected]=>
array(1) {
[0]=>
string(55) "`#__viewlevels` AS `b` ON (`a`.`view_level` = `b`.`id`)"
}
["glue":protected]=>
string(1) ","
}
}
["set":protected]=>
NULL
["where":protected]=>
NULL
["group":protected]=>
NULL
["having":protected]=>
NULL
["columns":protected]=>
NULL
["values":protected]=>
NULL
["order":protected]=>
NULL
["autoIncrementField":protected]=>
NULL
["call":protected]=>
NULL
["exec":protected]=>
NULL
["union":protected]=>
NULL
["unionAll":protected]=>
NULL
["selectRowNumber":protected]=>
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:
Alright folks. As usual, once I press the post button a new idea comes to mind which I tried and now have the result I want. To save others a few hours of hair-pulling here is what I did to fix it:

I changed this snip:
Code:
$query
  ->select('a.*', 'b.*')
  ->from($db->quoteName('csp_document_types','a'))

To this
Code:
$query
  ->select('a.*')
  ->select('b.*)
  ->from($db->quoteName('csp_document_types','a'))

So, it looks like in the J! documentation, the API will accept column specification with comma separators but this is failing in my tests. Placing the second tables output fields on a separate ->select() line works in my tests...
 
I don't think separate comma separated strings has ever worked, although a single string with comma separated values does ...

$query->select('a.*, b.*');

-- hugh
 
You are right. I re-read the api documentation again.

You can pass an array to ->select like this ->select(array('a.*', 'b.*'));

I was missing the array() that was sitting in plain sight.

I think I was reading munging the 'AS' part of the spec and confusing myself:

SELECT ('a.foo', 'foo');

Thinking it was a list fo fields when it was actually the alias
 
Yup, it can get confusing. Although in general, there aren't any J! API calls which take a variable number of arguments (a tiny handful, like cache callbacks, although even that is being changed in 4.0). So if you think you've read it as "pass an arbitrary number of arguments", you can safely assume you've misread it.

-- hugh
 
Status
Not open for further replies.
We are in need of some funding.
More details.

Thank you.

Members online

Back
Top