Joomla Database - How to use LIMIT in getQuery?

Question

I want to build the below query using joomla inbuilt database class.

SELECT * 
FROM table_name
ORDER BY id DESC
LIMIT 1

This is the query I have built up to now.

$db =& JFactory::getDBO();       
$query  = $db->getQuery(true);
$query->select($db->nameQuote('*'));
$query->from($db->nameQuote(TABLE_PREFIX.'table_name'));      
$db->setQuery($query);      
$rows = $db->loadObjectList();

I don't know how to add the limit(LIMIT 1) to the query. Can someone please tell me how to do it? Thanks

Solution 1

Older than Joomla 3.0

$db = JFactory::getDBO();    

$query  = $db->getQuery(true);
$query->select('*')
 ->from($db->nameQuote('#__table_name'))
 ->order($db->nameQuote('id').' desc');     
$db->setQuery($query,0,1);  

$rows = $db->loadObjectList();

$db->setQuery function takes 3 parameters. The first one being the query, then the start, then the limit. We can limit records as shown above.

Newer than Joomla 3.0

setLimit(integer $limit, integer $offset)

If you want just one row

$query->setLimit(1);

Read more

Solution 2

This should work as well:

$query->setLimit(1);

Documentation: http://api.joomla.org/cms-3/classes/JDatabaseQueryLimitable.html

Solution 3

SetLimit doesn't work for me in Joomla 3.4.x, so try:

Within the model:

protected function getListQuery()
{
    // Create a new query object.
    $db = JFactory::getDBO();
    $query = $db->getQuery(true);

    // Select some fields
    $query->select('*');
    $query->from('#__your_table');

    $this->setState('list.limit', 0); // 0 = unlimited

    return $query;
}

Davids answer: https://joomla.stackexchange.com/questions/4249/model-getlistquery-fetch-all-rows-with-using-jpagination

Run that before the model calls getItems and it will load all the items for you.

A few caveats with this.

You can also do this outside the model, so if for instance you were in your view. You could do the following:

$model = $this->getModel(); $model->setState('list.limit', 0);

Sometimes you can do this too early, before the model's state has been populated, which will cause the model to get rebuilt from the user state after you have set the limit, basically overriding the limit.

To fix this, you can force the model to populate its state first:

$model = $this->getModel(); $model->getState(); $model->setState('list.limit', 0); The actual populateState method is protected, so outside the model you can't call it directly, but any call to getState will make sure that the populateState is called before returning the current settings in the state.

Solution 4

Update: Just had to revisit this answer, and I can confirm, both the methods setLimit & order are working if used as below.

$query->order($db->qn($data->sort_column_name) . ' ' . $data->sort_column_order);
$query->setLimit($length,$start);

---

OLD ANSWER

As of 08/Sept/14 The solutions from @Dasun or @escopecz arent working for me on J3.x

but this old trick is working for me which is nice,

  $query->order($db->qn('id') . ' DESC LIMIT 25');

And About your specific requirement of wishing to fetch only 1 row you could use :

  $rows = $db->loadObject();

View more solutions

Comments

[piotr_cz]

since Joomla 3.0 (Platform 12.1)

[Krzysztof Jarosz]

this is strange (I will try to find out why) older method works with J3.3.1 but newer method doesn't.

[betweenbrain]

Krzysztof Jarosz - I'm also having the same issue of setLimit() not working with Joomla 3.3.2.

[Mohd Abdul Mujib]

@mickmackusa I absolutely agree correct usage should be followed, Its just that at that time (almost 5 years ago), I was unable to use the methods in question and was pretty frustrated, and upon finally discovering a workaround, I just posted it here hoping maybe it could help someone out in a similar situation.