一对多关系，分表只取关联的第一条记录，如何获取?

[luoyunchong]

有1对多 的关系，但是我需要取最近一条关联记录。这种是 左连接查询的。freesql 该如何处理呀

base_type1对base_item多，通过BaseTypeId 外键关联。

public class BaseType
{
    [Column(IsPrimary = true, IsIdentity = true)]
    public long Id { get; set; }
    public string TypeCode { get; set; }
    public int? SortCode { get; set; }
    public string FullName { get; set; }

    public ICollection<BaseItem> BaseItems { get; set; }
}

[Table(Name = "base_item")]
public class BaseItem
{
    [Column(IsPrimary = true, IsIdentity = true)]
    public long Id { get; set; }
    public string ItemName { get; set; }
    public long BaseTypeId { get; set; }
    public virtual BaseType BaseType { get; set; }
}

0.直接写sql

SELECT a.*
FROM `base_type` a
LEFT JOIN `base_item` b ON b.`id` = (SELECT b2.`id` FROM `base_item` b2 WHERE a.`id` = b2.`base_type_id` LIMIT 1)

1. 使用LeftJoin

参考上面的sql，转成left join

var sql = freeSql.Select<BaseType, BaseItem>()    
  .LeftJoin((a, b) => b.Id == (freeSql.Select<BaseItem>().As("b2").Where(b2 => b2.BaseTypeId == a.Id).First(b2 => b2.Id)))
  .ToSql((a, b) => new { a, b });

生成的sql

SELECT ...
FROM `base_type` a
LEFT JOIN `base_item` b ON b.`id` = (SELECT b2.`id` FROM `base_item` b2 WHERE ( b2.`base_type_id` = a.`id` ) LIMIT 0,1) 

---

2. 直接IncludeMany一对多。使用导航

var result = freeSql.Select<BaseType>()
  .IncludeMany(r => r.BaseItems.Take(1))
  .ToList();
}

生成的sql有点多，分开两次执行的：

SELECT ...
FROM `base_type` a ;

SELECT * 
FROM (
  SELECT a.`id`, a.`base_type_id`, a.`item_name`
  FROM `base_item` a 
  WHERE (a.`base_type_id` = 2) LIMIT 0,1
) ftb 
UNION ALL
SELECT * 
FROM (
  SELECT a.`id`, a.`base_type_id`, a.`item_name`
  FROM `base_item` a 
  WHERE (a.`base_type_id` = 3) LIMIT 0,1
) ftb 
UNION ALL
SELECT * 
FROM (
  SELECT a.`id`, a.`base_type_id`, a.`item_name`
  FROM `base_item` a 
  WHERE (a.`base_type_id` = 4) LIMIT 0,1
) ftb 
UNION ALL
SELECT * 
FROM (
  SELECT a.`id`, a.`base_type_id`, a.`item_name`
  FROM `base_item` a 
  WHERE (a.`base_type_id` = 5) LIMIT 0,1
) ftb 

[lhtuling]

碰到这个需求 不过测试发现 不可以排序后取第一条 比如需要按照ID倒叙排序最大的 上面的sql语句用法查出来的都是正序第一个

[JustMySpace]

获取分组后的最新一条数据如下
注意LeftJion中的子查询中lambda的临时变量命名要与AS中的字符串相同

  .LeftJoin((a, b) => b.Id == (freeSql.Select<BaseItem>().As("b2").OrderByDescending(b2 => b2.ID).Where(b2 => b2.BaseTypeId == a.Id).First(b2 => b2.Id)))
  .ToSql((a, b) => new { a, b });```