Laravel -使用' SQL_CALC_FOUND_ROWS '并使用鹰装载的关系返回错误的计数

In my Laravel app I was doing an ordinary query on a model (ModelA) whilst making use of SQL_CALC_FOUND_ROWS and then performing SELECT FOUND_ROWS() afterwards to retrieve the count of all records since the first query used LIMIT and OFFSET.

在Laravel应用程序中，我使用SQL_CALC_FOUND_ROWS，然后执行SELECT FOUND_ROWS()以检索所有记录的计数，因为第一个查询使用了LIMIT和OFFSET。

This worked perfectly fine, but now that I've added a relationship to the model I was querying above, if I do the same query but using with->('modelB'), this query is performed after the initial query and before SELECT FOUND_ROWS() so I get the count of the ModelB results instead of ModelA as I was expecting.

这个工作非常好,但是现在,我已经添加了一个关系模型我上面是查询的,如果我做相同的查询,但使用- >(“modelB”),这个查询执行之前在最初的查询和选择FOUND_ROWS()得到modelB计数的结果,而不是像我期望模型。

Is there a way to make this work as desired where I get the count of the first (main) query and not the relationship?

是否有一种方法可以使此工作在得到第一个(主)查询的计数而不是关系的情况下进行?

e.g. This works fine:

例如:来说,这就做得够好了

$query = ModelA::select([DB::raw("SQL_CALC_FOUND_ROWS *")])
    ->where('active', 1);

// conditional ->where()'s

$query->skip(intval($skip))
    ->take(intval($take))
    ->orderBy($column, $dir);

$results = $query->get();

$total = DB::select(DB::raw("SELECT FOUND_ROWS() AS 'total';"))[0]->total;

but changing the first line to this doesn't:

但是把第一行改成这一行并没有:

$query = ModelA::with('modelB')
    ->select([DB::raw("SQL_CALC_FOUND_ROWS *")])
    ->where('active', 1);

A workaround is to just do it without eager-loading and fetch each relationship individually but then I'd have one query per result when I loop through the results later in the code.

一种解决方案是不需要进行紧急加载并逐个获取每个关系，但是当我在后面的代码中循环遍历结果时，每个结果都会有一个查询。

2 个解决方案

#1

The row count available through FOUND_ROWS() is transient and not intended to be available past the statement following the SELECT SQL_CALC_FOUND_ROWS statement. If you need to refer to the value later, save it...

通过FOUND_ROWS()提供的行数是瞬态的，而不是打算在SELECT SQL_CALC_FOUND_ROWS语句之后的语句中可用。如果您需要稍后引用该值，请保存它…

Eloquent's eager loading will cause an extra select statement to be executed for each relationship in the with method.

图尔的热切加载将导致对with方法中的每个关系执行一个额外的select语句。

Therefore FOUND_ROWS() is returning the count for the last SELECT statement, the eagerly loaded relation.

因此，FOUND_ROWS()将返回最后一个SELECT语句的计数，即急切加载的关系。

To get around this you can use lazy eager loading. Instead of:

为了解决这个问题，您可以使用延迟加载。而不是:

$books = App\Book::with('author.contacts')->get();

Use:

使用:

$books = App\Book::all();
$count = DB::select(DB::raw('SELECT FOUND_ROWS()'));
$books->load('author.contacts');

#2

ModelA::with('modelB') results in a join of the 2 underlying tables. If you have multiple records in modelB corresponding to a single record in modelA, then the number of records returned by the query may be more, than the number of records in modelA. SQL_CALC_FOUND_ROWS returns the number of records in the overall query, you cannot restrict it to a single table within a query.

ModelA::with('modelB')导致两个基础表的联接。如果在modelB中有多个记录对应于在modelA中的一个记录，那么查询返回的记录数量可能大于在modelA中的记录数量。SQL_CALC_FOUND_ROWS返回整个查询中的记录数量，不能将其限制在查询中的单个表中。

Either you count the modelA records separately and return that number, or you need to select data in a subquery from modelA's table with SQL_CALC_FOUND_ROWS included in the subquery. I would go for a separate count. It is straightforward and simple.

您可以分别计算modelA记录并返回该数字，或者您需要从modelA的表中选择包含在子查询中的SQL_CALC_FOUND_ROWS的子查询中的数据。我要单独数一下。它简单明了。

#1