Laravel query builder returning empty where direct SQL returns results

Question

I am trying to resolve an issue where using Laravel's query builder I am getting no results, but when I run the same query, taken from getQueryLog using PHPMyAdmin I get expected results.

My Query builder:

DB::table('likes')
            ->join('geoip_blocks', function($join)
            {
                $join->where("ip", ">=", "block_start")
                    ->where("ip", "<=", "block_end");
            })
            ->join('geoip_locations', "geoip_locations.id", "=", "location_id")
            ->select(array("country", DB::raw("count(*) as {$this->aggregate}")))
            ->groupBy('country')->get();

Output from querylog:

array (size=3)
  'query' => string 'select `country`, count(*) as count from `likes` inner join `geoip_blocks` on `ip` >= ? and `ip` <= ? inner join `geoip_locations` on `geoip_locations`.`id` = `location_id` group by `country`' (length=196)
  'bindings' => 
    array (size=2)
      0 => string 'block_start' (length=11)
      1 => string 'block_end' (length=9)
  'time' => float 2.69

and the query I enter into phpmyadmin:

select `country`, count(*) as count from `likes` inner join `geoip_blocks` on `ip` >= block_start and `ip` <= block_end inner join `geoip_locations` on `geoip_locations`.`id` = `location_id` group by `country`

I know the above query is extremely inefficient. This was just for a proof of concept and it isn't even working so why bother go further. I've tried varying degrees of ambiguity and explicitness to no avail. Also tried removing the second join and just getting the location_id - once again without success.

Any help is greatly appreciated!

Same deal with Eloquent. I tried it using query builder because Eloquent wouldn't work! — pragmatist1
– pragmatist1, Commented Jul 8, 2014 at 0:39
Well, you can always use good old PDO with bound parameters, if everything else fails. — MightyPork
– MightyPork, Commented Jul 8, 2014 at 0:41

pragmatist1 · Accepted Answer · 2014-07-08 01:25:52Z

2

Ok, so I figured it out. I switched both $join->where's to $join->on's and now everything works. It appears on the face of it to generate the same query, but I guess something else is going on behind the scenes.

answered Jul 8, 2014 at 1:25

pragmatist1

9196 silver badges17 bronze badges

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

1 Comment

Jarek Tkaczyk Over a year ago

Yes, where on the JoinClause class is supposed for fixed values, while on for other columns. Your first attempt treated block_start & block_end as a string instead of a column to compare.

Collectives™ on Stack Overflow

Laravel query builder returning empty where direct SQL returns results

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related