如果不返回行，Laravel会更改关系where子句

Question

I have the following query in my controller.

$items = Item::with(['subitems' => function($query) {
    $query->where('language_id', '=', 1);
}])->get();

This is correctly getting me all items including subitems that have a language id of 1.

There are two things I would like to do with this though

• Firstly, I need to return all subitems that have a distinct 'ref_id' value.
• Secondly, I would like to give preference to the language id 1 but if none exist, use any language id.

So for example, I know this code won't work, but the sort of thing I am looking for is:

$items = Item::with(['subitems' => function($query) {
    $subItems = $query->where('language_id', '=', 1)
        ->where('ref_id', 'is', 'distinct');

    if($subItems->count() <= 0) {
      $subItems = $query->where('ref_id', 'is', 'distinct');
    }
}])->get();

Is this possible or is it a bit too complicated for Query Builder? Even if one of the two requests was possible, that would be great.

Answer 1

Try this:

$items = Item::with(['subitems' => function($query) {
    $join = Subitem::select('ref_id', DB::raw('MIN(language_id) language_id'))
        ->groupBy('ref_id');
    $sql = '(' . $join->toSql() . ') subitems_distinct';
    $query->join(DB::raw($sql), function($join) {
        $join->on('subitems.ref_id', 'subitems_distinct.ref_id')
            ->on('subitems.language_id', 'subitems_distinct.language_id');
    });
}])->get();

We can use the fact that your preferred language_id is the lowest value and select the minimum.