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?

Srinivasulu Rao · Accepted Answer · 2015-10-08 18:56:17Z

2

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`"));

answered Oct 8, 2015 at 18:56

Srinivasulu Rao

3091 gold badge3 silver badges12 bronze badges

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

Comments

Tim · Accepted Answer · 2015-01-31 03:03:27Z

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();

Community · Accepted Answer · 2017-05-23 12:23:02Z

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

hopsoo · Accepted Answer · 2014-03-07 08:09:12Z

0

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

answered Mar 7, 2014 at 8:09

hopsoo

3051 silver badge3 bronze badges

2 Comments

Chris Farrugia Over a year ago

Didn't know about the toSQL() so that is very helpful, thanks. Your query didn't work and produced the following: select * from listings_queue inner join listings on listings_queue.mls_listing_id = listings.mls_listing_id where listings.mls_id = ? and listings.city = ?

hopsoo Over a year ago

So, this is strange - this where is actually another join ?

Borna · Accepted Answer · 2016-08-21 05:35:27Z

0

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`");

answered Aug 21, 2016 at 5:35

Borna

5485 silver badges20 bronze badges

Collectives™ on Stack Overflow

Join not working using Laravel Query Builder

5 Answers 5

1st Way

2nd Way

Comments

Comments

Comments

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

1st Way

2nd Way

Comments

Comments

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related