Joomla数据库 - 如何在getQuery中使用LIMIT？

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

我想使用joomla内置数据库类构建以下查询。

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

我不知道如何将限制（LIMIT 1）添加到查询中。有人可以告诉我该怎么做？谢谢

4 个解决方案

#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.

$ db-> setQuery函数有3个参数。第一个是查询，然后是开始，然后是限制。我们可以限制记录，如上所示。

Newer than Joomla 3.0

setLimit(integer $limit, integer $offset)

If you want just one row

如果你只想要一排

$query->setLimit(1);

Read more

阅读更多

#2

This should work as well:

这也应该有效：

$query->setLimit(1);

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

文档：http：//api.joomla.org/cms-3/classes/JDatabaseQueryLimitable.html

#3

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

SetLimit在Joomla 3.4.x中对我不起作用，所以试试：

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

戴维斯回答：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.

在模型调用getItems之前运行它，它将为您加载所有项目。

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);

$ 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.

$ model = $ this-> getModel（）; $模型 - >的getState（）; $ model-> setState（'list.limit'，0）;实际的populateState方法是受保护的，因此在模型之外您无法直接调用它，但是对getState的任何调用都将确保在返回状态中的当前设置之前调用populateState。

#4

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

截至08/9月14日来自@Dasun或@escopecz的解决方案在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 :

并且关于您希望仅获取1行的特定要求，您可以使用：

  $rows = $db->loadObject();