文章/答案/技术大牛

发布

社区首页 >问答首页 >某些列返回null的Laravel雄辩查询

问某些列返回null的Laravel雄辩查询
EN

Stack Overflow用户

提问于 2017-09-27 21:19:34

回答 1查看 2.1K关注 0票数 1

我有一个很有说服力的问题：

$query = Event_model::join('countries', function($q) use ($enabled_country_ids) {
        $q->on('events.country_id', '=', 'countries.id')
          ->whereIn('countries.id', $enabled_country_ids);
    })
    ->join('leagues', function($q) use ($enabled_league_ids) {
        $q->on('events.league_id', '=', 'leagues.id')
          ->whereIn('leagues.id', $enabled_league_ids); 
    })
    ->leftJoin('event_odds AS eo1', function($q) use($win_market, $home_market_value) {
        $q->on('events.id', '=', 'eo1.event_id')
          ->where('eo1.market_id', '=', $win_market->id)
          ->where('eo1.market_value_id', '=', $home_market_value->id)
          ->where('eo1.value', '=', 'eo1.best_odd');
    })
    ->leftJoin('event_odds AS eo2', function($q) use($win_market, $draw_market_value) {
        $q->on('events.id', '=', 'eo2.event_id')
          ->where('eo2.market_id', '=', $win_market->id)
          ->where('eo2.market_value_id', '=', $draw_market_value->id)
          ->where('eo2.value', '=', 'eo2.best_odd');
    })
    ->leftJoin('event_odds AS eo3', function($q) use($win_market, $away_market_value) {
        $q->on('events.id', '=', 'eo3.event_id')
          ->where('eo3.market_id', '=', $win_market->id)
          ->where('eo3.market_value_id', '=', $away_market_value->id)
          ->where('eo3.value', '=', 'eo3.best_odd');
    })
    ->leftJoin('event_odds AS eo4', function($q) use($ou_market, $over_market_value) {
        $q->on('events.id', '=', 'eo4.event_id')
          ->where('eo4.market_id', '=', $ou_market->id)
          ->where('eo4.market_value_id', '=', $over_market_value->id)
          ->where('eo4.value', '=', 'eo4.best_odd');
    })
    ->leftJoin('event_odds AS eo5', function($q) use($ou_market, $under_market_value) {
        $q->on('events.id', '=', 'eo5.event_id')
          ->where('eo5.market_id', '=', $ou_market->id)
          ->where('eo5.market_value_id', '=', $under_market_value->id)
          ->where('eo5.value', '=', 'eo5.best_odd');
    })
    ->whereIn('events.sport_id', $enabled_sports_ids)
    ->where('events.event_datetime', '>', time())
    ->select([
        'events.id',
        DB::raw('FROM_UNIXTIME(events.event_datetime, "%a %D %b %H:%i") AS datetime'),
        DB::raw('CONCAT(countries.name, " ", leagues.name, " | ", FROM_UNIXTIME(events.event_datetime, "%Y-%m-%d")) AS countryleague'),
        DB::raw('CONCAT(events.team1_name, " vs ", events.team2_name) AS game'),
        'eo1.value AS home',           
        'eo2.value AS draw',
        'eo3.value AS away',
        'eo4.value AS over',
        'eo5.value AS under',
        DB::raw('CONCAT("<a href=\"#\" class=\"expand-all btn\" data-id=\"", events.id, "\"><i class=\"fa fa-plus\"></i> Expand all odds</a>") AS expand'),
        'leagues.major'
    ]);

这是雄辩地生成的原始SQL查询：

SELECT
  `events`.`id`,
  FROM_UNIXTIME(
    EVENTS.event_datetime,
    "%a %D %b %H:%i"
  ) AS DATETIME,
  CONCAT(
    countries.name,
    " ",
    leagues.name,
    " | ",
    FROM_UNIXTIME(
      EVENTS.event_datetime,
      "%Y-%m-%d"
    )
  ) AS countryleague,
  CONCAT(
    EVENTS.team1_name,
    " vs ",
    EVENTS.team2_name
  ) AS game,
  `eo1`.`value` AS `home`,
  `eo2`.`value` AS `draw`,
  `eo3`.`value` AS `away`,
  `eo4`.`value` AS `over`,
  `eo5`.`value` AS `under`,
  CONCAT(
    "<a href=\"#\" class=\"expand-all btn\" data-id=\"",
    EVENTS.id,
    "\"><i class=\"fa fa-plus\"></i> Expand all odds</a>"
  ) AS expand,
  `leagues`.`major`
FROM
  `events`
INNER JOIN
  `countries` ON `events`.`country_id` = `countries`.`id` AND `countries`.`id` IN(1,2,3,4)
INNER JOIN
  `leagues` ON `events`.`league_id` = `leagues`.`id` AND `leagues`.`id` IN(1,2,3,4)
LEFT JOIN
  `event_odds` AS `eo1` ON `events`.`id` = `eo1`.`event_id` AND `eo1`.`market_id` = 3 AND `eo1`.`market_value_id` = 51 AND `eo1`.`value` = eo1.best_odd
LEFT JOIN
  `event_odds` AS `eo2` ON `events`.`id` = `eo2`.`event_id` AND `eo2`.`market_id` = 3 AND `eo2`.`market_value_id` = 52 AND `eo2`.`value` = eo2.best_odd
