Laravel eloquent query returning null for some columns

Question

I have the following Eloquent query:

$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'
    ]);

This is the RAW SQL query that Eloquent generates:

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

The query runs fine but for each row of the results the values returned for the home, draw, away, over and under columns is null. Yet if I get the raw SQL that Eloquent has just run and execute it in PhpMyAdmin all of these fields have values. Why is Laravel returning null for them when they do have a value?

Update

By putting \DB::enableQueryLog before the query and then dd(\DB::getQueryLog() I get the following raw SQL which when run in PhpMyAdmin does the same thing that Laravel did and returns null for those columns so there must be something different about this query.

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

Try putting your "x.value as" statements in DB::raw - does it make a difference? — Jason
– Jason, Commented Sep 27, 2017 at 21:40
Seems ok to me. Try putting \DB::enableQueryLog(); before query and dd(\DB::getQueryLog()); after to check if the generated query has any difference. — Felippe Duarte
– Felippe Duarte, Commented Sep 27, 2017 at 21:44
I tried using DB::raw on the x.value as statements but it made no difference. The getQueryLog suggestion has shown something interesting though. The columns are null in the result set now. So there must be something different about the query. I'll post it up so you guys can see — tuscan88
– tuscan88, Commented Sep 27, 2017 at 21:52

tuscan88 · Accepted Answer · 2017-09-27 22:07:10Z

0

Aaaaaah I cracked it! It's because I was using WHERE instead of ON in my joins e.g

->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');
})

Should be:

->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');
})

answered Sep 27, 2017 at 22:07

tuscan88

5,86912 gold badges57 silver badges112 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Laravel eloquent query returning null for some columns

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related