echo full joomla query (with limit etc)?

Question

I was wondering if there was a way to echo out the full query with limit and limitstart etc. I can echo out the line $query, but i want to see why the limit isn't working and I can't seem to get it to display the actual query that it's sending to the database.. Here's the code:

$params =& JComponentHelper::getParams('com_news');
$limit = $params->get('x_items', 5);
$limitstart = JRequest::getVar('limitstart', 0);

$query = "SELECT * FROM #__news WHERE published = 1 AND catid = ".$Itemid." ORDER BY date DESC";
$db->setQuery($query, $limitstart, $limit);
$rows = $db->loadObjectList();

$db->getQuery($query, $limitstart, $limit); is only displaying "SELECT * FROM jos_news WHERE published = 1 AND catid = 8 ORDER BY date DESC" which doesnt have the LIMIT params on the end of the query..

Any help would be appreciated :)

Gobbet

Answer 1

var_dump($db);die;

Do that after the loadObjectList() call. Inside the $db variable there must be a _sql attribute that is the last query executed.

Rodas

Answer 2

The JDatabaseQuery object has a __toString() function that outputs the query so you can do:

echo $db->getQuery();

Or if you want to pass it to a function you can explicitly cast it to a string first:

var_dump((string)$db->getQuery());

Precept

This should be the accepted answer. Works very well and is exactly why it was developed (I imagine). – Elegize

Answer 3

Agreed with the previous answers, but... In case you are developing your own components, since I often want to know for sure what exactly is executed too, here's a simple solution:

In your models put:

$db = JFactory::getDBO(); echo $db->getQuery();

Where you want to know the query... Don't put it in (for example) your view, since it might have loaded some other dropdown list by way of execution in the meantime...

For example:

For a list-view put it right before the foreach ($items... in the public function getItems() of the model. In a form-/item-view put it right before the return $data / return $item in the protected function loadFormData() / public function getItem($pk = null)

Hope this helps...

Jehovist

Answer 4

On new joomla versions, you need to echo __toString() on query object.

echo $query->__toString();

I get this info on this joomla answer.

Hope this helps

Lonergan

Answer 5

Joomla provides $query->dump(). To add convenience, I like to wrap it in enqueueMessage() so that the presented string is at the top of the webpage.

JFactory::getApplication()->enqueueMessage(
    $query->dump(),
    'notice'
);

IMPORTANT: You should never show the raw SQL string or a raw query error string to the public.

See some of my implementations at Joomla Stack Exchange.

Jackboot

$query->dump() is deprecated without replacement. – Freund