LEFT JOIN
  `event_odds` AS `eo3` ON `events`.`id` = `eo3`.`event_id` AND `eo3`.`market_id` = 3 AND `eo3`.`market_value_id` = 53 AND `eo3`.`value` = eo3.best_odd
LEFT JOIN
  `event_odds` AS `eo4` ON `events`.`id` = `eo4`.`event_id` AND `eo4`.`market_id` = 4 AND `eo4`.`market_value_id` = 54 AND `eo4`.`value` = eo4.best_odd
LEFT JOIN
  `event_odds` AS `eo5` ON `events`.`id` = `eo5`.`event_id` AND `eo5`.`market_id` = 4 AND `eo5`.`market_value_id` = 55 AND `eo5`.`value` = eo5.best_odd
WHERE
  `events`.`sport_id` IN(1) AND `events`.`event_datetime` > 1506546556
ORDER BY
  `leagues`.`major` DESC,
  `events`.`event_datetime` ASC,
  `leagues`.`name` ASC
LIMIT 60 OFFSET 0

查询运行良好，但对于结果的每一行，主列、绘制列、离开列、上列和列下的值都为空。然而，如果我得到了在PhpMyAdmin中运行并执行的原始SQL，那么所有这些字段都有值。当它们有值时，Laravel为什么返回null？

更新

通过将\DB::enableQueryLog放在查询之前，然后放在dd(\DB::getQueryLog() )之前，我得到了以下原始SQL，在PhpMyAdmin中运行时，它执行与Laravel相同的操作，并对这些列返回null，所以这个查询肯定有一些不同。

SELECT
  `events`.`id`,
  FROM_UNIXTIME(
    EVENTS.event_datetime,
    "%a %D %b %H:%i"
  ) AS DATETIME,
  CONCAT(
    countries.name,
    " ",
    leagues.name,
    " | ",
    FROM_UNIXTIME(
      EVENTS.event_datetime,
      "%Y-%m-%d"
    )
  ) AS countryleague,
  CONCAT(
    EVENTS.team1_name,
    " vs ",
    EVENTS.team2_name
  ) AS game,
  CONCAT(eo1.value) AS home,
  `eo2`.`value` AS `draw`,
  `eo3`.`value` AS `away`,
  `eo4`.`value` AS `over`,
  `eo5`.`value` AS `under`,
  CONCAT(
    "<a href=\"#\" class=\"expand-all btn\" data-id=\"",
    EVENTS.id,
    "\"><i class=\"fa fa-plus\"></i> Expand all odds</a>"
  ) AS expand,
  `leagues`.`major`
FROM
  `events`
INNER JOIN
  `countries` ON `events`.`country_id` = `countries`.`id`
INNER JOIN
  `leagues` ON `events`.`league_id` = `leagues`.`id`
LEFT JOIN
  `event_odds` AS `eo1` ON `events`.`id` = `eo1`.`event_id` AND `eo1`.`market_id` = 3 AND `eo1`.`market_value_id` = 51 AND `eo1`.`value` = eo1.best_odd
LEFT JOIN
  `event_odds` AS `eo2` ON `events`.`id` = `eo2`.`event_id` AND `eo2`.`market_id` = 3 AND `eo2`.`market_value_id` = 52 AND `eo2`.`value` = eo2.best_odd
LEFT JOIN
  `event_odds` AS `eo3` ON `events`.`id` = `eo3`.`event_id` AND `eo3`.`market_id` = 3 AND `eo3`.`market_value_id` = 53 AND `eo3`.`value` = eo3.best_odd
LEFT JOIN
  `event_odds` AS `eo4` ON `events`.`id` = `eo4`.`event_id` AND `eo4`.`market_id` = 4 AND `eo4`.`market_value_id` = 54 AND `eo4`.`value` = eo4.best_odd
LEFT JOIN
  `event_odds` AS `eo5` ON `events`.`id` = `eo5`.`event_id` AND `eo5`.`market_id` = 4 AND `eo5`.`market_value_id` = 55 AND `eo5`.`value` = eo5.best_odd
WHERE
  `events`.`sport_id` IN(1) AND `events`.`event_datetime` > 0
ORDER BY
  `leagues`.`major` DESC,
  `events`.`event_datetime` ASC,
  `leagues`.`name` ASC
LIMIT 60 OFFSET 0

php

laravel

laravel-5.4

eloquent

回答 1

Stack Overflow用户

回答已采纳

发布于 2017-09-27 22:07:10

我把它破解了！这是因为我在联接中使用WHERE而不是ON

->leftJoin('event_odds AS eo1', function($q) use($win_market, $home_market_value) {
    $q->on('events.id', '=', 'eo1.event_id')
      ->where('eo1.market_id', '=', $win_market->id)
      ->where('eo1.market_value_id', '=', $home_market_value->id)
      ->where('eo1.value', '=', 'eo1.best_odd');
})

应：

->leftJoin('event_odds AS eo1', function($q) use($win_market, $home_market_value) {
    $q->on('events.id', '=', 'eo1.event_id')
      ->where('eo1.market_id', '=', $win_market->id)
      ->where('eo1.market_value_id', '=', $home_market_value->id)
      ->on('eo1.value', '=', 'eo1.best_odd');
})

票数 0

页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持

原文链接：

https://stackoverflow.com/questions/46457390

复制

相似问题

问某些列返回null的Laravel雄辩查询
EN

回答 1

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问某些列返回null的Laravel雄辩查询EN

回答 1

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问某些列返回null的Laravel雄辩查询
EN