Join not working using Laravel Query Builder

Question

I'm trying to use Query Builder to do a join that pulls all records that exist in two tables (inner join) using ON and WHERE. It works when I do raw SQL and gives me a count of 9k records but when I use query builder, my count is 0 every time. What am I doing wrong?

Laravel Query Builder

$count = DB::table('listings_queue')
->join('listings', function($join)
{
    $join->on('listings_queue.mls_listing_id', '=', 'listings.mls_listing_id')
        ->where('listings.mls_id','=','listings_queue.mls_id')
        ->where('listings.city' , '=', 'listings_queue.city');
})
    ->count();
    $this->info($count);

Raw SQL

select * from listings_queue
INNER JOIN listings
ON `listings_queue`.`mls_listing_id` = `listings`.`mls_listing_id`
WHERE `listings`.`mls_id`=`listings_queue`.`mls_id`
AND `listings`.`city`=`listings_queue`.`city`

Now, I'm admittedly not very smart but I could swear these are the same thing. Any idea what i'm doing wrong in Laravel?

Answer 1

There are two ways to do that, the first one is by the query builder api & the 2nd one is by writting raw query which is not a secure practice, you can try whichever suits you the best.

1st Way

  $results=DB::table('listings_queue')
 ->join('listing','listings_queue.mls_listing_id','=','listings.mls_listing_id')
->select('*')
->where('listings.mls_id','listings_queue.mls_id')
->where('listings.city','listings_queue.city');
->get();

2nd Way

$results=DB::select(DB::raw("
  select * from listings_queue
   INNER JOIN listings
  ON `listings_queue`.`mls_listing_id` = `listings`.`mls_listing_id`
   WHERE `listings`.`mls_id`=`listings_queue`.`mls_id`
   AND `listings`.`city`=`listings_queue`.`city`"));

Answer 2

In your query builder join, the third parameter to where methods will be treated as a bind parameter. Try using whereRaw instead.

$count = DB::table('listings_queue')
    ->join('listings', function ($join) {
        $join
            ->on('listings_queue.mls_listing_id', '=', 'listings.mls_listing_id')
            ->whereRaw('listings.mls_id = listings_queue.mls_id')
            ->whereRaw('listings.city = listings_queue.city');
    })
    ->count();

The raw SQL you posted is slightly different but I think it would produce the same result.

$count = DB::table('listings_queue')
    ->join('listings', 'listings_queue.mls_listing_id', '=', 'listings.mls_listing_id')
    ->whereRaw('listings.mls_id = listings_queue.mls_id')
    ->whereRaw('listings.city = listings_queue.city')
    ->count();

Answer 3

I've run into this one a lot actually. Tim's suggestion of whereRaw works but there are some cleaner options.

First, we can just pass the 3rd param wrapped in DB::raw().

$query->join('b', function ($join)
{

    $join->on('a.foo', '=', 'b.foo')
        ->where('a.bar', '=', DB::raw('b.bar'));
});

Better still we can chain multiple calls to ->on which expects the third param to be a column name, not a bound variable.

$query->join('b', function ($join)
{
    $join->on('a.foo', '=', 'b.foo')
        ->on('a.bar', '=', 'b.bar');
});

If you look at the actual generated sql the issue will be hard to spot. Column names are wrapped in tick marks ` and strings are wrapped in single quotes '. The end result of the query you posted would have something like.

WHERE `column_1` = 'column_2'

What you want to see is this...

WHERE `column_1` = `column_2`

For help logging SQL queries, see my other answer Laravel 4 - logging SQL queries

Answer 4

Did you try to add ->toSql() instead of count() to the end of chain methods ? This would print the sql and you could verify if the query is exactly the same as the raw one.

Also, try with this:

$count = DB::table('listings_queue')
            ->join('listings', 'listings_queue.mls_listing_id', '=', 'listings.mls_listing_id')
            ->where('listings.mls_id','=','listings_queue.mls_id')
            ->where('listings.city' , '=', 'listings_queue.city')
            ->count();
    $this->info($count);

Answer 5

DB::select("select * from listings_queue
INNER JOIN listings
ON `listings_queue`.`mls_listing_id` = `listings`.`mls_listing_id`
WHERE `listings`.`mls_id`=`listings_queue`.`mls_id`
AND `listings`.`city`=`listings_queue`.`city`");