well count returns the number of total rows, not the the rows per se

arcollector said:

well count returns the number of total rows, not the the rows per se

It's returning 0 though. This query will eventually be used to delete the records from the queue that already exist in the listings table.

I tried:

$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')
->toSql();
$this->info($count);

That produces the following SQL:

select * from `listings_queue` inner join `listings` on `listings_queue`.`mls_listing_id` = `listings`.`mls_listing_id` where `listings`.`mls_id` = ? and `listings`.`city` = ?

In your original example, you have the where clauses nested in the in the join function, I think that may put the conditions on the join and not the overall statement.

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

I think what that is saying is

LEFT JOIN listings ON `listings_queue`.`mls_listing_id` = `listings`.`mls_listing_id` and `listings.mls_id` = `listings_queue.mls_id` and `listings.city` = `listings_queue.city`

and what you want is

LEFT 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`

Try it with the where clause outside the join function and see if it works:

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

Thanks but that didn't work out either. It produced:

select * from `listings_queue`
inner join `listings`
on `listings_queue`.`mls_listing_id` = `listings`.`mls_listing_id`
where `listings`.`mls_id` = ?
and `listings`.`city` = ?

Stupid question, but are those ? causing my issue in that it's not comparing the two tables but instead looking for a value that isn't htere?

those ? are placeholders, they are use for security reasons as well for better code reading, check this: http://en.wikipedia.org/wiki/Prepared_statement

if you call DB::getQueryLog(), for each query you will see the values that are binded for each placeholder

I went back to my Sequel Pro and wrote the query in just raw SQL and it works great. So, asked another way, how would I write this query using query builder?

select count(listings_queue.mls_listing_id) 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`

i have same problem

This Link can help you : https://hdtuto.com/article/php-laravel-select-with-join-subquery-example