使用Laravel Fluent如何更改它，使其仅访问数据库一次

Question

在以下代码行中：

$ratio = $this->getColorRatioForDeck($communityDecks[0]->username, $communityDecks[0]->deck_uid);

您会注意到我正在按索引获取此比率，并且我可以通过使用如下所示的 for 循环来获取所有这些比率：

$ratio = [];
foreach($communityDecks as $deck) {
    $ratio[] = $this->getColorRatioForDeck($deck->username, $deck->deck_uid);
}

但是，我会点击数据库 n 次，具体取决于有多少$decks。

如何在下面的代码中组合 2 个查询，以便一次性获取信息？

这是我目前拥有的：

private $take = 10;
public function decks() {
    // get decks created by the community
    $communityDecks = $this->getCommunityDecks();
    $skip = 0;
    return parent::ajaxView('mtg.decks', compact('communityDecks', 'skip'));
}
public function getCommunityDecks($skip = 0) {
    $communityDecks = DB::table('auth.users_deck_overview as deckOverviewDB')
        ->select('deckOverviewDB.username', 'deckOverviewDB.by', 'deckOverviewDB.deck_uid', 'deckOverviewDB.featured', 'deckOverviewDB.name', 'deckOverviewDB.description', 'deckOverviewDB.usage', 'deckOverviewDB.format', 'deckOverviewDB.private')
        ->skip($skip)
        ->take($this->take)
        ->get();
    // need to refactor this so it doesn't hit the database n times - ideally this for loop query should be combined with the above query
    foreach($communityDecks as $deck) {
        $deck->ratio = $this->getColorRatioForDeck($deck->username, $deck->deck_uid);
    }
    return $communityDecks;
}
public function getColorRatioForDeck($username, $deckUid) {
    // get the corresponding deck information to calculate color ratio
    $query = DB::table('auth.users_deck_overview as deckOverviewDB')
        ->leftJoin('auth.users_deck_cards as deckCardsDB', 'deckOverviewDB.deck_uid', '=', 'deckCardsDB.deck_uid')
        ->leftJoin('mtg_cards.cards as cardsDB', 'deckCardsDB.card_uid', '=', 'cardsDB.uid')
        ->select('cardsDB.name', 'deckCardsDB.card_quantity', 'cardsDB.manaCost', 'cardsDB.cmc')
        ->where('deckOverviewDB.username', '=', $username)
        ->where('deckOverviewDB.deck_uid', '=', $deckUid)
        ->where('deckCardsDB.board', '=', 0)
        ->where('cardsDB.cmc', '!=', '')
        ->get();
    $ratio = [];
    $ratio['cmc'] = 0;
    $ratio['{W}'] = 0;
    $ratio['{U}'] = 0;
    $ratio['{B}'] = 0;
    $ratio['{R}'] = 0;
    $ratio['{G}'] = 0;
    $ratio['{n}'] = 0; // colorless (does not count {X})
    foreach($query as $card) {
        $cmc = $card->cmc;
        $W = substr_count($card->manaCost, '{W}');
        $U = substr_count($card->manaCost, '{U}');
        $B = substr_count($card->manaCost, '{B}');
        $R = substr_count($card->manaCost, '{R}');
        $G = substr_count($card->manaCost, '{G}');
        $n = ($cmc - $W - $U - $B - $R - $G);
        $ratio['cmc'] += $cmc * $card->card_quantity;
        $ratio['{W}'] += $W * $card->card_quantity;
        $ratio['{U}'] += $U * $card->card_quantity;
        $ratio['{B}'] += $B * $card->card_quantity;
        $ratio['{R}'] += $R * $card->card_quantity;
        $ratio['{G}'] += $G * $card->card_quantity;
        $ratio['{n}'] += $n * $card->card_quantity;
    }
    return $ratio;
}

Answer 1

您可以稍微修改函数以接受数组而不是整数，请考虑：

$deckIds = [];
foreach($communityDecks as $deck) {
    $deckIds[] = $deck->deck_uid;
}
$ratios = $this->getColorRatioForDeck($deck->username, $deckIds);

然后你的函数：

public function getColorRatioForDeck($username, array $deckUids) {
    // get the corresponding deck information to calculate color ratio
    $query = DB::table('auth.users_deck_overview as deckOverviewDB')
        ->leftJoin('auth.users_deck_cards as deckCardsDB', 'deckOverviewDB.deck_uid', '=', 'deckCardsDB.deck_uid')
        ->leftJoin('mtg_cards.cards as cardsDB', 'deckCardsDB.card_uid', '=', 'cardsDB.uid')
        ->select('cardsDB.name', 'deckCardsDB.card_quantity', 'cardsDB.manaCost', 'cardsDB.cmc')
        ->where('deckOverviewDB.username', '=', $username)
        ->whereIn('deckOverviewDB.deck_uid', $deckUids) // <-- changed comparison
        ->where('deckCardsDB.board', '=', 0)
        ->where('cardsDB.cmc', '!=', '')
        ->get();

您可能需要修改 getColorRatioForDeck 函数的其余部分以适应您现在返回多条记录的事实，我不确定 - 但有一个示例。