如何只从第二个表中查找最后一行,以及它的第一个表中的行 [英] How to find only last row from second table, along with its first table row
问题描述
我有两个桌子. 期刊和卷. 日记表具有唯一的行,卷表具有基于日记表ID的行,名称为journal_id(可以为多个).
I have two tables. journals and volumes. Journal Table has unique rows, and volumes table have rows based on journal table id, name is journal_id(may be multiple).
期刊表为:
id | journal_name
1 | journal1
2 | journal2
体积表为:
id | journal_id | volume_name
1 | 1 | volume1
2 | 1 | volume2
3 | 1 | volume3
4 | 2 | volume4
5 | 2 | volume5
现在,我需要与日志表中的行连接,并且仅基于journal_id的卷的最后一行.
Now I need join with row from journal table and only last rows of volumes based on journal_id.
结果应为:
id | journal_name | journal_id | volume_name
1 | journal1 | 1 | volume3
2 | journal2 | 2 | volume5
不是卷表中的所有行. (只需要每个journal_id组的最后一行).
从mysql查询获得的结果是:
SELECT J.journal_name,V.id,V.journal_id FROM journals AS J
INNER JOIN (SELECT *
FROM volumes
WHERE id IN (
SELECT MAX(id)
FROM volumes
GROUP BY journal_id
)) AS V ON J.id = V.journal_id
现在我在laravel中的尝试是:
控制器为:
public function index()
{
$volumes = volume::with('volumes')->orderBy('id','desc')->limit(1)->get();
return view('welcome',compact('volumes'));
}
体积模型为:
function volumes()
{
return $this->belongsTo(journal::class, 'journal_id');
}
但是从整个卷表中只给出一行.我需要卷表中每一组journal_id的最后一行.
But it is giving only one row from entire volume table. I need last one row from each group of journal_id in volume table.
推荐答案
您可以使用HasOne
关系:
class Journal extends Model
{
public function latestVolume()
{
return $this->hasOne(Volume::class)->orderByDesc('id');
}
}
$journals = Journal::with('latestVolume')->get();
请注意,这仍然会从数据库中获取所有卷.然后,它将丢弃旧"的那些.
Be aware that this will still fetch all volumes from the database. It then discards the "old" ones.
如果仅通过获取最新卷来提高性能,可以使用我创建的以下程序包:
If you want to improve the performance by really only fetching the latest volume, you can use this package I've created: https://github.com/staudenmeir/eloquent-eager-limit
该程序包允许您将limit()
应用于该关系:
The package allows you to apply limit()
to the relationship:
class Journal extends Model
{
use \Staudenmeir\EloquentEagerLimit\HasEagerLimit;
public function latestVolume()
{
return $this->hasOne(Volume::class)->orderByDesc('id')->limit(1);
}
}
这篇关于如何只从第二个表中查找最后一行,以及它的第一个表中的行的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!